SQL 错误：使用 UNION、INTERSECT 或 EXCEPT 运算符组合的所有查询在其目标列表中必须具有相同数量的表达式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23345506/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-01 01:46:23 来源:igfitidea点击:
Error: All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists
提问by GHock
I am trying to create a stored procedure that will email out an HTML table that uses a query with Unions in it. But I keep getting:
我正在尝试创建一个存储过程，它将通过电子邮件发送一个 HTML 表，该表使用包含联合的查询。但我不断得到：
"All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists."
“使用 UNION、INTERSECT 或 EXCEPT 运算符组合的所有查询在其目标列表中必须具有相同数量的表达式。”
I went through and I believe all my selects for the Unions have the same number for columns. Any one have any ideas? I am using SQL 2012.
我经历了，我相信我所有的工会选择都有相同的列数。谁有想法？我正在使用 SQL 2012。
DECLARE @DailySales varchar(max)
With Daily_Sales_CTE as (
SELECT
TD = 'Base','',
TD = CONVERT(int,SUM(totalprice)/1000),'',
TD = CONVERT(int,SUM(totalcost)/1000),'',
TD = CONVERT(int,SUM(totalprice-totalcost)/1000),'',
TD = CONVERT(decimal (3,3),1-((SUM(totalcost))/(SUM(totalprice)))),'',
TD = CONVERT(int, SUM(totweight)/2000)
FROM ubs_saleslookup_cost A
WHERE DATEPART(MM,A.date) = DATEPART(MM, GETDATE()) AND DATEPART(YYYY,A.date) = DATEPART(YYYY, GETDATE()) and DATEPART(DD,A.date) = DATEPART(DD, GETDATE()) and
a.cust<>'1522292' and a.cust<>'1435230' and a.location<>'g-ds'
union
Select
TD = 'Direct','',
TD = CONVERT(int,SUM(totalprice)/1000),'',
TD = CONVERT(int,SUM(totalcost)/1000),'',
TD = CONVERT(int,SUM(totalprice-totalcost)/1000),'',
TD = CONVERT(decimal (3,3),1-((SUM(totalcost))/(SUM(totalprice)))),'',
TD = CONVERT(int, SUM(totweight)/2000)
FROM ubs_saleslookup_cost A
WHERE DATEPART(MM,A.date) = DATEPART(MM, GETDATE()) AND DATEPART(YYYY,A.date) = DATEPART(YYYY, GETDATE()) and DATEPART(DD,A.date) = DATEPART(DD, GETDATE()) and
a.cust<>'1522292' and a.cust<>'1435230' and a.location='g-ds'
union
SELECT
TD = 'Acero',
TD = CONVERT(int,SUM(totalprice)/b.xchgrate/1000),'',
TD = CONVERT(int,SUM(totalcost)/b.xchgrate/1000),'',
TD = CONVERT(int,SUM(totalprice-totalcost)/b.xchgrate/1000),'',
TD = CONVERT(decimal (3,3),1-((SUM(totalcost)/b.xchgrate)/(SUM(totalprice)/b.xchgrate))),'',
TD = CONVERT(int, SUM(totweight)/2000)
FROM acero.dbo.ubs_saleslookup_cost A
left join acero.dbo.acero_rate b on DATEPART(YYYY,A.date)=b.year1 and DATEPART(MM,A.date)=b.periodid
WHERE DATEPART(MM,A.date) = DATEPART(MM, GETDATE()) AND DATEPART(YYYY,A.date) = DATEPART(YYYY, GETDATE()) and DATEPART(DD,A.date) = DATEPART(DD, GETDATE()) and
a.cust<>'1512098'
group by b.xchgrate
union
SELECT
TD = 'Chile','',
TD = CONVERT(int,SUM(totalprice)/b.xchgrate/1000),'',
TD = CONVERT(int,SUM(totalcost)/b.xchgrate/1000),'',
TD = CONVERT(int,SUM(totalprice-totalcost)/b.xchgrate/1000),'',
TD = CONVERT(decimal (3,3),1-((SUM(totalcost)/b.xchgrate)/(SUM(totalprice)/b.xchgrate))),'',
TD = CONVERT(int, SUM(totweight)/2000)
FROM chile.dbo.ubs_saleslookup_cost A
left join chile.dbo.chile_rate b on DATEPART(YYYY,A.date)=b.year1 and DATEPART(MM,A.date)=b.periodid
WHERE DATEPART(MM,A.date) = DATEPART(MM, GETDATE()) AND DATEPART(YYYY,A.date) = DATEPART(YYYY, GETDATE()) and DATEPART(DD,A.date) = DATEPART(DD, GETDATE()) and
a.cust<>'1512098'
group by b.xchgrate
union
SELECT
TD = 'Total','',
TD = CONVERT(int,SUM(a.totalprice+b.totalprice+isnull(c.totalprice,0))/1000),'',
TD = CONVERT(int,SUM(a.totalcost+b.totalcost+isnull(c.totalprice,0))/1000),'',
TD = CONVERT(int,SUM(a.totalprice-a.totalcost+b.totalprice-b.totalcost+isnull(c.totalprice-c.totalcost,0))/1000),'',
TD = CONVERT(decimal (3,3),1-((SUM(a.totalcost+b.totalcost+isnull(c.totalcost,0)))/(SUM(a.totalprice+b.totalprice+isnull(c.totalprice,0))))),'',
TD = CONVERT(int, SUM(a.totweight+b.totweight+isnull(c.totweight,0)))
FROM (select 'CurMonth' as Month, sum(totalprice) as totalprice, sum(totalcost) as totalcost, sum(totweight)/2000 as totweight
from ubs_saleslookup_cost
WHERE DATEPART(MM,date) = DATEPART(MM, GETDATE()) AND DATEPART(YYYY,date) = DATEPART(YYYY, GETDATE()) and DATEPART(DD,date) = DATEPART(DD, GETDATE()) and
cust<>'1522292' and cust<>'1435230') a
left join (select 'CurMonth' as Month, sum(a.totalprice)/b.xchgrate as totalprice, sum(a.totalcost)/b.xchgrate as totalcost,
sum(a.totweight)/2000 as totweight
from acero.dbo.ubs_saleslookup_cost A
left join acero.dbo.acero_rate b on DATEPART(YYYY,A.date)=b.year1 and DATEPART(MM,A.date)=b.periodid
WHERE DATEPART(MM,A.date) = DATEPART(MM, GETDATE()) AND DATEPART(YYYY,A.date) = DATEPART(YYYY, GETDATE()) and DATEPART(DD,A.date) = DATEPART(DD, GETDATE()) and
a.cust<>'1512098' group by b.xchgrate) b on a.month=b.month
left join (select 'CurMonth' as Month, sum(a.totalprice)/b.xchgrate as totalprice, sum(a.totalcost)/b.xchgrate as totalcost,
sum(a.totweight)/2000 as totweight
from chile.dbo.ubs_saleslookup_cost A
left join chile.dbo.chile_rate b on DATEPART(YYYY,A.date)=b.year1 and DATEPART(MM,A.date)=b.periodid
WHERE DATEPART(MM,A.date) = DATEPART(MM, GETDATE()) AND DATEPART(YYYY,A.date) = DATEPART(YYYY, GETDATE()) and DATEPART(DD,A.date) = DATEPART(DD, GETDATE()) and
a.cust<>'1512098' group by b.xchgrate) c on a.month=c.month
)
Select @DailySales =
N'<style type="text/css">
#box-table
{
font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
font-size: 12px;
text-align: center;
border-collapse: collapse;
border-top: 7px solid #9baff1;
border-bottom: 7px solid #9baff1;
}
#box-table th
{
font-size: 13px;
font-weight: Bold;
background: #b9c9fe;
border-right: 2px solid #9baff1;
border-left: 2px solid #9baff1;
border-bottom: 2px solid #9baff1;
color: #039;
}
#box-table td
{
border-right: 1px solid #aabcfe;
border-left: 1px solid #aabcfe;
border-bottom: 1px solid #aabcfe;
color: #669;
}
tr:nth-child(odd) { background-color:#eee; }
tr:nth-child(even) { background-color:#fff; }
</style>'+
N'<H1><font color="Black">MTD Results</H1>'+
N'<table id="box-table">'+
N'<tr><font color = "Black">
<th> Type </th>
<th> Sales </th>
<th> Cost </th>
<th> Margin </th>
<th> Percentage </th>
<th> Tons </th>
</tr>'
+ CAST ( ( select * from Daily_Sales_CTE
FOR XML PATH ('tr')
) as varchar(max))
+ '</table>'
EXEC msdb.dbo.sp_send_dbmail
@recipients = '[email protected]',
@copy_recipients = '',
@subject = 'Daily Sales',
@body = @DailySales,
@body_format = 'html'
采纳答案by dean
There is one less column in the 'Acero' part (as Tony commented). Should be:
“Acero”部分少了一栏（正如托尼评论的那样）。应该：
SELECT
TD = 'Acero', '',
TD = CONVERT(int,SUM(totalprice)/b.xchgrate/1000),'',
However, when you fix this, you'll face another problem: all the columns in CTE must have a column name, and when you fix even that, you'll have the error that they are all called the same, and it's not allowed.
然而，当你解决这个问题时，你会面临另一个问题：CTE 中的所有列都必须有一个列名，而当你甚至解决这个问题时，你会得到它们都被称为相同的错误，这是不允许的.
