还要一个条件
有两个表A: B:
InID BARCODE InWareType Inwaredate Outid Barcode OutWareType OutWareDate
1 aa bbb 2006-01-01 1 aa cc 2006-01-02
2 aa ccc 2006-01-02 2 bb cc 2006-01-03
3 bb bbb 2006-01-03
4 cc bbb 2006-01-03
5 cc null 2006-01-03
6 bb null 2006-01-03
要得到如下视图
InID BARCODE InWareType Inwaredate
2 aa ccc 2006-01-02
5 cc null 2006-01-03
就是要以barcode分组汇总,如果表a的记录条数(intype 不为空)多于表b的记录条数,则取出表a中inid号最大的记录
有两个表A: B:
InID BARCODE InWareType Inwaredate Outid Barcode OutWareType OutWareDate
1 aa bbb 2006-01-01 1 aa cc 2006-01-02
2 aa ccc 2006-01-02 2 bb cc 2006-01-03
3 bb bbb 2006-01-03
4 cc bbb 2006-01-03
5 cc null 2006-01-03
6 bb null 2006-01-03
要得到如下视图
InID BARCODE InWareType Inwaredate
2 aa ccc 2006-01-02
5 cc null 2006-01-03
就是要以barcode分组汇总,如果表a的记录条数(intype 不为空)多于表b的记录条数,则取出表a中inid号最大的记录
from tableA aa
left join (select max(InID) as InID
from tableA
group by barcode) bb on aa.InID = bb.InID
left join (select a.barcode
from tableA a
left join tableB b on a.barcode = b.barcode
where a.InWareType is not null
group by a.barcode
having count(a.InID) > count(b.Outid)) cc on aa.barcode = cc.barcode
where bb.InID is not null and cc.barcode is not null