MySQL错误: The used SELECT statements have a different number of columns

Question

请查看下面的查询..

SELECT Client_Portfolio.*,
Client.Name,
Provider.Name,
"One" AS Income_Type,
One.`One_Gross_Fee` AS "Gross_Fee",
One.`One_V_Fee` AS "V_Fee",
One.`One_E_Fee` AS "E_Fee",
One.`One_I_Fee` AS "I_Fee",
One.`One_Tax_Provision` AS "Tax_Provision",
One.`One_Net_Income` AS "Net_Income",
"N/A" AS VAT,
One.`Updated_Date`
FROM Client_Portfolio
INNER JOIN Portfolio ON Portfolio.`idPortfolio` = Client_Portfolio.`idPortfolio`
INNER JOIN Client ON Client.idClient = Client_Portfolio.idClient
JOIN Provider ON Provider.idProvider = Portfolio.idProvider
INNER JOIN One ON One.idPortfolio = Portfolio.idPortfolio

UNION

SELECT Client_Portfolio.*,
Client.Name,
Provider.Name,
"Two" AS Income_Type,
Two.`Two_Gross_Fee` AS "Gross_Fee",
Two.`Two_V_Fee` AS "V_Fee",
Two.`Two_E_Fee` AS "E_Fee",
Two.`Two_I_Fee` AS "I_Fee",
Two.`Two_Tax_Provision` AS "Tax_Provision",
Two.`Two_Net_Income` AS "Net_Income",
Two.`Two_Vat` AS VAT,
Two.`Updated_Date`
FROM Client_Portfolio
INNER JOIN Portfolio ON Portfolio.`idPortfolio` = Client_Portfolio.`idPortfolio`
INNER JOIN Client ON Client.idClient = Client_Portfolio.idClient
JOIN Provider ON Provider.idProvider = Portfolio.idProvider
INNER JOIN Two ON Two.idPortfolio = Portfolio.idPortfolio

UNION

SELECT 
Three.`Date` AS 'Updated_Date',
Three.`Gross_Fee` AS 'Gross_Fee',
Three.`Three_E_Fee` AS 'E_Fee',
Three.`Three_Tax_Provision` AS 'Tax_Provision',
Three.`Three_Net_Income` AS 'Net_Income',
Three.`Three_Vat` AS 'VAT',
0.0 AS 'V_Fee',
0.0 AS 'I_Fee',
Client.Name AS 'Client_Name',
'Three' AS Income_Type,
Provider.Name AS 'Provider_Name'
FROM Three
INNER JOIN Three_Obs ON Three_Obs.`idThree_Obs` = Three.`idThree_Obs`
LEFT JOIN Client ON Three_Obs.`idClient` = Client.`idClient`
LEFT JOIN Provider ON Three_Obs.`idProvider` = Provider.`idProvider`

当我运行此查询时，收到错误所使用的 SELECT 语句具有不同的列数。这只是因为最后一个查询没有 SELECT Client_Portfolio.*。在 Client_Portfolio 内部有 2 列，idClient 和 idPortfolio，我没有理由将其加入到我的最后一个查询中，因为不需要它。但是，我尝试加入 Three_Obs.idClient = Client_Portfolio.idClient 只是为了看看它会如何，但我得到了完全无效的结果。

那么我该如何解决这个问题呢？我在最后一个查询中不需要 Client_Portfolio.*，但我需要该查询与其他 2 进行 UNION。

Answer 1

由于列数必须与联合语句匹配，因此您可以在最后一个语句中添加两个虚拟列并修复顺序，以便它与联合中的其他选择语句匹配。尝试一下:

SELECT 
Client_Portfolio.idClient,
Client_Portfolio.idPortfolio,
Client.Name AS "Client_Name",
Provider.Name AS "Provider_Name",
"One" AS "Income_Type",
One.`One_Gross_Fee` AS "Gross_Fee",
One.`One_V_Fee` AS "V_Fee",
One.`One_E_Fee` AS "E_Fee",
One.`One_I_Fee` AS "I_Fee",
One.`One_Tax_Provision` AS "Tax_Provision",
One.`One_Net_Income` AS "Net_Income",
"N/A" AS "VAT",
One.`Updated_Date`
FROM Client_Portfolio
INNER JOIN Portfolio ON Portfolio.`idPortfolio` = Client_Portfolio.`idPortfolio`
INNER JOIN Client ON Client.idClient = Client_Portfolio.idClient
JOIN Provider ON Provider.idProvider = Portfolio.idProvider
INNER JOIN One ON One.idPortfolio = Portfolio.idPortfolio

UNION

SELECT 
Client_Portfolio.idClient,
Client_Portfolio.idPortfolio,
Client.Name AS "Client_Name",
Provider.Name AS "Provider_Name",
"Two" AS "Income_Type",
Two.`Two_Gross_Fee` AS "Gross_Fee",
Two.`Two_V_Fee` AS "V_Fee",
Two.`Two_E_Fee` AS "E_Fee",
Two.`Two_I_Fee` AS "I_Fee",
Two.`Two_Tax_Provision` AS "Tax_Provision",
Two.`Two_Net_Income` AS "Net_Income",
Two.`Two_Vat` AS "VAT",
Two.`Updated_Date`
FROM Client_Portfolio
INNER JOIN Portfolio ON Portfolio.`idPortfolio` = Client_Portfolio.`idPortfolio`
INNER JOIN Client ON Client.idClient = Client_Portfolio.idClient
JOIN Provider ON Provider.idProvider = Portfolio.idProvider
INNER JOIN Two ON Two.idPortfolio = Portfolio.idPortfolio

UNION

SELECT 
null AS idClient,
null AS idPortfolio,
Client.Name AS "Client_Name",
Provider.Name AS "Provider_Name",
"Three" AS "Income_Type",
Three.`Gross_Fee` AS "Gross_Fee",
0.0 AS "V_Fee",
Three.`Three_E_Fee` AS "E_Fee",
0.0 AS "I_Fee",
Three.`Three_Tax_Provision` AS "Tax_Provision",
Three.`Three_Net_Income` AS "Net_Income",
Three.`Three_Vat` AS "VAT",
Three.`Date` AS "Updated_Date"
FROM Three
INNER JOIN Three_Obs ON Three_Obs.`idThree_Obs` = Three.`idThree_Obs`
LEFT JOIN Client ON Three_Obs.`idClient` = Client.`idClient`
LEFT JOIN Provider ON Three_Obs.`idProvider` = Provider.`idProvider`

尽管如此，看看您的查询似乎做了什么，我认为您可以重写它以至少对公共(public)表客户端和提供者使用联接，并稍微减少查询。