先有如下语句:--语句一
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为什么在句二和句三中加了is not null条件的查询速度会变那么多慢
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为什么在句二和句三中加了is not null条件的查询速度会变那么多慢
但是is not null 这个条件我是写在父查询那里,而且父查询查询的对象是子查询,子查询的结果我也提到了,为null的记录数只有一条,同时子查询才是面向实际表的,我都没有设置条件,按道理不应该会慢那么多的