有两个表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号最大的记录
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号最大的记录
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中inwaretype 不为空 的记录条数多于表b的记录条数,则取出表a中inid号最大的记录
Insert into A
Select 1, 'aa', 'bbb', '2006-01-01' Union all
Select 2, 'aa', 'ccc', '2006-01-02' Union all
Select 3, 'bb', 'bbb', '2006-01-03' Union all
Select 4, 'cc', 'bbb', '2006-01-03' Union all
Select 5, 'cc', null, '2006-01-03' Union all
Select 6, 'bb', null, '2006-01-03' Create Table B(Outid int,Barcode varchar(10),OutWareType varchar(10),OutWareDate datetime)
Insert into b
Select 1 , 'aa', 'cc', '2006-01-02' Union all
Select 2 , 'bb', 'cc', '2006-01-03'
-----------------语句如----------------------------------------------------
select * From A where inid in
(select inid=max(a.inid) From A Inner join B on A.barcode=B.barcode group by a.barcode having count(*)>1)
----------------结果----------
InID BARCODE InWareType Inwaredate
2 aa ccc 2006-01-02
5 cc null 2006-01-03
这里为什么是count(*)>1 假如B表某个Barcode字段有重复咋办
Create Table A(InID Int,BARCODE varchar(10),InWareType varchar(10) Null,Inwaredate Datetime)
Insert into A
Select 1, 'aa', 'bbb', '2006-01-01' Union all
Select 2, 'aa', 'ccc', '2006-01-02' Union all
Select 3, 'bb', 'bbb', '2006-01-03' Union all
Select 4, 'cc', 'bbb', '2006-01-03' Union all
Select 5, 'cc', null, '2006-01-03' Union all
Select 6, 'bb', null, '2006-01-03' Create Table B(Outid int,Barcode varchar(10),OutWareType varchar(10),OutWareDate datetime)
Insert into b
Select 1 , 'aa', 'cc', '2006-01-02' Union all
Select 2 , 'bb', 'cc', '2006-01-03'
-----------------语句如----------------------------------------------------
select * from A where inid in
(select inid from
(select inid=max(inid),barcode,inwaretype=sum(case isnull(inwaretype,'0') when '0' then 0 else 1 end) From a group by barcode) a Left join
(select barcode,ou=count(*) From B group by barcode) b On A.barcode=B.barcode
where A.inwaretype>isnull(ou,0))
BEGIN
SELECT * FROM A WHERE InID=(SELECT MAX(InID) FROM A)
END
我的MS-SQL有问题,所以没运行过,你看看可以不?可以的话你就加个视图块就OK了