select column1 column2,count(*) as DataCout from Table group by column1 column2 这个和你要的差不多,你要的 是 每个值 还作为了表头
create table fy (column1 int, column2 varchar(5))insert into fy select 1, 'fix' union all select 1, 'num' union all select 1, 'fix' union all select 1, 'num' union all select 1, 'num' select sum(case when column2='fix' then 1 else 0 end) 'fix_count', sum(case when column2='num' then 1 else 0 end) 'num_count', abs(sum(case when column2='fix' then 1 else 0 end)- sum(case when column2='num' then 1 else 0 end)) 'num-fix' from fy/* fix_count num_count num-fix ----------- ----------- ----------- 2 3 1(1 row(s) affected) */
group by column2
如果分开写
1. select count(*) from table where column2=‘fix’
2. select count(*) from table where column2=‘num’
3. 然后拿到这两个数字做运算
我现在想把以上三步合成一步 有没有办法
2 3 1
group by column1 column2 这个和你要的差不多,你要的 是 每个值 还作为了表头
create table fy
(column1 int, column2 varchar(5))insert into fy
select 1, 'fix' union all
select 1, 'num' union all
select 1, 'fix' union all
select 1, 'num' union all
select 1, 'num'
select sum(case when column2='fix' then 1 else 0 end) 'fix_count',
sum(case when column2='num' then 1 else 0 end) 'num_count',
abs(sum(case when column2='fix' then 1 else 0 end)-
sum(case when column2='num' then 1 else 0 end)) 'num-fix'
from fy/*
fix_count num_count num-fix
----------- ----------- -----------
2 3 1(1 row(s) affected)
*/
强!之前都不知道case什么用 学了本事了