--感觉楼主,提供测试脚本. -- 如果item 不固定。。楼主就要拼接sql语句了。 create table tab1(id nvarchar(20),createdate datetime) insert into tab1(id,createdate)values('111','2014-01-01') insert into tab1(id,createdate)values('112','2014-02-01') insert into tab1(id,createdate)values('113','2014-06-01') create table tab2(id nvarchar(20),cerid nvarchar(20),item nvarchar(20)) insert into tab2(id,cerid,item)values('111','111','跑步') insert into tab2(id,cerid,item)values('112','111','跳远') insert into tab2(id,cerid,item)values('113','112','跑步') insert into tab2(id,cerid,item)values('114','113','跑步') ;WITH t AS ( SELECT pvt.* FROM ( SELECT DATEPART(q,t1.createdate) [季度],t2.item,COUNT(1) ct FROM tab2 AS t2 INNER JOIN tab1 AS t1 ON t1.id=t2.cerid GROUP BY DATEPART(q,t1.createdate),t2.item ) r PIVOT( SUM(ct) FOR item IN([跑步],[跳远])) pvt ), q AS( SELECT 1 q UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ) SELECT q.q AS [季度], isnull([跑步],0)[跑步],isnull([跳远],0) [跳远] FROM t RIGHT JOIN q ON t.[季度]=q.q;/* 季度 跑步 跳远 ----------- ----------- ----------- 1 2 1 2 1 0 3 0 0 4 0 0(4 行受影响)*/
SELECT JD,[跑步],[跳远] FROM ( SELECT JDBH,JD,item FROM( SELECT A.item ,SUM(CASE WHEN DATEPART(MONTH,createdate) BETWEEN 1 AND 3 THEN 1 ELSE 0 END )AS N'第一季度' ,SUM(CASE WHEN DATEPART(MONTH,createdate) BETWEEN 4 AND 6 THEN 1 ELSE 0 END)AS N'第二季度' ,SUM(CASE WHEN DATEPART(MONTH,createdate) BETWEEN 7 AND 9 THEN 1 ELSE 0 END)AS N'第三季度' ,SUM(CASE WHEN DATEPART(MONTH,createdate)BETWEEN 10 AND 12 THEN 1 ELSE 0 END)AS N'第四季度' FROM #tab2 AS A JOIN #tab1 AS B ON A.cerid = B.id GROUP BY A.item ) AS A UNPIVOT ( JDBH FOR JD IN([第一季度],[第二季度],[第三季度],[第四季度])) upivot ) AS B PIVOT (SUM(JDBH) FOR B.item IN([跑步],[跳远])) p下班
两种方法: -- 不用 PIVOT ;WITH Summary([Quarter],Number,Item) AS ( SELECT N'第' + CASE c.number WHEN 1 THEN N'一' WHEN 2 THEN N'二' WHEN 3 THEN N'三' WHEN 4 THEN N'四' END + N'季度' ,c.number ,b.Item FROM dbo.tab1 a INNER JOIN dbo.tab2 b ON a.id = b.cerid RIGHT JOIN master..spt_values c ON c.number = DATEPART(q,a.createdate) WHERE c.TYPE = 'P' AND c.number BETWEEN 1 AND 4 ) SELECT [Quarter] AS 时间, '跑步' = SUM(CASE Item WHEN N'跑步' THEN 1 ELSE 0 END), '跳远' = SUM(CASE Item WHEN N'跳远' THEN 1 ELSE 0 END) FROM Summary GROUP BY [Quarter],Number ORDER BY Number/*结果:时间 跑步 跳远 ---- ----------- ----------- 第一季度 2 1 第二季度 1 0 第三季度 0 0 第四季度 0 0*/ -- 用 PIVOT ;WITH Summary([Quarter],Item,CNT) AS ( SELECT c.number,b.Item,COUNT(*) FROM dbo.tab1 a INNER JOIN dbo.tab2 b ON a.id = b.cerid RIGHT JOIN master..spt_values c ON c.number = DATEPART(q,a.createdate) WHERE c.TYPE = 'P' AND c.number BETWEEN 1 AND 4 GROUP BY c.number,b.Item ) SELECT N'第' + CASE [Quarter] WHEN 1 THEN N'一' WHEN 2 THEN N'二' WHEN 3 THEN N'三' WHEN 4 THEN N'四' END + N'季度' AS 时间, ISNULL(跑步,0) AS 跑步, ISNULL(跳远,0) AS 跳远 FROM Summary PIVOT(SUM(CNT) FOR Item IN(跑步,跳远)) AS a/*结果:时间 跑步 跳远 ---- ----------- ----------- 第一季度 2 1 第二季度 1 0 第三季度 0 0 第四季度 0 0*/
-- 如果item 不固定。。楼主就要拼接sql语句了。
create table tab1(id nvarchar(20),createdate datetime)
insert into tab1(id,createdate)values('111','2014-01-01')
insert into tab1(id,createdate)values('112','2014-02-01')
insert into tab1(id,createdate)values('113','2014-06-01')
create table tab2(id nvarchar(20),cerid nvarchar(20),item nvarchar(20))
insert into tab2(id,cerid,item)values('111','111','跑步')
insert into tab2(id,cerid,item)values('112','111','跳远')
insert into tab2(id,cerid,item)values('113','112','跑步')
insert into tab2(id,cerid,item)values('114','113','跑步') ;WITH t AS (
SELECT pvt.* FROM (
SELECT DATEPART(q,t1.createdate) [季度],t2.item,COUNT(1) ct
FROM tab2 AS t2
INNER JOIN tab1 AS t1 ON t1.id=t2.cerid
GROUP BY DATEPART(q,t1.createdate),t2.item
) r
PIVOT( SUM(ct) FOR item IN([跑步],[跳远])) pvt
), q AS(
SELECT 1 q
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
)
SELECT q.q AS [季度],
isnull([跑步],0)[跑步],isnull([跳远],0) [跳远] FROM t
RIGHT JOIN q ON t.[季度]=q.q;/*
季度 跑步 跳远
----------- ----------- -----------
1 2 1
2 1 0
3 0 0
4 0 0(4 行受影响)*/
SELECT JD,[跑步],[跳远] FROM (
SELECT JDBH,JD,item
FROM(
SELECT A.item
,SUM(CASE WHEN DATEPART(MONTH,createdate) BETWEEN 1 AND 3 THEN 1 ELSE 0 END )AS N'第一季度'
,SUM(CASE WHEN DATEPART(MONTH,createdate) BETWEEN 4 AND 6 THEN 1 ELSE 0 END)AS N'第二季度'
,SUM(CASE WHEN DATEPART(MONTH,createdate) BETWEEN 7 AND 9 THEN 1 ELSE 0 END)AS N'第三季度'
,SUM(CASE WHEN DATEPART(MONTH,createdate)BETWEEN 10 AND 12 THEN 1 ELSE 0 END)AS N'第四季度'
FROM #tab2 AS A
JOIN #tab1 AS B ON A.cerid = B.id
GROUP BY A.item ) AS A
UNPIVOT
( JDBH FOR JD IN([第一季度],[第二季度],[第三季度],[第四季度])) upivot ) AS B
PIVOT (SUM(JDBH) FOR B.item IN([跑步],[跳远])) p下班
-- 不用 PIVOT
;WITH Summary([Quarter],Number,Item) AS
(
SELECT
N'第' + CASE c.number
WHEN 1 THEN N'一'
WHEN 2 THEN N'二'
WHEN 3 THEN N'三'
WHEN 4 THEN N'四'
END + N'季度'
,c.number
,b.Item
FROM dbo.tab1 a
INNER JOIN dbo.tab2 b ON a.id = b.cerid
RIGHT JOIN master..spt_values c ON c.number = DATEPART(q,a.createdate)
WHERE c.TYPE = 'P' AND c.number BETWEEN 1 AND 4
)
SELECT
[Quarter] AS 时间,
'跑步' = SUM(CASE Item WHEN N'跑步' THEN 1 ELSE 0 END),
'跳远' = SUM(CASE Item WHEN N'跳远' THEN 1 ELSE 0 END)
FROM Summary
GROUP BY [Quarter],Number
ORDER BY Number/*结果:时间 跑步 跳远
---- ----------- -----------
第一季度 2 1
第二季度 1 0
第三季度 0 0
第四季度 0 0*/
-- 用 PIVOT
;WITH Summary([Quarter],Item,CNT) AS
(
SELECT c.number,b.Item,COUNT(*)
FROM dbo.tab1 a
INNER JOIN dbo.tab2 b ON a.id = b.cerid
RIGHT JOIN master..spt_values c ON c.number = DATEPART(q,a.createdate)
WHERE c.TYPE = 'P' AND c.number BETWEEN 1 AND 4
GROUP BY c.number,b.Item
)
SELECT
N'第' + CASE [Quarter]
WHEN 1 THEN N'一'
WHEN 2 THEN N'二'
WHEN 3 THEN N'三'
WHEN 4 THEN N'四'
END + N'季度' AS 时间,
ISNULL(跑步,0) AS 跑步,
ISNULL(跳远,0) AS 跳远
FROM Summary PIVOT(SUM(CNT) FOR Item IN(跑步,跳远)) AS a/*结果:时间 跑步 跳远
---- ----------- -----------
第一季度 2 1
第二季度 1 0
第三季度 0 0
第四季度 0 0*/