mysql 日期多条件分组,MySQL日期范围多列计数和按选择语句分组

我有一个查询,我已经工作了一段时间,但我似乎无法理解它.此处的其他答案很好地用于计算具有特定日期范围的金额,然后按日期分组以获取计数.但是,我需要计算两列并按日期分组.

例如,这是我尝试上班的查询：

(SELECT COUNT(*) arrived, DATE(arrived) date, 'arrived' AS source

FROM products

WHERE arrived BETWEEN '2016-01-01' AND '2016-01-31'

GROUP BY DATE(date)

ORDER BY date ASC)

UNION ALL

(SELECT COUNT(*) released, DATE(released) date, 'released' AS source

FROM products

WHERE released BETWEEN '2016-01-01' AND '2016-01-31'

GROUP BY DATE(date)

ORDER BY date ASC)

但是,这将返回以下内容：

arrived date source

3 2016-01-12 arrived

2 2016-01-28 arrived

1 2016-01-29 arrived

1 2016-01-05 released

我需要的是这样的：

date arrived released

2016-01-05 0 1

2016-01-12 3 0

2016-01-28 2 0

2016-01-29 1 0

有什么建议么？谢谢.

解决方法:

您可以将条件聚合应用于UNION ALL操作针对“到达”和“发布”日期获得的派生表：

SELECT `date`,

COUNT(CASE WHEN type = 'arrived' THEN 1 END) AS arrived,

COUNT(CASE WHEN type = 'released' THEN 1 END) AS released

FROM (

SELECT arrived AS `date`, 'arrived' as type

FROM products

WHERE arrived BETWEEN '2016-01-01' AND '2016-01-31'

UNION ALL

SELECT released AS `date`, 'released' as type

FROM products

WHERE released BETWEEN '2016-01-01' AND '2016-01-31') AS t

GROUP BY `date`

标签：sql,mysql,date

来源： https://codeday.me/bug/20191119/2033280.html