求一简单SQL语句

[jxdjxd1111]

数据库
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

[JiangHongTao]

select sl,je,max(case when left(hh,1)='8' then cp else '' end) as cp1,
             max(case when left(hh,1)='6' then cp else '' end) as cp2,bm,bs
from tb group by sl,je,bm,bs

[zefuzhang2008]

with t as(select sl,je,cp,bm,bs,row_number(PARTITION BY sl,je order by sl,je) as rowid
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

[pt1314917]

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

[dawugui]

select * from tb where len(cp) = 3

[dawugui]

select 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,bs

[dawugui]

create table tb(sl int, je int, cp varchar(10), bm int, bs varchar(10))
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') 
go

select 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,bs

drop 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 行）
*/

[bqb]

--如果cp的长度不固定，用下面这个

if object_id('tb') is not null
drop table tb
go

create 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
*/

[jxdjxd1111]

补充个条件
数据库
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

[pt1314917]

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

[pt1314917]

--应该再加个去重。。。。。

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

[zefuzhang2008]

with t as(select sl,je,cp,bm,bs,row_number(PARTITION BY sl,je order by sl,je) as rowid
from t)
select sl,je,cp as cp1,bm,bs,char(97+rowid-1)+char(97+rowid-1) as cp2
from t