MSSQL中用 WITH AS 和PIVOT来统计编辑新闻发布时间详情和总数
时间：2018-11-27 15:58:01
手机看文章
扫描二维码
随时随地手机看文章
[导读]WITH CR AS(
SELECT AddTime,[编辑名称1],[编辑名称2],[编辑名称3]
FROM (SELECT NewsID,AddTime,AuditingUser FROM New
WITH CR AS( SELECT AddTime,[编辑名称1],[编辑名称2],[编辑名称3] FROM (SELECT NewsID,AddTime,AuditingUser FROM News) AS a Pivot (COUNT(a.NewsID) FOR a.AuditingUser in ([编辑名称1],[编辑名称2],[编辑名称3])) as PVT WHERE AddTime between '2014-11-24 00:00:00' and '2014-11-30 23:59:59' GROUP BY AddTime,[编辑名称1],[编辑名称2],[编辑名称3]) SELECT CONVERT(NVARCHAR(50),AddTime,120) as '时间',[编辑名称1],[编辑名称2],[编辑名称3] FROM CR UNION(SELECT '总计' AS AddTime ,SUM(CR.[编辑名称1]) AS '编辑名称1',SUM(CR.[编辑名称2]) AS [编辑名称2],SUM(CR.[编辑名称3]) AS [编辑名称3] FROM CR)
执行前需要把目标数据库的兼容性设置为90（sql2005）以上，否则会报错。
得到的结果：
时间 编辑名称1 编辑名称2 编辑名称3 2014-11-24 08:36:03 1 0 0 2014-11-24 09:42:15 0 0 1 2014-11-24 09:42:31 0 1 0 2014-11-24 09:43:25 1 0 0 2014-11-24 09:45:54 0 0 1 2014-11-24 10:22:13 0 0 1 2014-11-24 10:31:17 0 0 1 2014-11-24 10:39:36 0 0 1 2014-11-24 11:27:12 0 1 0 2014-11-24 11:28:57 0 0 0 2014-11-24 14:28:57 0 1 0 2014-11-24 14:31:05 1 0 0 2014-11-24 14:47:34 0 1 0 2014-11-24 15:35:54 0 1 0 2014-11-24 15:37:25 1 0 0 2014-11-24 15:41:14 1 0 0 2014-11-24 15:49:39 0 1 0 2014-11-24 15:56:30 0 1 0 2014-11-24 16:02:10 1 0 0 2014-11-24 16:17:32 0 0 1 2014-11-24 16:26:50 1 0 0 2014-11-24 16:34:43 0 1 0 2014-11-24 16:41:45 0 0 0 2014-11-24 16:47:42 0 0 0 2014-11-24 17:01:27 0 0 0 2014-11-24 17:17:16 0 0 1 2014-11-24 20:14:35 0 0 0 2014-11-25 08:44:35 0 1 0 2014-11-25 09:18:06 0 1 0 2014-11-25 09:23:49 1 0 0 2014-11-25 10:37:55 0 1 0 2014-11-25 10:50:53 0 0 1 2014-11-25 11:02:30 0 0 1 2014-11-25 11:28:11 0 1 0 2014-11-25 14:28:24 0 1 0 2014-11-25 14:35:09 1 0 0 2014-11-25 14:44:43 0 1 0 2014-11-25 15:10:52 0 0 1 2014-11-25 15:14:03 0 1 0 2014-11-25 15:28:07 0 0 1 2014-11-25 15:34:24 0 0 1 2014-11-25 15:35:08 0 0 1 2014-11-25 15:40:51 0 0 1 2014-11-25 16:06:52 1 0 0 2014-11-25 16:07:40 0 0 1 2014-11-25 16:30:13 0 0 1 2014-11-25 16:44:33 1 0 0 2014-11-25 16:53:33 1 0 0 2014-11-25 17:06:54 1 0 0 2014-11-25 17:12:05 1 0 0 2014-11-25 17:17:20 0 1 0 2014-11-26 08:56:24 1 0 0 2014-11-26 09:01:38 0 1 0 2014-11-26 09:09:53 0 1 0 2014-11-26 09:22:49 1 0 0 2014-11-26 09:31:26 0 1 0 2014-11-26 10:01:38 0 0 1 2014-11-26 10:34:35 1 0 0 2014-11-26 10:42:53 0 0 1 2014-11-26 10:45:15 0 1 0 2014-11-26 11:07:30 0 0 1 2014-11-26 11:26:55 0 1 0 2014-11-26 11:28:44 0 0 1 2014-11-26 14:30:21 0 1 0 2014-11-26 14:50:07 0 1 0 2014-11-26 15:07:40 0 1 0 2014-11-26 15:23:50 1 0 0 2014-11-26 15:35:24 1 0 0 2014-11-26 15:40:41 1 0 0 2014-11-26 15:46:22 0 1 0 2014-11-26 16:15:42 0 0 1 2014-11-26 16:27:36 0 1 0 2014-11-26 16:38:38 1 0 0 2014-11-26 16:39:58 0 0 1 2014-11-26 16:42:17 0 0 1 2014-11-26 16:45:11 0 0 1 2014-11-26 16:51:14 0 0 1 2014-11-27 08:41:46 0 1 0 2014-11-27 08:50:38 1 0 0 2014-11-27 09:41:12 0 1 0 2014-11-27 09:50:59 1 0 0 2014-11-27 09:51:32 0 1 0 2014-11-27 10:36:09 1 0 0 2014-11-27 10:54:57 0 0 1 2014-11-27 14:16:58 1 0 0 2014-11-27 14:45:37 0 1 0 2014-11-27 14:50:22 0 0 1 2014-11-27 14:52:13 0 0 1 2014-11-27 14:55:29 0 1 0 2014-11-27 15:03:23 0 0 1 2014-11-27 15:07:49 0 0 1 2014-11-27 15:19:07 0 0 1 2014-11-27 15:38:14 0 1 0 2014-11-27 15:50:25 0 1 0 2014-11-27 15:58:04 1 0 0 2014-11-27 15:58:48 0 0 1 2014-11-27 16:08:59 0 0 1 2014-11-27 16:14:53 0 1 0 2014-11-27 16:17:10 0 0 1 2014-11-27 16:19:59 1 0 0 2014-11-27 16:41:57 1 0 0 2014-11-28 08:46:40 1 0 0 2014-11-28 09:03:07 0 1 0 2014-11-28 09:27:11 0 1 0 2014-11-28 09:44:44 0 0 1 2014-11-28 09:48:48 0 0 1 2014-11-28 09:53:52 0 1 0 2014-11-28 10:34:09 0 0 1 2014-11-28 10:39:20 0 1 0 2014-11-28 10:51:16 1 0 0 2014-11-28 11:38:25 1 0 0 2014-11-28 11:44:27 1 0 0 2014-11-28 14:36:29 1 0 0 2014-11-28 15:03:20 0 0 1 2014-11-28 15:34:43 0 0 1 2014-11-28 15:40:29 1 0 0 2014-11-28 15:40:42 0 0 1 2014-11-28 15:50:40 1 0 0 2014-11-28 16:07:17 1 0 0 2014-11-28 16:09:41 0 1 0 2014-11-28 16:10:12 0 0 1 2014-11-28 16:27:12 0 0 1 2014-11-28 17:02:38 0 0 1 2014-11-28 21:24:18 0 0 0 2014-11-29 08:31:36 0 0 0 2014-11-29 12:32:32 0 0 1 2014-11-29 12:35:14 0 0 1 2014-11-29 12:44:43 0 0 1 2014-11-29 13:13:16 0 0 1 2014-11-29 13:20:21 0 0 1 2014-11-29 13:30:12 0 0 1 2014-11-29 13:42:31 0 0 1 2014-11-29 20:30:36 0 0 0 2014-11-29 20:50:03 0 0 0 2014-11-29 21:12:30 0 0 1 2014-11-30 12:12:08 0 0 1 2014-11-30 12:30:36 0 0 1 2014-11-30 12:33:36 0 0 1 2014-11-30 12:37:54 0 0 1 2014-11-30 12:43:38 0 0 1 2014-11-30 12:52:18 0 0 1 2014-11-30 12:56:47 0 0 1 2014-11-30 17:55:54 0 0 1 2014-11-30 17:55:55 0 0 1 2014-11-30 18:30:41 0 0 1 2014-11-30 18:48:15 0 0 1 2014-11-30 18:54:55 0 0 1 2014-11-30 18:56:59 0 0 1 2014-11-30 19:15:30 0 0 1 2014-11-30 19:29:41 0 0 1 总计 36 39 66
下载文档
