group by 出现一个奇怪的问题
union三个数据集后group by时其中一个数据集的部分数据没有group，而如果我在每个数据集后加入一标志列ftype则结果正确。这是为什么呢？
代码如下：
select p.billno,p.fitemid ,sum(p.fqty) as fqty from (
select '1' as ftype,t.fbillno as billno,t1.fitemid,sum(t1.fqty) as fqty from icstockbill t left join icstockbillentry t1 on t1.finterid=t.finterid
inner join (select distinct left(fbillno,CHARINDEX('退',fbillno)-1) as fbillno from SEOutStock where fbillno like '%退%' and fdate>='2008-01-01' and fdate<='2013-12-31' and fcancellation=0 and fstatus>=1 group by fbillno) o on o.fbillno = t.fbillno
where t.fdate>='2008-01-01' and t.fdate<='2013-12-31' and ftrantype=21 and frob=1 and fcancellation=0 and fstatus>=1 group by t.fbillno,t1.fitemid
union
select '2' as ftype,left(tp.fbillno,CHARINDEX('退',tp.fbillno)-1) as billno,tp1.fitemid,sum(tp1.fqty) as fqty from icstockbill tp left join icstockbillentry tp1 on tp1.finterid=tp.finterid
where tp.fbillno like '%退%' and tp.fdate>='2008-01-01' and tp.fdate<='2013-12-31' and ftrantype=21 and fcancellation=0 and fstatus>=1 group by tp.fbillno,tp1.fitemid
union
select '3' as ftype,tt1.fsourcebillno as billno,fitemid,sum(fqty) as fqty from icstockbill tt left join icstockbillentry tt1 on tt1.finterid=tt.finterid
inner join (select distinct left(fbillno,CHARINDEX('退',fbillno)-1) as fbillno from SEOutStock where fbillno like '%退%' and fdate>='2008-01-01' and fdate<='2013-12-31' and fcancellation=0 and fstatus>=1 group by fbillno) o1 on o1.fbillno = tt1.fsourcebillno
where tt.fdate>='2008-01-01' and tt.fdate<='2013-12-31' and ftrantype=21 and frob=-1 and fcancellation=0 and fstatus>=1 group by tt1.fsourcebillno,tt1.fitemid
) p group by p.billno,p.fitemid
[解决办法]
或者写死标识列，比如：
select *
from (
select *,1 as orderid
from tb
union all
select *,2
from tb2
union all
select *,3
from tb3)x
order by orderid
select p.billno,p.fitemid ,sum(p.fqty) as fqty from (
select t.fbillno as billno,t1.fitemid,sum(t1.fqty) as fqty from icstockbill t left join icstockbillentry t1 on t1.finterid=t.finterid
inner join (select distinct left(fbillno,CHARINDEX('退',fbillno)-1) as fbillno from SEOutStock where fbillno like '%退%' and fdate>='2008-01-01' and fdate<='2013-12-31' and fcancellation=0 and fstatus>=1 group by fbillno) o on o.fbillno = t.fbillno
where t.fdate>='2008-01-01' and t.fdate<='2013-12-31' and ftrantype=21 and frob=1 and fcancellation=0 and fstatus>=1
group by t.fbillno,t1.fitemid
union all
select left(tp.fbillno,CHARINDEX('退',tp.fbillno)-1) as billno,tp1.fitemid,sum(tp1.fqty) as fqty from icstockbill tp left join icstockbillentry tp1 on tp1.finterid=tp.finterid
where tp.fbillno like '%退%' and tp.fdate>='2008-01-01' and tp.fdate<='2013-12-31' and ftrantype=21 and fcancellation=0 and fstatus>=1
group by tp.fbillno,tp1.fitemid
union all
select tt1.fsourcebillno as billno,fitemid,sum(fqty) as fqty from icstockbill tt left join icstockbillentry tt1 on tt1.finterid=tt.finterid
inner join (select distinct left(fbillno,CHARINDEX('退',fbillno)-1) as fbillno from SEOutStock where fbillno like '%退%' and fdate>='2008-01-01' and fdate<='2013-12-31' and fcancellation=0 and fstatus>=1 group by fbillno) o1 on o1.fbillno = tt1.fsourcebillno
where tt.fdate>='2008-01-01' and tt.fdate<='2013-12-31' and ftrantype=21 and frob=-1 and fcancellation=0 and fstatus>=1
group by tt1.fsourcebillno,tt1.fitemid
) p
group by p.billno,p.fitemid
