MSSQL中用WITHAS和PIVOT来统计编辑新闻发布时间详情和总数
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 PVTWHERE AddTime between 2014-11-2
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
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1390
52
How to connect php to mssql database
Oct 23, 2023 pm 12:02 PM
Methods for php to connect to mssql database include using PHP's MSSQL extension, using PDO, etc. Detailed introduction: 1. Use PHP's MSSQL extension method to ensure that PHP has the MSSQL extension installed. You can check whether the mssql extension is enabled in the PHP configuration file (php.ini); 2. Use the PDO method to ensure that PHP has the PDO extension installed. You can check whether the pdo_sqlsrv extension is enabled in the PHP configuration file (php.ini).
How to restore the deleted hosts file
Feb 22, 2024 pm 10:48 PM
Title: How to restore the hosts file after deletion Summary: The hosts file is a very important file in the operating system and is used to map domain names to IP addresses. If you accidentally delete the hosts file, you may be unable to access certain websites or have other network problems. This article will introduce how to recover accidentally deleted hosts file in Windows and Mac operating systems. Text: 1. Restore hosts file in Windows operating system. Hosts file in Windows operating system
Detailed guide to install PHP and configure MSSQL connection on Ubuntu
Feb 29, 2024 am 11:15 AM
Ubuntu is a popular open source operating system commonly used to run servers. Installing PHP and configuring MSSQL connections on Ubuntu is one of the operations that many developers and system administrators often need to do. This article will provide readers with a detailed guide, including the steps to install PHP, set up Apache, install MSSQLServer, etc., and attach specific code examples. Step 1: Install PHP and related extensions First, we need to install PHP and related extensions to support PHP connections
How to edit documents in Tencent Docs? -Tencent Document Editing Document Tutorial Guide
Mar 19, 2024 am 08:19 AM
Does anyone know how to edit documents in Tencent Docs? It doesn't matter if you don't know. Today, the editor will introduce detailed graphic explanations on how to edit documents in Tencent Docs. I hope it can help you. Detailed graphic explanation of editing documents in Tencent Documents 1. First, enter Tencent Documents directly (if you don’t have it, download it now!) and log in directly (QQ and TIM two login methods are supported) 2. After logging in, click Add in the upper right corner No., directly create online documents, online forms, new folders, etc.! 3. Then enter the information according to your needs!
How to implement data statistics and analysis in uniapp
Oct 24, 2023 pm 12:37 PM
How to implement data statistics and analysis in uniapp 1. Background introduction Data statistics and analysis are a very important part of the mobile application development process. Through statistics and analysis of user behavior, developers can have an in-depth understanding of user preferences and usage habits. Thereby optimizing product design and user experience. This article will introduce how to implement data statistics and analysis functions in uniapp, and provide some specific code examples. 2. Choose appropriate data statistics and analysis tools. The first step to implement data statistics and analysis in uniapp is to choose the appropriate data statistics and analysis tools.
How to use SQL statements for data aggregation and statistics in MySQL?
Dec 17, 2023 am 08:41 AM
How to use SQL statements for data aggregation and statistics in MySQL? Data aggregation and statistics are very important steps when performing data analysis and statistics. As a powerful relational database management system, MySQL provides a wealth of aggregation and statistical functions, which can easily perform data aggregation and statistical operations. This article will introduce the method of using SQL statements to perform data aggregation and statistics in MySQL, and provide specific code examples. 1. Use the COUNT function for counting. The COUNT function is the most commonly used
Implementation of linear and pie chart functions in Vue statistical charts
Aug 19, 2023 pm 06:13 PM
The linear and pie chart functions of Vue statistical charts are implemented in the field of data analysis and visualization. Statistical charts are a very commonly used tool. As a popular JavaScript framework, Vue provides convenient methods to implement various functions, including the display and interaction of statistical charts. This article will introduce how to use Vue to implement linear and pie chart functions, and provide corresponding code examples. Linear graph function implementation A linear graph is a type of chart used to display trends and changes in data. In Vue, we can use some excellent
What to do if word document cannot be edited
Mar 19, 2024 pm 09:37 PM
After editing the document, we will save the document to provide convenience for editing and modifying the document next time. Sometimes we can modify it directly after clicking on the edited document, but sometimes for some unknown reason, there is no response no matter how we click on the word document, and the command will not be executed. , what should I do if the word document cannot be edited? Don’t worry, the editor will help you solve this problem. Let’s take a look at the operation process. After opening a Word document, when editing text, you will see a "Restrict Editing" prompt displayed on the right side of the page, as shown in the figure below. 2. You need to cancel editing and you need to know the set password. Click "Stop Protection" below the pop-up prompt, as shown in the figure below. 3. Then enter the password in the "Unprotect Document" dialog box and click OK, as shown in the figure below.
