mysql - 加入两个查询与联合都只返回单个值?
问题描述
我通过分别执行两个查询合并了两个查询,它返回 3 列(月份、计数、目标),它返回了 2 条正确的记录。但是使用 UNION ALL 它只返回一个值:
我的查询:
select s.month,COALESCE(max(s.count), NULL, 0) as count, COALESCE(max(s.targets), NULL, 0) as target from
(SELECT MONTHNAME(tm.meeting_date) as month, count(DISTINCT tm.meeting_id) as count, NULL as targets
FROM tig as t
left join tig_meeting as tm ON t.tig_code = tm.tig_code
WHERE tm.meeting_date IS NOT NULL AND tm.meeting_date != 0 AND t.school_district = 'Islamabad C3'
AND (tm.meeting_date BETWEEN "2018-07-01" AND "2018-09-30") AND tm.key_support <> 'QuarterlyTigMeetings' AND tm.key_support <> 'QTIG_by_SSA'
AND tm.key_support <> 'MTIG_by_SSA' AND tm.meeting_no NOT LIKE '%RF'
GROUP BY MONTH(tm.meeting_date)
UNION ALL
SELECT mpt.month, NULL as count, SUM(mpt.monthly_target) as targets
FROM meeting_plan_targets mpt
LEFT JOIN meeting_plans mp ON mp.id = mpt.meeting_plan_id
WHERE mp.district = 'Islamabad C3' AND mp.module_title = 1 AND mp.year = 2018 AND mp.quarter = "Quarter 4" GROUP BY mpt.month) s
group by MONTH(s.month)
供参考,请查看附件图片
解决方案
你可以试试下面
select s.`month`,COALESCE(max(s.count),0) as count, COALESCE(max(s.targets),0) as target from
(
SELECT MONTHNAME(tm.meeting_date) as `month`,MONTH(tm.meeting_date) as monnum,count(DISTINCT tm.meeting_id) as count, NULL as targets
FROM tig as t
left join tig_meeting as tm ON t.tig_code = tm.tig_code
WHERE tm.meeting_date IS NOT NULL AND tm.meeting_date != 0 AND t.school_district = 'Islamabad C3'
AND (tm.meeting_date BETWEEN '2018-07-01' AND '2018-09-30') AND tm.key_support <> 'QuarterlyTigMeetings' AND tm.key_support <> 'QTIG_by_SSA'
AND tm.key_support <> 'MTIG_by_SSA' AND tm.meeting_no NOT LIKE '%RF'
GROUP BY MONTHNAME(tm.meeting_date),MONTH(tm.meeting_date)
UNION ALL
SELECT MONTHNAME(mpt.month), MONTH(mpt.month),NULL as count, SUM(mpt.monthly_target) as targets
FROM meeting_plan_targets mpt
LEFT JOIN meeting_plans mp ON mp.id = mpt.meeting_plan_id
WHERE mp.district = 'Islamabad C3' AND mp.module_title = 1 AND mp.year = 2018 AND mp.quarter = 'Quarter 4'
GROUP BY MONTHNAME(mpt.month),MONTH(mpt.month)
) s
group by s.`month`,monnum order by monnum
推荐阅读
- python - 如何在 Django 中更新查询集中所有对象的字段值?
- python - 如何减少比较来自两个不同数据帧的两个句子的函数中的处理时间?
- javascript - Js在一定时间后显示元素 WITH 转换
- go - 为什么这个语句是不可访问的?
- statistics-bootstrap - boot.ci() 无法计算 F1 分数的置信区间
- bash - zsh 奇怪的管道行为(与 bash 相比)
- ios - Swift:如何清除 collectionView 以便在 collectionViewCell 中重用
- if-statement - 错别字3。如何根据语言条件改变流畅的节奏
- angular - SignalR错误:无法启动连接:错误:客户端支持的传输均不受服务器支持
- java - 在网格中查找某些多米诺骨牌的所有位置