sql - 字段列表中的列“order_date”不明确

Question

我想建立客户保留队列。我有列 customerId 和 order_date。我正在使用自我连接，但它显示了一些错误。

自联接

SELECT 
   p1.customerId AS id,
   MONTH(order_date) as month,
   YEAR(order_date) as year 
FROM
    jugnoo AS p1
        LEFT JOIN
    jugnoo AS p2 ON p1.id = p2.id
WHERE
    p2.id IN (SELECT DISTINCT
                 (customerId) AS id
              FROM
                  jugnoo
              WHERE
                    MONTH(order_date) = 06
                AND YEAR(order_date) = 2017
              order by year(order_date), month(order_date))
order by 3, 2;

预期的 customerId 与条件匹配，但显示错误消息“字段列表中的列 'order_date' 不明确 0.00072 秒”

标签： sqljoinself-join

Answer 1

不需要JOIN。只需使用 p1.id 列代替。

SELECT 
   p1.customerId AS id,
   MONTH(p1.order_date) as month,
   YEAR(p1.order_date) as year 
FROM
    jugnoo AS p1
WHERE
    p1.id IN (SELECT DISTINCT
                  customerId AS id
              FROM
                  jugnoo p2
              WHERE
                    MONTH(p2.order_date) = 06
                AND YEAR(p2.order_date) = 2017
              order by year(p2.order_date), month(p2.order_date))
order by 3, 2;

当涉及多个表时，对所有列进行限定是一种很好的编程习惯。喜欢p1.id而不是仅仅id. 不易出错，更易于阅读和维护。

编辑：（根据要求）

使用相关子查询获取每个月的数据：

SELECT 
   p1.customerId AS id,
   MONTH(p1.order_date) as month,
   YEAR(p1.order_date) as year 
FROM
    jugnoo AS p1
WHERE
    p1.id IN (SELECT DISTINCT
                  p2.customerId AS id
              FROM
                  jugnoo p2
              WHERE
                   MONTH(p2.order_date) = MONTH(p1.order_date) 
                AND YEAR(p2.order_date) = YEAR(p1.order_date))
order by 3, 2;