tableA
Jan
1
2
3tableB
Jan Loc date
1 A 6/1
1 B 6/1
1 C 6/1
2 A 6/2
2 A 6/3
3 A 6/1
Jan Loc date Jan2 Seq
1 A 6/1 1 1
1 B 6/1 1 2
1 B 6/1 1 3
2 A 6/2 2 1
3 A 6/1 3 1
想得到的记录就是在tableA有记录的tableB的date最小的记录,如果有两个以上的相同的最小记录的话,那么就给他们排序编号。
Jan
1
2
3tableB
Jan Loc date
1 A 6/1
1 B 6/1
1 C 6/1
2 A 6/2
2 A 6/3
3 A 6/1
Jan Loc date Jan2 Seq
1 A 6/1 1 1
1 B 6/1 1 2
1 B 6/1 1 3
2 A 6/2 2 1
3 A 6/1 3 1
想得到的记录就是在tableA有记录的tableB的date最小的记录,如果有两个以上的相同的最小记录的话,那么就给他们排序编号。
a.Jan,
b.Loc,
min(date) as date,
b.Jan as Jan2,
(select count(1) from tableB where Jan=b.Jan and Loc<=b.Loc) as Seq
from tableA a inner join tableB b
on a.Jan=b.Jan
group by a.Jan,b.Loc,b.Jan
order by a.Jan,Seq
a.Jan,
b.Loc,
min(date) as date,
b.Jan as Jan2,
(select count(1)+1 from tableB where Jan=b.Jan and Loc<b.Loc) as Seq
from tableA a inner join tableB b
on a.Jan=b.Jan
group by a.Jan,b.Loc,b.Jan
order by a.Jan,Seq
select
a.Jan,
b.Loc,
b.date,
b.Jan as Jan2,
(select count(1) from tableB where Jan=b.Jan and date=b.date and Loc<=b.Loc) as Seq
from tableA a inner join tableB b
on a.Jan=b.Jan
where not exists(select * from tableB where Jan=a.Jan and date<b.date)
order by a.Jan,Seq
Jan Loc date Jan2 Seq
1 A 6/1 1 1
1 B 6/1 1 2
1 B 6/1 1 3
2 A 6/2 2 3
3 A 6/1 3 3而不是我想要的:
Jan Loc date Jan2 Seq
1 A 6/1 1 1
1 B 6/1 1 2
1 B 6/1 1 3
2 A 6/2 2 1
3 A 6/1 3 1
要换成
(select count(1) from tableB where Jan=b.Jan and date=b.date and Loc<=b.Loc) as Seq
才准确吧
a.Jan,
b.Loc,
b.date,
b.Jan as Jan2,
(select count(1) from tableB where Jan=b.Jan and date=b.date and Loc<=b.Loc) as Seq
from tableA a inner join tableB b
on a.Jan=b.Jan
where not exists(select * from tableB where Jan=a.Jan and date<b.date)
order by a.Jan,Seq
这个你试一下,得不出结果吗?
(select count(1) from tableB where Jan=b.Jan and Loc<=b.Loc) as Seq
这个是把所有的Jan相同的排序,那么同一日期的就不一定是连号了
(select count(1) from tableB where Jan=b.Jan and date=b.date and Loc<=b.Loc) as Seq
这样感觉才是准确
还有如果存在Jan和Loc都相同的数据这个排序也是有问题的