22,209
社区成员
发帖
与我相关
我的任务
分享
--语句一
select b,max(sum) as 'max' from (
select a,b,sum(c)as 'sum' group by a,b
) as a group by b --这条语句的查询速度正常
--语句二
select b,max(sum) as 'max' from (
select a,b,sum(c)as 'sum' group by a,b
) as a where b is not null group by b --这条语句的查询速度不正常,会很慢,查询的时间是上一条的10倍多
--select b,max(sum) as 'max' from (
-- select a,b,sum(c)as 'sum' group by a,b
--) as a group by b having b is not null --这句的速度也一样很慢,跟句二没区别
--语句三
select b,max from (
select b,max(sum) as 'max' from (
select a,b,sum(c)as 'sum' group by a,b
) as a group by b
) as c where b is not null --这条跟句二的速度一样
--语句四:
select * into #temp from (
select b,max(sum) as 'max' from (
select a,b,sum(c)as 'sum' group by a,b
) as a group by b
) as c
select * from #temp where b is not null --先把结果插入到临时表再用条件is not null 的总查询速度只比第一种查询速度慢一秒
--这语句得到的记录数有1096条
select a,b,sum(c)as 'sum' group by a,b
--这语句查询到的记录数只有四十多条,b为null的记录只有一条
select b,max(sum) as 'max' from (
select a,b,sum(c)as 'sum' group by a,b
) as a group by b