mysql - 并集期间出现重复的列名错误

Question

我正在使用此查询来计算 2 个表的行数，在查询末尾使用联合和别名，但它不起作用:

SELECT COUNT(*) FROM (
(SELECT  * FROM bills INNER JOIN cats INNER JOIN suppliers
INNER JOIN new_cards
WHERE new_cards.`Card_Code` LIKE '%8%'
AND bills.`Sup_ID` = suppliers.`Sup_ID`
AND new_cards.`Sup_ID`  = suppliers.`Sup_ID`
AND cats.`Cat_ID` = bills.`Cat_ID`
AND bills.`Cat_ID` = cats.`Cat_ID`
AND new_cards.`Bill_ID` = bills.`Bill_ID`)
UNION
(SELECT * FROM bills INNER JOIN cats INNER JOIN suppliers
INNER JOIN sold_cards WHERE
sold_cards.`Card_Code` LIKE '%8%'
AND bills.`Sup_ID` = suppliers.`Sup_ID`
AND sold_cards.`Sup_ID`  = suppliers.`Sup_ID`
AND cats.`Cat_ID` = bills.`Cat_ID`
AND bills.`Cat_ID` = cats.`Cat_ID`
AND sold_cards.`Bill_ID` = bills.`Bill_ID`)
) w

错误是

Error Code: 1060
Duplicate column name 'Cat_ID'

Answer 1

似乎在 cats 和 bills 表中都有一个名为 Cat_ID 的列。当您在表的联接上执行 select * 时，您将获得重复的列名。

一些可能的解决方案:

• 重命名其中一个表中的 Cat_ID 列
• 明确命名您选择的列，即使用“bills.Cat_ID AS new_name, ...”代替“*”。