create table #t ( a int, b int , c int ,d int)
--drop table #t
insert into #t
select 1,1,1,1 union all
select 2,1,2,1 union all
select 3,2,2,2 union all
select 4,2,2,3 union all
select 5,3,4,3 union all
select 6,3,4,3
go
select count(a) from #t
group by case
when b >1 then 'a'
when c >1 then 'b'
end
select count(a) from #t
group by case
when b >1 then 'a'
when c >1 then 'a'
end----------------------------------/*
这两个结果集不同, WHY? 谁能帮忙解释下。
*/
--drop table #t
insert into #t
select 1,1,1,1 union all
select 2,1,2,1 union all
select 3,2,2,2 union all
select 4,2,2,3 union all
select 5,3,4,3 union all
select 6,3,4,3
go
select count(a) from #t
group by case
when b >1 then 'a'
when c >1 then 'b'
end
select count(a) from #t
group by case
when b >1 then 'a'
when c >1 then 'a'
end----------------------------------/*
这两个结果集不同, WHY? 谁能帮忙解释下。
*/
group by case
when b >1 then 'a'
when c >1 then 'b'
end
select count(a) from #t
group by case
when b >1 then 'a'
when c >1 then 'a'
end 条件都不同吧,
第一个分组最后会形成两条记录,一是group的值为a的,另一个是为null
第二个为三个值,a,b和null
group by case
when b >1 then 'a'
when c >1 then 'b'
else null
end
select count(a) from #t
group by case
when b >1 then 'a'
when c >1 then 'a'
else null
end