sumcasewhen的用法,sumcase when语句
sql server中case when与sum联合使用
把你的表名换成你自己的表名
DECLARE?@sql?AS?VARCHAR(4000)=''
SELECT?@sql=@sql+'SELECT?date0?'
SELECT?@sql=@sql+',SUM(CASE?WHEN?type='+type+'?THEN?num?END)AS?''num_'+type+''''
FROM?表名?GROUP?BY?type
SELECT?@sql=@sql+'?FROM?表名?GROUP?BY?date0?ORDER?BY?date0'
PRINT?@sql
EXEC(@sql)
这里的引号要注意，PRINT下看看动态语句正不正确，正确后EXEC就可以了
Sqlserver的case when 用法
?---下文举例分析了case?when常用的用法，如下所示：涉及排序字段的应用
?create?table?test
(
?qty?int?,
?sort?varchar(20)
)
insert?into?test(qty,sort)values
(1,'a'),(2,'b'),(3,'d'),(1,'e')
go
----方法1:
select?sort,qty,
??case?qty
????when?1?then?'少'
????when?2?then?'中'
????when?3?then?'多'
????else?'未知'
??end?as?[数量范围]
??from?test?
--方法2:
select?sort,qty,
??case?
????when?qty=1?then?'少'
????when?qty=2?then?'中'
????when?qty=3?then?'多'
????else?'未知'
??end?as?[数量范围]
??from?test?
---sum统计用法
select?
?sum(?case??????when?qty=1?then?1?else?0?end)?as?[少],
?sum(?case?qty??when??2?then?1?else?0?end)?as?[中],
?sum(?case??????when?qty=3?then?1?else?0?end)?as?[多],
?sum(?case??????when?qty?1?and?qty?2?and?qty?3?then?1?else?0?end)?as?[位置]
????from?test?
??
?
?---case?when?做排序字段
?declare?@i?int?
?set?@i=0
?select?*?from?test?
?order?by?
?case?@i?when?0?then?qty?else?sort????end?
???
?
go
truncate?table?test
drop?????table?test
SQL语句中是否用过Sum,Count,Top,Group By,Having,Case...When这些关键字，请描述具体用法?大家回答下谢谢
1 sum()计算括号里的总和 例：select sum(a) from tmp,列出字段a列的综合
2 count（）计算括号里内容的数量 例：select count(*) from student where age=18,年龄是18岁的学生人数
3 top()前几条数据 例：select top 5 * from tmp 列出前5条记录
4 group by 根据某个字段分组 例：select count（*） from tmp group by 部门 意思是根据部门分组，列出每个部门有多少条记录
5 having 后面加条件，与where有点像，区别就是having可以用聚合函数 例： select count （*） from tmp group by 部门 having money1000根据部门分组，列出每个部门工资大于1000的有多少人
6 case..when根据条件选择
例子：select (case when age=18 then "成年" else "未成年" end) from tmp
如果年龄大于等于18就显示成年，反之显示未成年
自己举的一些例子，有不明白再问我