gpt4 book ai didi

mysql - 带有左连接和并集的 SELECT

转载 作者:行者123 更新时间:2023-11-29 09:59:03 24 4
gpt4 key购买 nike

我有一个查询,该查询返回特定员工类次的发票

SELECT
i.dateTime,i.amount,i.totalProfit,i.shiftID,
i_o.itemID,i_o.quantity,
item.name itemName,
p.full_name

from invoice i

LEFT JOIN
inv_order i_o on i_o.invID=i.invID

LEFT JOIN
`item-service` item on item.itemID = i_o.itemID


LEFT JOIN person p on
p.PID=i.personID

where i.shiftID =97

但是,我需要从员工表中获取员工姓名,而我只有 shiftID。

SELECT
i.dateTime,i.type,i.amount,i.totalProfit,i.shiftID,i_o.itemID,i_o.quantity,item.name itemName,p.full_name

from invoice i

LEFT JOIN
inv_order i_o on i_o.invID=i.invID

LEFT JOIN
`item-service` item on item.itemID = i_o.itemID


LEFT JOIN person p on
p.PID=i.personID

where i.shiftID =97

UNION

SELECT e.name from employee e

left join shift s on s.empID = e.empID

where s.shiftID =97

mysql返回这个错误

使用的 SELECT 语句具有不同的列数

最佳答案

错误消息对于问题非常清楚:您的第一个查询返回 8 列数据,而第二个查询仅返回 1 列,因此您无法对它们进行UNION。看来您可能只想通过 shiftJOIN employee 表,例如

SELECT i.dateTime
,i.type
,i.amount
,i.totalProfit
,i.shiftID
,i_o.itemID
,i_o.quantity
,item.name AS itemName
,p.full_name
,e.name
FROM invoice i
LEFT JOIN inv_order i_o ON i_o.invID=i.invID
LEFT JOIN `item-service` item ON item.itemID = i_o.itemID
LEFT JOIN person p ON p.PID=i.personID
LEFT JOIN shift s ON s.shiftID = i.shiftID
LEFT JOIN employee e ON e.empID = s.empID
WHERE i.shiftID = 97

关于mysql - 带有左连接和并集的 SELECT,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/53366939/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com