我正在使用 MySQL 练习 SQL,并在 SQL 中遇到了奇怪的行为。假设我有一张这样的表:
Delivery table:
+-------------+-------------+------------+-----------------------------+
| delivery_id | customer_id | order_date | customer_pref_delivery_date |
+-------------+-------------+------------+-----------------------------+
| 1 | 1 | 2019-08-01 | 2019-08-02 |
| 2 | 5 | 2019-08-02 | 2019-08-02 |
| 3 | 1 | 2019-08-11 | 2019-08-11 |
| 4 | 3 | 2019-08-24 | 2019-08-26 |
| 5 | 4 | 2019-08-21 | 2019-08-22 |
| 6 | 2 | 2019-08-11 | 2019-08-13 |
+-------------+-------------+------------+-----------------------------+
我的查询:
SELECT COUNT(*) as imm, count(*) over() as all_t
FROM
Delivery
WHERE order_date = customer_pref_delivery_date
Result :
+-----+-------+
| imm | all_t |
+-----+-------+
| 2 | 1 |
+-----+-------+
我期望over()函数将覆盖整个表,在本例中返回 6,但由于某种原因它只返回 1。这种行为有解释吗?
窗口函数:
count(*) over() as all_t
对查询结果进行操作:
SELECT COUNT(*) as imm
FROM Delivery
WHERE order_date = customer_pref_delivery_date
它只有 1 行(1 列),这就是为什么你得到 1 结果。
我相信您正在寻找的是条件聚合:
SELECT COUNT(CASE WHEN order_date = customer_pref_delivery_date THEN 1 END) AS imm,
COUNT(*) AS all_t -- here it's the aggregate function COUNT()
FROM Delivery;
See the .
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)