数据库
sl je cp bm bs
2 200 aes 5001 g01
2 200 aa 5001 g01
5 500 aos 5002 g02
5 500 bb 5002 g02
3 350 aes 5001 g03
3 350 bb 5001 g03
8 800 ams 5003 g04
8 800 cc 5003 g04
求
sl je cp1 cp2 bm bs
2 200 aes aa 5001 g01
5 500 aos bb 5002 g02
3 350 aes bb 5001 g03
8 800 ams cc 5003 g04
sl je cp bm bs
2 200 aes 5001 g01
2 200 aa 5001 g01
5 500 aos 5002 g02
5 500 bb 5002 g02
3 350 aes 5001 g03
3 350 bb 5001 g03
8 800 ams 5003 g04
8 800 cc 5003 g04
求
sl je cp1 cp2 bm bs
2 200 aes aa 5001 g01
5 500 aos bb 5002 g02
3 350 aes bb 5001 g03
8 800 ams cc 5003 g04
数据库
sl je cp bh bm bs
2 200 aes 8001 5001 g01
2 200 aa 6001 5001 g01
5 500 aos 8002 5002 g02
5 500 bb 6002 5002 g02
3 350 aes 8001 5001 g03
3 350 bb 6002 5001 g03
8 800 ams 8004 5003 g04
8 800 cc 6003 5003 g04
求
sl je cp1 cp2 bm bs
2 200 aes aa 5001 g01
5 500 aos bb 5002 g02
3 350 aes bb 5001 g03
8 800 ams cc 5003 g04
select m.sl,m.je,m.cp cp1,n.cp cp2,m.bm m.bs from
(select sl,je,cp,bm,bs from 表名 a where not exists(select 1 from 表名 where
sl=a.sl and je=a.je and bm=a.bm and bs=a.bs and bh>a.bh))m,
(select sl,je,cp,bm,bs from 表名 a where not exists(select 1 from 表名 where
sl=a.sl and je=a.je and bm=a.bm and bs=a.bs and bh<a.bh))n
where m.sl=n.sl and m.je=n.je and m.bm=n.bm and m.bs=n.bs
--应该再加个去重。。select distinct m.sl,m.je,m.cp cp1,n.cp cp2,m.bm m.bs from
(select sl,je,cp,bm,bs from 表名 a where not exists(select 1 from 表名 where
sl=a.sl and je=a.je and bm=a.bm and bs=a.bs and bh>a.bh))m,
(select sl,je,cp,bm,bs from 表名 a where not exists(select 1 from 表名 where
sl=a.sl and je=a.je and bm=a.bm and bs=a.bs and bh<a.bh))n
where m.sl=n.sl and m.je=n.je and m.bm=n.bm and m.bs=n.bs
from t)
select sl,je,cp as cp1,bm,bs,char(97+rowid-1)+char(97+rowid-1) as cp2
from t
max(case when left(hh,1)='6' then cp else '' end) as cp2,bm,bs
from tb group by sl,je,bm,bs
from t),
t1 as(
select sl,je,cp as cp1,bm,bs
from t
where rowid=1),
t2 as(select sl,je,cp1,bm,bs,row_number() over(order by sl)
from t1)
select sl,je,cp1,bm,bs,char(97+rowid-1)+char(97+rowid-1) as cp2
from t2
create table os(sl varchar(10),je varchar(10),cp varchar(10),bh int,bm varchar(10),bs varchar(10))
insert into os select '2','200','aes',8001,'5001','g01'
insert into os select '2','200','aa',6001,'5001','g01'
insert into os select '5','500','aos',8002,'5002','g02'
insert into os select '5','500','bb',6002,'5002','g02'
insert into os select '3','350','aes',8001,'5001','g03'
insert into os select '3','350','bb',6002,'5001','g03'
insert into os select '8','800','ams',8004,'5003','g04'
insert into os select '8','800','cc',6003,'5003','g04'select m.sl,m.je,m.cp cp1,n.cp cp2,m.bm,m.bs from os m,os n
where m.sl=n.sl and m.je=n.je and m.bm=n.bm and m.bs=n.bs and m.bh>n.bh
max(case len(cp) when 3 then cp else '' end) cp1,
max(case len(cp) when 2 then cp else '' end) cp2,
bm,bs
from tb
group by sl,je,bm,bs
insert into tb values(2, 200, 'aes', 5001 , 'g01')
insert into tb values(2, 200, 'aa' , 5001 , 'g01')
insert into tb values(5, 500, 'aos', 5002 , 'g02')
insert into tb values(5, 500, 'bb' , 5002 , 'g02')
insert into tb values(3, 350, 'aes', 5001 , 'g03')
insert into tb values(3, 350, 'bb' , 5001 , 'g03')
insert into tb values(8, 800, 'ams', 5003 , 'g04')
insert into tb values(8, 800, 'cc' , 5003 , 'g04')
goselect sl,je,
max(case len(cp) when 3 then cp else '' end) cp1,
max(case len(cp) when 2 then cp else '' end) cp2,
bm,bs
from tb
group by sl,je,bm,bsdrop table tb/*
sl je cp1 cp2 bm bs
----------- ----------- ---------- ---------- ----------- ----------
2 200 aes aa 5001 g01
3 350 aes bb 5001 g03
5 500 aos bb 5002 g02
8 800 ams cc 5003 g04(所影响的行数为 4 行)
*/
drop table tb
gocreate table tb(sl int,je int,cp varchar(10),bh varchar(10),bm varchar(10),bs varchar(20))
insert into tb
select 2,200,'aes','8001','5001','g01' union all
select 2,200,'aa','6001','5001','g01' union all
select 5,500,'aos','8002','5002','g02' union all
select 5,500,'bb','6002','5002','g02' union all
select 3,350,'aes','8001','5001','g03' union all
select 3,350,'bb','6002','5001','g03' union all
select 8,800,'ams','8004','5003','g04' union all
select 8,800,'cc','6003','5003','g04'
select sl,je,
max(case when cid=1 then cp else ''end) as cp1,
max(case when cid=2 then cp else ''end) as cp2,
bm,bs
from (select *,(select count(cp) from tb where sl=a.sl and je=a.je and bm=a.bm and bs=a.bs and cp<=a.cp) as cid from tb a) tmp
group by sl,je,bm,bs
/*
sl je cp1 cp2 bm bs
------------------------------------------------
2 200 aa aes 5001 g01
3 350 aes bb 5001 g03
5 500 aos bb 5002 g02
8 800 ams cc 5003 g04
*/