我有一个采用这种设计的单表数据库：
CREATE TABLE [dbo].[Facturen](
[UUID] [uniqueidentifier] NOT NULL,
[Invoice] [nvarchar](15) NULL,
[Company] [nvarchar](10) NULL,
[MESSAGEID] [nvarchar](max) NOT NULL,
[SENTON] [datetime] NOT NULL,
[SENDER] [nvarchar](320) NOT NULL,
[RECIPIENT] [nvarchar](320) NOT NULL,
[CLIENT] [nvarchar](15) NOT NULL,
[OK] [bit] NULL,
[ERROR] [nvarchar](max) NULL
此表包含此示例数据：
+--------------------------------------+-----------+---------+---------------------------------------------------------------+-------------------------+-------------------+----------------------+--------------+----+----------------------------------+-----------------------------------------------+------------------------------------------------+------------------------------------------------------------+
| UUID | Invoice | Company | MESSAGEID | SENTON | SENDER | RECIPIENT | CLIENT | OK | ERROR | | | |
+--------------------------------------+-----------+---------+---------------------------------------------------------------+-------------------------+-------------------+----------------------+--------------+----+----------------------------------+-----------------------------------------------+------------------------------------------------+------------------------------------------------------------+
| B7B989C3-AD46-48EC-8399-0000EA265704 | 9940541 | V | <[email protected]> | 2019-06-08 01:00:38.317 | [email protected] | [email protected] | 172.16.2.78 | 1 | NULL | | | |
| 31D995CB-CC0F-402D-ABAC-0003D5B9FC1B | 9230694 | W01 | <[email protected]> | 2019-05-29 22:19:12.603 | [email protected] | [email protected] | 172.16.2.78 | 1 | NULL | | | |
| 8F8AB577-BEFE-4A9C-84E0-00116B8F7E35 | 9307584 | V03 | <[email protected]> | 2019-05-31 18:37:14.013 | [email protected] | [email protected] | 172.16.2.78 | 1 | NULL | | | |
| 6C72A5C6-B39F-47AA-9B28-0012A569AE48 | 9936729 | V | <[email protected]> | 2019-05-23 18:54:39.600 | [email protected] | [email protected] | 172.16.2.78 | 1 | NULL | | | |
| 7627141F-7004-4E26-8061-001B1D21D76F | 1931923 | W03 | <[email protected]> | 2019-06-06 16:56:49.643 | [email protected] | [email protected] | 172.16.0.93 | 1 | NULL | | | |
| FA86911B-A9C2-4449-9101-00248B46D626 | 9937761 | V | <[email protected]> | 2019-05-28 20:12:50.250 | [email protected] | [email protected] | 172.16.2.78 | 0 | Error Type: SMTP | Remote server (63.34.218.7) issued an error. | hMailServer sent: EHLO relay.pacombigroup.com | Remote server replied: Remote server closed connection. |
| 9BF2E3C9-CC2E-4CC8-BA77-002CC9AE9984 | 636393256 | V | <[email protected]> | 2019-05-23 16:51:56.163 | [email protected] | [email protected] | 172.16.2.149 | 1 | NULL | | | |
| 537C2C48-2ED5-40B1-8023-002E1E874237 | 9231441 | W01 | <[email protected]> | 2019-06-04 17:17:17.860 | [email protected] | [email protected] | 172.16.2.57 | 1 | NULL | | | |
| E68F3AF0-7307-486F-832E-00353A4F3A62 | 636393206 | V | <[email protected]> | 2019-05-22 16:56:49.743 | [email protected] | [email protected] | 172.16.2.70 | 0 | Remote server closed connection. | | | |
| 009CE9DA-496A-4761-BE9B-003864D70605 | 9937733 | V | <[email protected]> | 2019-05-28 20:47:44.990 | [email protected] | [email protected] | 172.16.2.78 | 1 | NULL | | | |
| 490A0637-388D-4DD3-9372-0050EC04FAE7 | 9938751 | V | <[email protected]> | 2019-05-31 19:21:29.147 | [email protected] | [email protected] | 172.16.2.78 | 1 | NULL | | | |
| BDEEC909-F708-4C79-BAC8-0057EC803252 | 9231144 | W01 | <[email protected]> | 2019-06-03 15:15:26.007 | [email protected] | [email protected] | 172.16.2.57 | 1 | NULL | | | |
| 7212EDE1-4500-4A03-B27F-005E67EE8528 | 9231612 | W01 | <[email protected]> | 2019-06-04 18:10:13.913 | [email protected] | [email protected] | 172.16.2.57 | 1 | NULL | | | |
| FFC0C644-4A43-4502-820E-005F32685CE7 | 311909810 | V | <[email protected]> | 2019-06-06 21:44:16.193 | [email protected] | [email protected] | 172.16.2.78 | 1 | NULL | | | |
| C7F22163-F776-484E-8139-00615FFC3F84 | 9940777 | V | <[email protected]> | 2019-06-08 00:08:30.017 | [email protected] | [email protected] | 172.16.2.78 | 1 | NULL | | | |
| AD9415D7-126C-45E6-B829-0065124EA70C | 311908978 | V | <[email protected]> | 2019-05-23 21:32:49.513 | [email protected] | [email protected] | 172.16.2.78 | 1 | NULL | | | |
| 247A6385-671D-4AF7-BEBA-0075BF12320C | 9940891 | V | <[email protected]> | 2019-06-11 11:24:14.803 | [email protected] | [email protected] | 172.16.2.108 | 1 | NULL | | | |
| A24844C1-153C-4DC7-812E-0079EAD2DDD5 | 9230357 | W01 | <[email protected]> | 2019-05-28 18:17:11.330 | [email protected] | [email protected] | 172.16.2.57 | 1 | NULL | | | |
| B41D3BC1-56E4-49F3-A838-007C45565A87 | 311909841 | V | <[email protected]> | 2019-06-06 21:37:16.117 | [email protected] | [email protected] | 172.16.2.78 | 1 | NULL | | | |
| 1E8926C6-F4E0-4DDE-985A-0089063BDBA4 | 9937563 | V | <[email protected]> | 2019-05-27 19:24:38.643 | [email protected] | [email protected] | 172.16.2.78 | 1 | NULL | | | |
| 47A8CC55-7E25-4C81-AAC2-008C1E62E6F6 | 311909072 | V | <[email protected]> | 2019-05-24 23:26:32.157 | [email protected] | [email protected] | 172.16.2.78 | 1 | NULL | | | |
| FDDE4FAB-6B84-4D29-AA62-008E0F77B66F | 9937220 | V | <[email protected]> | 2019-05-24 19:22:13.560 | [email protected] | [email protected] | 172.16.2.78 | 1 | NULL | | | |
| E720070F-B3F5-42D6-A899-0099CD379C09 | 9229900 | W01 | <[email protected]> | 2019-05-27 15:08:03.220 | [email protected] | [email protected] | 172.16.2.57 | 1 | NULL | | | |
| D04BD8C1-6BBF-4DB1-A6C4-009E8CECCC63 | 9505925 | V05 | <[email protected]> | 2019-05-24 21:56:22.550 | [email protected] | [email protected] | 172.16.2.78 | 1 | NULL | | | |
| 37E35F68-BB22-4C06-BA57-009ECC4FF47A | 9407267 | V04 | <[email protected]> | 2019-05-24 21:09:08.763 | [email protected] | [email protected] | 172.16.2.78 | 1 | NULL | | | |
| 207D930A-1225-4768-8ABF-00A114BC85FA | 636393442 | V | <[email protected]> | 2019-06-04 10:51:37.830 | [email protected] | [email protected] | 172.16.2.76 | 1 | NULL | | | |
| C67CA5CF-4AF9-4EF7-B40E-00A69BBCC351 | 9939771 | V | <[email protected]> | 2019-06-05 20:16:55.193 | [email protected] | [email protected] | 172.16.2.78 | 1 | NULL | | | |
| CDC62243-8B9C-496C-86BB-00A85BABBC0A | 9940006 | V | <[email protected]> | 2019-06-05 18:52:13.953 | [email protected] | [email protected] | 172.16.2.78 | 1 | NULL | | | |
| 93036181-5268-4548-9A8F-00B09CCDD316 | 9939875 | V | <[email protected]> | 2019-06-05 19:06:25.580 | [email protected] | [email protected] | 172.16.2.78 | 1 | NULL | | | |
| 6A0A7119-8CF4-451D-A12C-00B15E876CF0 | 9937709 | V | <[email protected]> | 2019-05-27 19:46:08.007 | [email protected] | [email protected] | 172.16.2.78 | 1 | NULL | | | |
| 41CF9249-0101-4AE2-ACEE-00B3A045BEEF | 9938746 | V | <[email protected]> | 2019-05-31 20:04:14.687 | [email protected] | [email protected] | 172.16.2.78 | 1 | NULL | | | |
| B9C1649A-4CAF-4A0C-AE78-00B616030A57 | 9232022 | W01 | <[email protected]> | 2019-06-06 17:21:31.590 | [email protected] | [email protected] | 172.16.2.57 | 1 | NULL | | | |
| 9070853E-1659-4208-B200-00C24BA5FDE1 | 9229059 | W01 | <[email protected]> | 2019-05-22 17:08:26.467 | [email protected] | [email protected] | 172.16.2.57 | 1 | NULL | | | |
| B93C4E32-6EF0-44B7-B4EA-00C8CE642FCF | 9938830 | V | <[email protected]> | 2019-06-01 12:00:33.080 | [email protected] | [email protected] | 172.16.0.83 | 1 | NULL | | | |
| 8600D2E7-A602-4032-811D-00CA17A2C559 | 9940767 | V | <[email protected]> | 2019-06-08 00:25:59.760 | [email protected] | [email protected] | 172.16.2.78 | 1 | NULL | | | |
| A40A621E-0E97-4FD7-B892-00D7CF9ABE05 | 9231322 | W01 | <[email protected]> | 2019-06-03 17:21:37.307 | [email protected] | [email protected] | 172.16.2.57 | 1 | NULL | | | |
| 51F069BC-977D-4C72-82F4-00DBFB3F498B | 9936699 | V | <[email protected]> | 2019-05-23 19:16:51.480 | [email protected] | [email protected] | 172.16.2.78 | 1 | NULL | | | |
| EF290E19-1446-419F-8492-00DF704A4D8B | 9232337 | W01 | <[email protected]> | 2019-06-07 17:28:13.777 | [email protected] | [email protected] | 172.16.2.57 | 1 | NULL | | | |
| C98B9027-DA45-4E24-B22C-00EC6FD16C21 | 9007771 | V00 | <[email protected]> | 2019-05-24 18:17:53.600 | [email protected] | [email protected] | 172.16.2.78 | 1 | NULL | | | |
| 95B8C712-1827-4E3D-90A7-00F499086956 | 9008157 | V00 | <[email protected]> | 2019-05-31 18:13:06.860 | [email protected] | [email protected] | 172.16.2.78 | 1 | NULL | | | |
+--------------------------------------+-----------+---------+---------------------------------------------------------------+-------------------------+-------------------+----------------------+--------------+----+----------------------------------+-----------------------------------------------+------------------------------------------------+------------------------------------------------------------+
我想创建一个查询，它向我显示 OK 列中具有 'true' 的记录数和 OK 列中具有 'false' 的记录数。这一切都应该是基于过去 7 天的工作日的汇总，包括零。
例如：
+---------+-----+--------+
| WEEKDAY | OK | NOT OK |
+---------+-----+--------+
| Sunday | 0 | 0 |
| Monday | 100 | 7 |
| Tuesday | 95 | 18 |
+---------+-----+--------+
请帮我看看这个查询代码？
检查下面的脚本。这应该有效。
SELECT FORMAT(SENTON, 'dddd') WEEKDAY,
SUM(CASE WHEN OK = 'true' THEN 1 ELSE 0 END) 'OK',
SUM(CASE WHEN OK = 'false' THEN 1 ELSE 0 END) 'NOT OK'
FROM Facturen
WHERE SENTON BETWEEN <start date> AND <end date>
GROUP BY FORMAT(SENTON, 'dddd')
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句