CREATE table #t (date datetime,卓望 int,播思 int,pica int,宇天 int,精点 int,从兴 int,融创 int,拓维 int,网维 int,A8 int,广告 int,视频基地 int,音乐基地 int,阅读基地 int,游戏基地 int) insert #t values ('2009-2-6 17:29:37' ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2010-3-5 17:39:53' ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2010-3-5 17:39:53' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 )
insert #t values ('2010-3-5 17:39:53' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 )
insert #t values ('2019-1-6 17:48:42' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2019-8-6 17:59:57' ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2017-3-7 18:14:21' ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2017-3-7 18:14:21' ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2017-3-7 18:14:21' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2017-3-7 18:14:21' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 )
insert #t values ('2012-3-6 18:25:45' ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 18:25:45' ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 18:25:45' ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 18:25:45' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 18:25:45' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 18:25:45' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 )
insert #t values ('2018-8-6 18:33:08' ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2018-8-6 18:33:08' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2018-8-6 18:48:53' ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2018-8-6 18:48:53' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2018-9-6 18:57:19' ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 19:22:04' ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2014-9-6 19:24:49' ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2003-7-6 19:37:10' ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 19:48:03' ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 19:48:03' ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 19:48:03' ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 19:48:03' ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 19:48:03' ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 19:48:03' ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 19:48:03' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 19:48:03' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 19:48:03' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 19:48:03' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 19:48:03' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 )
insert #t values ('2012-3-6 19:48:03' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 )
insert #t values ('2012-3-6 19:48:03' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 )
insert #t values ('2012-3-6 19:48:03' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 )
insert #t values ('2004-4-6 20:11:45' ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2004-4-6 20:11:45' ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2004-4-6 20:11:45' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2004-4-6 20:11:45' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 )
insert #t values ('2015-5-8 0:00:00' ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2004-2-8 0:00:00' ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2001-4-10 0:00:00' ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2018-1-5 0:00:00' ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2019-2-5 0:00:00' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2001-8-5 0:00:00' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 )
insert #t values ('2001-8-5 0:00:00' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 )
insert #t values ('2010-8-4 0:00:00' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-8 16:49:24' ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-8 17:40:20' ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-8 17:40:20' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ) 需要根据时间统计,每个模块每天出现的次数。
GO
IF OBJECT_ID('#T') IS NOT NULL DROP TABLE #T
GO
CREATE table #t (date datetime,卓望 int,播思 int,pica int,宇天 int,精点 int,从兴 int,融创 int,拓维 int,网维 int,A8 int,广告 int,视频基地 int,音乐基地 int,阅读基地 int,游戏基地 int) insert #t values ('2009-2-6 17:29:37' ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2010-3-5 17:39:53' ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2010-3-5 17:39:53' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 )
insert #t values ('2010-3-5 17:39:53' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 )
insert #t values ('2019-1-6 17:48:42' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2019-8-6 17:59:57' ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2017-3-7 18:14:21' ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2017-3-7 18:14:21' ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2017-3-7 18:14:21' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2017-3-7 18:14:21' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 )
insert #t values ('2012-3-6 18:25:45' ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 18:25:45' ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 18:25:45' ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 18:25:45' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 18:25:45' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 18:25:45' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 )
insert #t values ('2018-8-6 18:33:08' ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2018-8-6 18:33:08' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2018-8-6 18:48:53' ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2018-8-6 18:48:53' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2018-9-6 18:57:19' ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 19:22:04' ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2014-9-6 19:24:49' ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2003-7-6 19:37:10' ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 19:48:03' ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 19:48:03' ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 19:48:03' ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 19:48:03' ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 19:48:03' ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 19:48:03' ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 19:48:03' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 19:48:03' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 19:48:03' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 19:48:03' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-6 19:48:03' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 )
insert #t values ('2012-3-6 19:48:03' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 )
insert #t values ('2012-3-6 19:48:03' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 )
insert #t values ('2012-3-6 19:48:03' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 )
insert #t values ('2004-4-6 20:11:45' ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2004-4-6 20:11:45' ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2004-4-6 20:11:45' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2004-4-6 20:11:45' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 )
insert #t values ('2015-5-8 0:00:00' ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2004-2-8 0:00:00' ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2001-4-10 0:00:00' ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2018-1-5 0:00:00' ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2019-2-5 0:00:00' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2001-8-5 0:00:00' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 )
insert #t values ('2001-8-5 0:00:00' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 )
insert #t values ('2010-8-4 0:00:00' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-8 16:49:24' ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-8 17:40:20' ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 )
insert #t values ('2012-3-8 17:40:20' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,0 ,0 ,0 ,0 ,0 ,0 )
GO
SELECT CONVERT(VARCHAR(10),DATE,120) AS 日期
,SUM(卓望) AS 卓望
,SUM(播思) AS 播思
,SUM(pica) AS pica
,SUM(宇天) AS 宇天
,SUM(精点) AS 精点
,SUM(从兴) AS 从兴
,SUM(融创) AS 融创
,SUM(拓维) AS 拓维
,SUM(网维) AS 网维
,SUM(A8) AS A8
,SUM(广告)AS 广告
,SUM(视频基地) AS 视频基地
,SUM(音乐基地) AS 音乐基地
,SUM(阅读基地) AS 阅读基地
,SUM(游戏基地) AS 游戏基地
FROM #T
GROUP BY CONVERT(VARCHAR(10),DATE,120)
/*
日期 卓望 播思 pica 宇天 精点 从兴 融创 拓维 网维 A8 广告 视频基地 音乐基地 阅读基地 游戏基地
2001-04-10 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0
2001-08-05 0 0 0 0 0 0 0 0 0 0 1 0 0 0 1
2003-07-06 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0
2004-02-08 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0
2004-04-06 1 0 1 0 0 0 0 0 1 0 1 0 0 0 0
2009-02-06 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0
2010-03-05 0 1 0 0 0 0 0 0 0 0 0 1 1 0 0
2010-08-04 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0
2012-03-06 3 1 2 1 1 1 1 1 2 2 2 1 1 1 1
2012-03-08 2 0 0 0 0 0 0 0 1 0 0 0 0 0 0
2014-09-06 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0
2015-05-08 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0
2017-03-07 1 0 1 0 0 0 0 0 0 1 0 1 0 0 0
2018-01-05 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0
2018-08-06 2 0 0 0 0 0 0 0 2 0 0 0 0 0 0
2018-09-06 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0
2019-01-06 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0
2019-02-05 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0
2019-08-06 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0
*/
from tb group by [date]