表1:
id a1 a2
1 aaaa 1
2 bbbbb 2
3 ccccc 1
4 aaaa 1
5 aaaa 3
6 bbbbb 3
7 bbbbb 1
8 ccccc 1用一条sql语句
统计成如下形式:(a1分组为x列,各a1在a2中出现1的次数为y列,非1的次数为z列)
x y z
aaaa 1 2
bbbbb 1 2
ccccc 2 0
id a1 a2
1 aaaa 1
2 bbbbb 2
3 ccccc 1
4 aaaa 1
5 aaaa 3
6 bbbbb 3
7 bbbbb 1
8 ccccc 1用一条sql语句
统计成如下形式:(a1分组为x列,各a1在a2中出现1的次数为y列,非1的次数为z列)
x y z
aaaa 1 2
bbbbb 1 2
ccccc 2 0
SUM(case when a2=1 then 1 else 0 end) as y,
SUM(case when a2<>1 then 1 else 0 end) as z
from tb
group by a1
declare @tab table(id int identity(1,1),a1 varchar(10),a2 int)
insert into @tab
select 'aaaa',1 union all
select 'bbbbb',2 union all
select 'ccccc',1 union all
select 'aaaa',1 union all
select 'aaaa',3 union all
select 'bbbbb',3 union all
select 'bbbbb',1 union all
select 'ccccc',1select a1,sum(case when a2=1 then 1 else 0 end)x,sum(case when a2<>1 then 1 else 0 end)y
from @tab group by a1/*
a1 x y
---------- ----------- -----------
aaaa 2 1
bbbbb 1 2
ccccc 2 0(3 行受影响)*/
-- =========================================
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(id int , a1 varchar(10),a2 int)
go
insert into tb
select 1,'aaaa',1 union all
select 2,'bbbbb',2 union all
select 3,'ccccc',1 union all
select 4,'aaaa',1 union all
select 5,'aaaa',3 union all
select 6,'bbbbb',3 union all
select 7,'bbbbb',1 union all
select 8,'ccccc',1
go
select a1 as x,
SUM(case when a2=1 then 1 else 0 end) as y,
SUM(case when a2<>1 then 1 else 0 end) as z
from tb
group by a1
/*------------
aaaa 2 1
bbbbb 1 2
ccccc 2 0
-------*/
DECLARE @t TABLE(id smallint, a1 varchar(10), a2 int)
INSERT INTO @t
SELECT 1, 'aaaa', 1 UNION ALL
SELECT 2, 'bbbbb', 2 UNION ALL
SELECT 3, 'ccccc', 1 UNION ALL
SELECT 4, 'aaaa', 1 UNION ALL
SELECT 5, 'aaaa', 3 UNION ALL
SELECT 6, 'bbbbb', 3 UNION ALL
SELECT 7, 'bbbbb', 1 UNION ALL
SELECT 8, 'ccccc', 1SELECT
x = a1,
y = SUM(CASE WHEN a2 = 1 THEN 1 ELSE 0 END),
y = SUM(CASE WHEN a2 = 1 THEN 0 ELSE 1 END)
FROM @t a
GROUP BY a1