用存储过程union all还是少数据
原来写了一个存储过程:
create procedure Location_groupbyitem_location_sum_Combine (@pg1 char(20),@pg2 char(20))as
select a.* from (
(select id,itemcode ,itemname,chinesename,sum(boxqtyofsum) as boxqtyofsum,sum(qtysumofsum) as qtysumofsum,unit,[type],'' as productgroup,'' as barcode
from Location_groupbyitem_location_sum
where (productgroup like '%Burg%' or productgroup like '%Lighting Unit%') And qtysumOfSum>0
group by id,itemcode,itemname,chinesename,unit,[type]
having count(productgroup)>1 )
union
(select id,itemcode,itemname,chinesename,sum(boxqtyofsum) as boxqtyofsum,sum(qtysumofsum) as qtysumofsum,unit,[type], '' as productgroup, '' as barcode
from Location_groupbyitem_location_sum
where (productgroup like '%Burg%' or productgroup like '%Lighting Unit%') And qtysumOfSum>0 and type='part'
group by id,itemcode,itemname,chinesename,unit,[type]
having count(itemcode)<2)
union
(select id,itemcode,itemname,chinesename,sum(boxqtyofsum) as boxqtyofsum,sum(qtysumofsum) as qtysumofsum,unit,[type], '' as productgroup, '' as barcode
from Location_groupbyitem_location_sum
where (productgroup like '%Burg%' or productgroup like '%Lighting Unit%') And qtysumOfSum>0 and type='item'
group by id,itemcode,itemname,chinesename,unit,[type]
having count(itemcode)<2)
)a
发现第三个子select中的数据没有出来,用了union all之后还是没有……
但代入数据,用视图写出来,无论union还是union all数据都是对的.
想请教大家,这是为什么?难道存储过程写法是错的?那要怎样写呢?
谢谢! sql?存储过程?UNION?ALL
[解决办法]
SQL SERVER什么版本,是否没有打补丁
[解决办法]
另外,你的两个参数(@pg1 char(20),@pg2 char(20))没有用到,怀疑你贴的不是原始代码
你把参数修改为
(@pg1 VARchar(20),@pg2 VARchar(20))
试试
尽量贴原始代码,免得浪费我的时间