先问个基础问题:
A表1000条记录
A left join B 之后会有多少条记录?都会=1000条吗?还是可能大于1000条?有两个表:一个进库表(假设1000条记录),一个出库表(很多记录),以进库表为基础,两个表根据工作单号和操作单位连接,意思就是最后连出来的表要和进库表一样多的记录数,也是1000条select *
from InputTable left join OutTable
on (InputTable.工作单号=OutTable.工作单号 and InputTable.操作单位=OutTable.操作单位)
怎么最后查出来的比1000条还多
A表1000条记录
A left join B 之后会有多少条记录?都会=1000条吗?还是可能大于1000条?有两个表:一个进库表(假设1000条记录),一个出库表(很多记录),以进库表为基础,两个表根据工作单号和操作单位连接,意思就是最后连出来的表要和进库表一样多的记录数,也是1000条select *
from InputTable left join OutTable
on (InputTable.工作单号=OutTable.工作单号 and InputTable.操作单位=OutTable.操作单位)
怎么最后查出来的比1000条还多
insert into @tablea
select 1,'a' union all
select 2,'a' union all
select 3,'a' union all
select 4,'a'select * from @tableadeclare @tableb table (id int,col varchar(1),col1 varchar(1))
insert into @tableb
select 1,'a','b' union all
select 2,'a','c' union all
select 3,'a','a' union all
select 4,'a','f' union all
select 5,'a','e'--4条
select * from @tableb--4条
select * from @tablea a left join @tableb b on a.id=b.id--20条
select * from @tablea a left join @tableb b on a.col=b.col
按工作单号和操作单位分组
然后再用InputTable 与其左联
declare @tablea table (A int,B varchar(7),C int)
insert into @tablea
select 1,'2009-06',1 union all
select 2,'2009-06',2 union all
select 2,'2009-07',3 union all
select 3,'2009-06',2declare @tableb table (A int,B varchar(7),C int)
insert into @tableb
select 1,'as',1 union all
select 2,'ad',2 union all
select 2,'gg',3 union all
select 3,'gg',2
--在两个表中A字段都不唯一
--6条数据
--select * from @tablea a left join @tableb b on a.A=b.A
--4条数据:
select t.A as ta,t.B as tb,t.C AS tc into #t from @tableb t
where b=(select max(b) from @tableb where A=t.A) order by aselect aa.*,bb.tb,bb.tc from @tablea aa left join #t bb on aa.A=bb.ta
drop table #t
/*
A B C tb tc
----------- ------- ----------- ------- -----------
1 2009-06 1 as 1
2 2009-06 2 gg 3
2 2009-07 3 gg 3
3 2009-06 2 gg 2
*/
declare @tablea table (A int,B varchar(7),C int)
insert into @tablea
select 1,'2009-06',1 union all
select 2,'2009-06',2 union all
select 2,'2009-07',3 union all
select 3,'2009-06',2declare @tableb table (A int,B varchar(7),C int)
insert into @tableb
select 1,'as',1 union all
select 2,'ad',2 union all
select 2,'gg',3 union all
select 3,'gg',2
--在两个表中A字段都不唯一
--6条数据
--select * from @tablea a left join @tableb b on a.A=b.A
--4条数据:
select t.A as ta,t.B as tb,t.C AS tc into #t from @tableb t
where b=(select max(b) from @tableb where A=t.A) order by aselect * from
(
select aa.*,bb.tb,bb.tc from @tablea aa left join #t bb on aa.A=bb.ta
)dd
where C=(select max(C) from (select aa.*,bb.tb,bb.tc from @tablea aa left join #t bb on aa.A=bb.ta)
ee where A=dd.A) order by Adrop table #t
在刚才的结果上再进行一次分组貌似也可以。