mysql - 不同表的Mysql不同Counts。1个查询可以吗?
问题描述
MySql 生产服务器版本:5.7.14-google-log (Google)
我可以在 Mysql 版本 8 上进行测试
我有这些表:
- nms_alert (id, user_attended_id)
- nms_alert_not_attend (nms_alert_id, not_attend_id)
- nms_employee_log(开始、结束、zone_id)
我想要类似的东西:
SELECT DISTINCT
# COUNT(accept),<-- How to find this?
# COUNT(reject),<-- How to find this?
nms_employee_log.id as idLog,
nms_employee_log.begin as employeeBegin,
nms_employee_log.end as employeeEnd,
nms_employee_log.zone_id
FROM nms_employee_log where employee_id= 11
(GROUP BY idLog)
“接受”的地方是:
SELECT DISTINCT
nms_alert.id as accept,
nms_employee_log.id as idLog,
nms_alert.alert_begin as dataAlert,
nms_employee_log.begin as employeeBegin,
nms_employee_log.end as employeeEnd,
nms_employee_log.zone_id
FROM `nms_alert`
INNER JOIN nms_employee_log on ( nms_employee_log.begin <= alert_begin AND nms_employee_log.end >= alert_begin )
WHERE nms_employee_log.employee_id= 11 AND INSTR( nms_alert.sent_to_zones , nms_employee_log.zone_id )
AND nms_alert.user_attended_id=3
ORDER BY nms_employee_log.id DESC
accept idLog employeeBegin employeeEnd zone_id
64077 805 2019-12-16 15:10:04 2019-12-17 19:32:24 25
64077 766 2019-12-16 15:10:04 2019-12-16 16:16:07 25
62202 712 2019-12-13 20:18:28 2019-12-13 20:47:04 25
62202 709 2019-12-13 20:18:28 2019-12-13 20:46:41 25
55526 530 2019-12-03 16:13:43 2019-12-03 19:40:59 25
53800 427 2019-11-28 15:08:00 2019-11-29 14:28:07 24
53800 426 2019-11-28 15:08:00 2019-11-29 14:28:07 26
53824 425 2019-11-28 15:08:00 2019-11-29 14:28:07 25
53800 425 2019-11-28 15:08:00 2019-11-29 14:28:07 25
53579 389 2019-11-27 07:42:24 2019-11-27 14:09:45 24
53437 386 2019-11-26 15:17:04 2019-11-27 07:48:32 25
52570 332 2019-11-22 19:15:54 2019-11-23 10:23:52 25
52596 332 2019-11-22 19:15:54 2019-11-23 10:23:52 25
52535 328 2019-11-22 13:33:31 2019-11-22 21:18:04 25
52538 328 2019-11-22 13:33:31 2019-11-22 21:18:04 25
52570 328 2019-11-22 13:33:31 2019-11-22 21:18:04 25
52515 327 2019-11-22 13:33:31 2019-11-22 21:18:04 24
52515 323 2019-11-22 13:33:31 2019-11-22 16:04:56 24
52399 306 2019-11-21 12:20:01 2019-11-22 08:14:38 25
52328 306 2019-11-21 12:20:01 2019-11-22 08:14:38 25
52337 306 2019-11-21 12:20:01 2019-11-22 08:14:38 25
52396 306 2019-11-21 12:20:01 2019-11-22 08:14:38 25
52328 305 2019-11-21 12:20:01 2019-11-22 08:14:38 26
52399 305 2019-11-21 12:20:01 2019-11-22 08:14:38 26
52328 304 2019-11-21 12:20:01 2019-11-22 08:14:38 27
和“拒绝”是:
SELECT DISTINCT
nms_alert.id as reject, #<-- Count Rejected
nms_employee_log.id as idLog,
nms_alert.alert_begin as dataAlert,
nms_employee_log.begin as employeeBegin,
nms_employee_log.end as employeeEnd,
nms_employee_log.zone_id
FROM `nms_alert`
INNER JOIN nms_alert_not_attend on nms_alert.id = nms_alert_not_attend.nms_alert_id
INNER JOIN nms_employee_log on ( nms_employee_log.begin <= alert_begin AND nms_employee_log.end >= alert_begin )
WHERE nms_employee_log.employee_id= 11 AND INSTR( nms_alert.sent_to_zones , nms_employee_log.zone_id )
AND nms_alert_not_attend.not_attend_id = 3
ORDER BY nms_employee_log.id DESC
reject idLog employeeBegin employeeEnd zone_id
64757 803 2019-12-16 20:05:07 2019-12-17 19:30:29 27
64089 784 2019-12-16 16:16:35 2019-12-16 19:23:44 27
64187 784 2019-12-16 16:16:35 2019-12-16 19:23:44 27
64092 783 2019-12-16 16:16:35 2019-12-16 19:23:44 25
64175 783 2019-12-16 16:16:35 2019-12-16 19:23:44 25
64177 783 2019-12-16 16:16:35 2019-12-16 19:23:44 25
64181 783 2019-12-16 16:16:35 2019-12-16 19:23:44 25
64089 780 2019-12-16 16:16:35 2019-12-16 19:16:12 27
64092 778 2019-12-16 16:16:35 2019-12-16 19:16:12 25
64175 778 2019-12-16 16:16:35 2019-12-16 19:16:12 25
64177 778 2019-12-16 16:16:35 2019-12-16 19:16:12 25
64181 778 2019-12-16 16:16:35 2019-12-16 19:16:12 25
64083 768 2019-12-16 15:10:04 2019-12-16 16:16:07 27
64012 761 2019-12-16 13:20:13 2019-12-16 14:32:38 25
64010 756 2019-12-16 12:59:37 2019-12-16 13:20:06 25
62262 728 2019-12-13 21:44:54 2019-12-13 21:57:14 25
62223 723 2019-12-13 20:50:27 2019-12-13 21:44:32 25
62227 723 2019-12-13 20:50:27 2019-12-13 21:44:32 25
62223 720 2019-12-13 20:50:27 2019-12-13 21:26:39 25
62227 720 2019-12-13 20:50:27 2019-12-13 21:26:39 25
62202 712 2019-12-13 20:18:28 2019-12-13 20:47:04 25
62202 709 2019-12-13 20:18:28 2019-12-13 20:46:41 25
56664 631 2019-12-05 10:45:09 2019-12-07 22:03:28 26
56657 630 2019-12-05 10:45:09 2019-12-07 22:03:28 25
56661 630 2019-12-05 10:45:09 2019-12-07 22:03:28 25
我需要的结果:
accept reject idLog employeeBegin employeeEnd zone_id
1 0 805 2019-12-16 15:10:04 2019-12-17 19:32:24 25
0 1 803 2019-12-16 20:05:07 2019-12-17 19:30:29 27
0 2 784 2019-12-16 16:16:35 2019-12-16 19:23:44 27
0 4 783 2019-12-16 16:16:35 2019-12-16 19:23:44 25
0 1 780 2019-12-16 16:16:35 2019-12-16 19:16:12 27
0 4 778 2019-12-16 16:16:35 2019-12-16 19:16:12 25
0 1 768 2019-12-16 15:10:04 2019-12-16 16:16:07 27
1 0 766 2019-12-16 15:10:04 2019-12-16 16:16:07 25
0 1 761 2019-12-16 13:20:13 2019-12-16 14:32:38 25
0 0 760 2019-12-16 13:20:13 2019-12-16 14:32:38 26
0 0 758 2019-12-16 12:59:37 2019-12-16 13:20:06 27
0 0 757 2019-12-16 12:59:37 2019-12-16 13:20:06 28
0 1 756 2019-12-16 12:59:37 2019-12-16 13:20:06 25
0 0 755 2019-12-16 12:59:37 2019-12-16 13:20:06 26
0 0 754 2019-12-13 21:57:22 2019-12-16 12:57:30 27
0 0 753 2019-12-13 21:57:22 2019-12-16 12:57:30 28
0 0 752 2019-12-13 21:57:22 2019-12-16 12:57:30 21
0 0 751 2019-12-13 21:57:22 2019-12-16 12:57:30 22
0 0 730 2019-12-13 21:44:54 2019-12-13 21:57:14 23
0 0 729 2019-12-13 21:44:54 2019-12-13 21:57:14 24
0 1 728 2019-12-13 21:44:54 2019-12-13 21:57:14 25
0 0 727 2019-12-13 21:44:54 2019-12-13 21:57:14 26
0 0 726 2019-12-13 20:50:27 2019-12-13 21:44:32 27
0 0 725 2019-12-13 20:50:27 2019-12-13 21:44:32 23
0 0 724 2019-12-13 20:50:27 2019-12-13 21:44:32 24
0 2 723 2019-12-13 20:50:27 2019-12-13 21:44:32 25
0 0 722 2019-12-13 20:50:27 2019-12-13 21:26:39 26
0 0 721 2019-12-13 20:50:27 2019-12-13 21:26:39 27
0 2 720 2019-12-13 20:50:27 2019-12-13 21:26:39 25
0 0 719 2019-12-13 20:50:27 2019-12-13 21:26:39 26
0 0 718 2019-12-13 20:47:23 2019-12-13 20:50:01 27
0 0 717 2019-12-13 20:47:23 2019-12-13 20:50:01 28
0 0 716 2019-12-13 20:47:23 2019-12-13 20:50:01 21
0 0 715 2019-12-13 20:47:23 2019-12-13 20:50:01 22
0 0 714 2019-12-13 20:18:28 2019-12-13 20:47:04 23
0 0 713 2019-12-13 20:18:28 2019-12-13 20:47:04 24
1 1 712 2019-12-13 20:18:28 2019-12-13 20:47:04 25
0 0 711 2019-12-13 20:18:28 2019-12-13 20:47:04 26
0 0 710 2019-12-13 20:18:28 2019-12-13 20:46:42 27
1 1 709 2019-12-13 20:18:28 2019-12-13 20:46:41 25
0 0 708 2019-12-13 20:18:28 2019-12-13 20:46:41 25
0 0 707 2019-12-13 20:18:28 2019-12-13 20:46:42 27
0 0 706 2019-12-13 19:06:04 2019-12-13 20:18:06 27
0 0 705 2019-12-13 19:06:04 2019-12-13 20:18:06 25
0 0 704 2019-12-13 19:06:04 2019-12-13 20:18:06 27
0 0 703 2019-12-13 19:06:04 2019-12-13 20:18:06 25
0 0 702 2019-12-13 14:22:59 2019-12-13 19:05:43 27
0 0 701 2019-12-13 14:22:59 2019-12-13 19:05:43 25
0 0 700 2019-12-13 14:22:59 2019-12-13 19:05:43 25
0 0 699 2019-12-13 14:22:59 2019-12-13 19:05:43 26
0 0 698 2019-12-12 14:43:13 2019-12-13 14:22:36 27
0 0 697 2019-12-12 14:43:13 2019-12-13 14:22:36 25
0 0 696 2019-12-12 14:43:13 2019-12-13 14:22:36 27
0 0 695 2019-12-12 14:43:13 2019-12-13 14:22:36 27
0 0 694 2019-12-12 14:11:20 2019-12-12 14:42:27 25
0 0 693 2019-12-12 14:11:20 2019-12-12 14:42:27 27
0 0 692 2019-12-12 14:11:20 2019-12-12 14:42:27 25
0 0 691 2019-12-12 14:11:20 2019-12-12 14:42:27 27
0 0 690 2019-12-12 14:08:50 2019-12-12 14:10:54 25
0 1 631 2019-12-05 10:45:09 2019-12-07 22:03:28 26
0 2 630 2019-12-05 10:45:09 2019-12-07 22:03:28 26
1 0 530 2019-12-03 16:13:43 2019-12-03 19:40:59 26
1 0 427 2019-11-28 15:08:00 2019-11-29 14:28:07 24
1 0 426 2019-11-28 15:08:00 2019-11-29 14:28:07 26
2 0 425 2019-11-28 15:08:00 2019-11-29 14:28:07 25
1 0 389 2019-11-27 07:42:24 2019-11-27 14:09:45 24
1 0 386 2019-11-26 15:17:04 2019-11-27 07:48:32 25
2 0 332 2019-11-22 19:15:54 2019-11-23 10:23:52 25
3 0 328 2019-11-22 13:33:31 2019-11-22 21:18:04 25
1 0 327 2019-11-22 13:33:31 2019-11-22 21:18:04 24
1 0 323 2019-11-22 13:33:31 2019-11-22 16:04:56 24
4 0 306 2019-11-21 12:20:01 2019-11-22 08:14:38 25
2 0 305 2019-11-21 12:20:01 2019-11-22 08:14:38 26
0 1 304 2019-11-21 12:20:01 2019-11-22 08:14:38 27
====
实现这一目标的最佳查询是什么?
以及最好的表现?
解决方案
从最初的问题猜测,这应该可以使用CTE
(如果你在 MySQL 8+ 上)或子选择和CASE SUM
SELECT
SUM(case when accept is not null then 1 else 0 end) as countAccept
,SUM(case when reject is not null then 1 else 0 end) as countReject
FROM (
SELECT DISTINCT
nms_alert.id as accept,
nms_alert_not_attend.nms_alert_id as reject,
nms_alert.alert_begin,
nms_employee_log.zone_id,
nms_employee_log.device_type,
nms_employee_log.model,
nms_employee_log.wifi_network
FROM `nms_alert`
INNER JOIN nms_alert_not_attend on nms_alert.id = nms_alert_not_attend.nms_alert_id
INNER JOIN nms_employee_log on ( nms_employee_log.begin <= alert_begin AND nms_employee_log.end >= alert_begin )
WHERE nms_alert_not_attend.not_attend_id = 3
AND nms_alert.user_attended_id=3
AND nms_employee_log.employee_id= 11
) s
推荐阅读
- mysql - 驱动程序发生异常:找不到驱动程序
- python - 代码导航在vscode python中不起作用
- r - 从 r 中的 qda 函数进行预测
- wix - 对于 Wix Toolset 和 UpgradeVersion 标签是 Property ever reset
- css - 如何更改 reactjs 按钮组件的字体系列?
- node.js - 11 MB 数据库的 NodeJS 堆内存不足
- javascript - React/MaterialUI - TypeError:无法读取未定义的属性“地图”
- javascript - 在 React 中对状态对象进行排序
- excel - 当单个单元格包含多个字符串时与字符串关联的值的总和
- angular - VS Code 和 Angular CLI:打开或切换到文件会重新启动构建