请教一季度统计语句

2014-06-12 04:54:27 206 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

mysql 按日、周、月、年统计sql语句整理，实现报表统计可视化最近在做报表统计，前端可视化插件用的是Echarts，个人认为是目前免费插件里的翘楚http://echarts.baidu.com/demo.html#pie-doughnut例子演示现在重点如何把后台数据源搞定，我采用的是mybatis,所以重点是怎么把sql语句写好。– –按日 SELECT COUNT(*),DATE(CreateTime) FROM t_voipchannelrecord 阅读详情



tab1

id   createdate   

111   2014-01-01

112   2014-02-01

113   2014-06-01

tab2

id   cerid  item

111  111     跑步

112  111     跳远

113  112     跑步

114  113     跑步

查询结果



时间      跑步  跳远

第一季度   3     1

第二季度   1     0

第三季度   0     0

第四季度   0     0



测试数据

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','跑步')

多谢

...全文

第一季度过程介绍量化的第一季度学习计划阅读详情

netajax1

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

詩和遠方 2014-06-13

打赏
举报

两种方法：

-- 不用 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

*/

chen357313771 2014-06-12

打赏
举报


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

下班

netajax1 2014-06-12

打赏
举报

引用 6 楼 feiazifeiazi 的回复:



--感觉楼主，提供测试脚本.
-- 如果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 行受影响)

*/

多谢大神，请教问题提供测试数据应该的

飞啊子 2014-06-12

打赏
举报



--感觉楼主，提供测试脚本.
-- 如果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 行受影响)

*/

chen357313771 2014-06-12

打赏
举报

引用 4 楼 netajax1 的回复:

[quote=引用 3 楼 chen357313771 的回复:] 你的tb1的id和tb2的哪个id有关系啊，tb2的两个id有父子关系么？

嗯tab2 cerid 为tab1主键id[/quote]item不知跑步跳远两项吧

netajax1 2014-06-12

打赏
举报

引用 3 楼 chen357313771 的回复:

你的tb1的id和tb2的哪个id有关系啊，tb2的两个id有父子关系么？

嗯tab2 cerid 为tab1主键id

chen357313771 2014-06-12

打赏
举报

你的tb1的id和tb2的哪个id有关系啊，tb2的两个id有父子关系么？

netajax1 2014-06-12

打赏
举报

netajax1 2014-06-12

打赏
举报

请教一季度统计语句

相关推荐