mysql - 列名不明确

Question

我有一个查询，其中“月份”列不明确。我正在尝试以下查询。请检查并帮助我。

SELECT SC.name as sc,
       B.name as brand,
       COUNT(T.id) as tc,
       SUM(IF(is_category_present = 1, 1, 0)) as BASE
FROM outlet_categories OC 
     INNER JOIN transactions T ON OC.outlet_id = T.outlet_id 
     inner join outlets O on O.id = T.outlet_id 
     inner join brands B on T.brand_id = B.id 
     inner join sale_channels SC on SC.id =  O.sale_channel_id
WHERE T.month = month

Answer 1

限定所有列名称。目前尚不清楚正确的名称是什么，但这些需要表别名:

SELECT SC.name as sc, B.name as brand,
       COUNT(T.id) as tc,
-------^ Looks like an aggregation query, but there is no `GROUP BY`.
       SUM(IF(is_category_present = 1, 1, 0)) as BASE
--------------^
FROM outlet_categories OC INNER JOIN
     transactions T
     ON OC.outlet_id = T.outlet_id INNER JOIN
     outlets O 
     ON O.id = T.outlet_id INNER JOIN
     brands B 
     ON T.brand_id = B.id INNER JOIN
     sale_channels SC 
     ON SC.id =  O.sale_channel_id
WHERE T.month = month;
----------------^

关于 MySQL 的三个注意事项:

• 您的 IF() 不是必需的。
• 我猜测 month 是一个变量。如果是这样，请将其命名为其他名称。
• 大概，您需要一个 GROUP BY。

更好的查询版本:

SELECT SC.name as sc, B.name as brand, COUNT(T.id) as tc,
       SUM(??.is_category_present) as BASE
FROM outlet_categories OC INNER JOIN
     transactions T
     ON OC.outlet_id = T.outlet_id INNER JOIN
     outlets O 
     ON O.id = T.outlet_id INNER JOIN
     brands B 
     ON T.brand_id = B.id INNER JOIN
     sale_channels SC 
     ON SC.id =  O.sale_channel_id
WHERE T.month = v_month
GROUP BY SC.name, B.name;

变量month已重命名为v_month，因此它不会与列名混淆。 MySQL 会将 T.month = Month 解释为 T.month = T.month —— 假设 month 仅在 T 中 表。而且，我猜测 T.month = T.month 并不是您的本意。