mysql - 计算具有相同 ID 和 Date <= CURDATE() 的行

Question

如何编写结合了两个 SELECT 语句的 SQL 查询？

我有这张 table

+-----+--------------+----------------+
| ID  |     DATE     | other props... |
+=====+==============+
|  1  |  2015-01-02  | 
+-----+--------------+
|  1  |  2015-02-03  |
+-----+--------------+
|  2  |  2015-03-16  | 
+-----+--------------+
|  3  |  2015-02-01  | 
+-----+--------------+
|  3  |  2016-05-14  | 
+-----+--------------+

想数数

1. 有多少行具有相同的ID

2. 有多少行日期 <= 当前日期

示例结果:

+-----+-----------------+
| ID  | COUNT | expired |
+=====+=================+
|  1  |   2   |    2    | 
+-----+-----------------+
|  2  |   1   |    0    |
+-----+-----------------+
|  3  |   2   |    1    |
+-----+-----------------+

我使用这些查询来完成每项任务:

计数行数:

SELECT `ID`, COUNT(*) AS `count` 
FROM `table`
GROUP BY `ID`

计数过期:

SELECT `ID`, COUNT(*) AS `expired` 
FROM `table`
WHERE `DATE` <= CURDATE(
GROUP BY `ID`

Answer 1

您可以使用此查询:

SELECT
  ID,
  COUNT(*) AS cnt,
  SUM(`date`<CURDATE()) AS expired
FROM
  tablename
GROUP BY
  ID

COUNT(*) 将计算所有行，而 SUM() 将仅计算过期行(date<CURDATE() 将在日期过期时计算为 1，否则计算为 0)

请查看工作 fiddle here .