用存储过程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 SERVER什么版本，是否没有打补丁

[马不拉虎]

2005，不清楚有没有补丁

[昵称被占用了]

另外，你的两个参数(@pg1 char(20),@pg2 char(20))没有用到，怀疑你贴的不是原始代码 你把参数修改为 (@pg1 VARchar(20),@pg2 VARchar(20)) 试试 尽量贴原始代码，免得浪费我的时间

[马不拉虎]

查了些资料，char()和varchar()的不同仅在于是否可变长度。 在这个例子中，是这个原因导致的问题吗？如果是，详细过程是怎样的呢？小钱钱发完了，纯问题。

[马不拉虎]

非常抱歉，贴错了……这是我把参数代入时检查时用的，一不小心copy错了。 根据楼上提示，修改后已正确，非常感谢！ 原始代码是： 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 @pg1 or productgroup like @pg2) And qtysumOfSum>0 group by id,itemcode,itemname,chinesename,unit,[type] having count(productgroup)>1 ) union all (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 @pg1 or productgroup like @pg2) And qtysumOfSum>0 and type='part' group by id,itemcode,itemname,chinesename,unit,[type] having count(itemcode)<2) union all (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 @pg1 or productgroup like @pg2) And qtysumOfSum>0 and type='item' group by id,itemcode,itemname,chinesename,unit,[type] having count(itemcode)<2) )a order by id