这是什么问题啊 无语

[leaf_2008]

我写了一个存储过程

建了一个虚拟表
CREATE TABLE #ProductsToPrepare
(
[ProductId] int NOT NULL,
[Quantity] int,
[orderid] int
)





SET @SQL = 'INSERT INTO #ProductsToPrepare (ProductId,Quantity,orderid) select ss.productid,Sum(ss.Quantity)from ( '
SET @SQL = @SQL + ' SELECT e.ExchangeId as productid,e.quantity*hh.quantity as quantity,hh.orderid FROM huanaigu.productexchange e '
SET @SQL = @SQL + ' left join (select productid,sum(quantity),orderid as quantity from huanaigu.orderproduct where orderid IN (SELECT OrderId FROM huanaigu.Orders WHERE OrderStatusId=2) group by productid ) hh on e.productid=hh.productid '
SET @SQL = @SQL + 'where e.productid in(select productid from huanaigu.orderproduct where orderid in(SELECT OrderId FROM huanaigu.Orders WHERE OrderStatusId=2) group by productid) ) ss group by ss.productid'

exec sp_executesql @SQL




可是调试的时候老是报错误、
错误如下

服务器: 消息 8157，级别 16，状态 1，行 1
[Microsoft][ODBC SQL Server Driver][SQL Server]包含 UNION 运算符的查询表达式中的所有查询都必须在选择列表中包含同样数目的表达式。
获取调用堆栈失败！




大家帮忙看看是什么问题啊

[叶子]

意思是你union的字段不一样多

[zhangmeeno]

LS正解

[ljhcy99]

你的sql改为


INSERT INTO #ProductsToPrepare (ProductId,Quantity,orderid)
select ss.productid,Sum(ss.Quantity)
from (
SELECT e.ExchangeId as productid,e.quantity*hh.quantity as quantity,hh.orderid
FROM huanaigu.productexchange e left join
(select productid,sum(quantity),orderid as quantity
from huanaigu.orderproduct
where orderid IN (SELECT OrderId FROM huanaigu.Orders WHERE OrderStatusId=2)
group by productid
) hh on e.productid=hh.productid

where e.productid in
(select productid from
huanaigu.orderproduct
where orderid in(SELECT OrderId FROM huanaigu.Orders WHERE OrderStatusId=2)
)
) ss
group by ss.productid

[叶子]

就你的第一个语句来看

INSERT INTO #ProductsToPrepare 
(ProductId,Quantity,orderid) 
select ss.productid,Sum(ss.Quantity) from 

取出两个字段，往三个字段里插，前后不对应！

[lacie]

SQL语句中字段不一样多，一定是少写了，你在详细查查

[shangxb]

SET @SQL = 'INSERT INTO #ProductsToPrepare (ProductId,Quantity,orderid) select ss.productid,Sum(ss.Quantity)from ( '
SET @SQL = @SQL + ' SELECT e.ExchangeId as productid,e.quantity*hh.quantity as quantity,hh.orderid FROM huanaigu.productexchange e '
SET @SQL = @SQL + ' left join (select productid,sum(quantity),orderid as quantity from huanaigu.orderproduct where orderid IN (SELECT OrderId FROM huanaigu.Orders WHERE OrderStatusId=2) group by productid ) hh on e.productid=hh.productid '
SET @SQL = @SQL + 'where e.productid in(select productid from huanaigu.orderproduct where orderid in(SELECT OrderId FROM huanaigu.Orders WHERE OrderStatusId=2) group by productid) ) ss group by ss.productid'

字段数不匹配

[dd__dd]

[Quote=引用 4 楼 maco_wang 的回复:]
就你的第一个语句来看


INSERT INTO #ProductsToPrepare
(ProductId,Quantity,orderid)
select ss.productid,Sum(ss.Quantity) from




取出两个字段，往三个字段里插，前后不对应！
[/Quote]
恩

[kuuga_8086]

所对的数据列名或多或小了一些~

[benbirdar]

两个表的字段个数不一致！

[leaf_2008]

maco_wang

加了也不行还是一样的错误，我试过了

[leaf_2008]

加了也不行还是一样的错误，我试过了

[叶子]

把你的@SQL字符串select出来看一下，还是哪里字段没有匹配上。
你可以先把where条件去掉试一下
把@sql的语句拿出来，挨个子句测试，看看都好使不？

[Adechen]

查询的结果中与定义的结构不相同，多了或少了

[皓月明]

up