declare @table table(lx char(4),ck char(2))
insert @table values('顶级','红')
insert @table values('特级','白')
insert @table values('一级','红')
insert @table values('顶级','黄')select ck,count(case when lx = '顶级' then lx end) 顶级,
count(case when lx = '特级' then lx end) 特级,
count(case when lx = '一级' then lx end) 一级,
count(case when lx = '次品' then lx end) 次品
from @table
group by ck
union all
select '合计',
count(case when lx = '顶级' then lx end) 顶级,
count(case when lx = '特级' then lx end) 特级,
count(case when lx = '一级' then lx end) 一级,
count(case when lx = '次品' then lx end) 次品
from @table/*ck 顶级 特级 一级 次品
---- ----------- ----------- ----------- -----------
白 0 1 0 0
红 1 0 1 0
黄 1 0 0 0
合计 2 1 1 0
*/
(
bh VARCHAR(10),
rq DATETIME,
lx VARCHAR(10),
pz VARCHAR(10),
gl VARCHAR(10),
ck VARCHAR(10)
)
INSERT tab_1
SELECT '0001','2003-07-05','顶级','aaa','次','红'
UNION ALL SELECT '0002','2003-07-05','特级','bbb','正','白'
UNION ALL SELECT '0003','2003-07-08','一级','CCC','正','红'
UNION ALL SELECT '0004','2003-08-01','顶级','fda','正','黄'SELECT * FROM tab_1SELECT * FROM
(
SELECT
仓库 = ck,
顶级 = SUM(CASE WHEN lx = '顶级' THEN 1 ELSE 0 END),
特级 = SUM(CASE WHEN lx = ' 特级' THEN 1 ELSE 0 END),
一级 = SUM(CASE WHEN lx = '一级' THEN 1 ELSE 0 END),
次品 = SUM(CASE WHEN gl = '次' THEN 1 ELSE 0 END)
FROM tab_1 WHERE YEAR(rq) = 2003
GROUP BY ck
UNION
SELECT
仓库 = '合计',
顶级 = SUM(CASE WHEN lx = '顶级' THEN 1 ELSE 0 END),
特级 = SUM(CASE WHEN lx = ' 特级' THEN 1 ELSE 0 END),
一级 = SUM(CASE WHEN lx = '一级' THEN 1 ELSE 0 END),
次品 = SUM(CASE WHEN gl = '次' THEN 1 ELSE 0 END)
FROM tab_1 WHERE YEAR(rq) = 2003
) A
ORDER BY CHARINDEX('合计',仓库)DROP TABLE tab_1
/*(所影响的行数为 4 行)bh rq lx pz gl ck
---------- ------------------------------------------------------ ---------- ---------- ---------- ----------
0001 2003-07-05 00:00:00.000 顶级 aaa 次 红
0002 2003-07-05 00:00:00.000 特级 bbb 正 白
0003 2003-07-08 00:00:00.000 一级 CCC 正 红
0004 2003-08-01 00:00:00.000 顶级 fda 正 黄(所影响的行数为 4 行)仓库 顶级 特级 一级 次品
---------- ----------- ----------- ----------- -----------
白 0 0 0 0
红 1 0 1 1
黄 1 0 0 0
合计 2 0 1 1(所影响的行数为 4 行)
*/
lx char(10),
pz char(10),
gl char(10),
ck char(10)
)insert into #a values('顶级','aaa','次','红')
insert into #a values('特级','bbb','正','白')
insert into #a values('一级','ccc','正','红')
insert into #a values('顶级','fda','正','黄')
select * from #aselect ck, max(case when lx='顶级' then 1 else 0 end)as 顶级,
max(case when lx='特级' then 1 else 0 end)as 特级,
max(case when lx='一级' then 1 else 0 end)as 一级,
max(case when gl='次' then 1 else 0 end)as 次
from #a
group by ck union all
select '合计', sum(case when lx='顶级' then 1 else 0 end)as 顶级,
sum(case when lx='特级' then 1 else 0 end)as 特级,
sum(case when lx='一级' then 1 else 0 end)as 一级,
sum(case when gl='次' then 1 else 0 end)as 次
from #a