以下sql有什么问题:
SELECT DATE(NOW())-INTERVAL seq.seq DAY sdate, u.order_type, IFNULL(c,0) c
FROM (
SELECT 0 AS seq
UNION ALL SELECT 1 UNION ALL SELECT 2
UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6
) seq
LEFT JOIN (
SELECT t.order_type, DATE_FORMAT(t.order_date, '%Y-%m-%d') as tdate, count(*) c
FROM `order` t
WHERE t.order_type IN (10,11)
GROUP BY t.order_type, tdate
) u ON u.tdate = sdate
为什么我会得到
Error Code: 1054. Unknown column 'sdate' in 'on clause'
?基本上,我试图选择每种订单类型每天的订单数量。如果在给定的日期没有给定类型的订单,则结果为0:
| date | order_type | count |
|------------|------------|-------|
| 2018-11-09 | 10 | 2 |
| 2018-11-09 | 11 | 0 |
| 2018-11-10 | 10 | 0 |
| 2018-11-10 | 11 | 0 |
| 2018-11-11 | 10 | 1 |
| 2018-11-11 | 11 | 2 |
| 2018-11-12 | 10 | 3 |
| 2018-11-12 | 11 | 2 |
最佳答案
sdate
在SELECT子句中定义,因此在FROM子句(FROM is calculated waaaaaaaay before SELECT)中不可用。您需要指定联接条件中的计算:
ON u.tdate = DATE(NOW())-INTERVAL seq.seq DAY
关于mysql - 当我在SELECT中定义它时,为什么MySQL给我“错误代码:1054。'on子句'中的未知列'x'”?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/53301473/