--测试数据:
drop table tb1
create table tb1
(id char(10),ph char(10),sb char(10))insert into tb1 values('1','123','987')
insert into tb1 values('1','124','987')
insert into tb1 values('1','125','987')
insert into tb1 values('1','123','986')
insert into tb1 values('1','124','986')
insert into tb1 values('1','125','986')
insert into tb1 values('2','123','987')
insert into tb1 values('2','124','987')
insert into tb1 values('3','333','666')
insert into tb1 values('3','333','777')--要求显示的结果如下:
1 123 987
124 986
125
2 123 987
124
3 333 666
777
drop table tb1
create table tb1
(id char(10),ph char(10),sb char(10))insert into tb1 values('1','123','987')
insert into tb1 values('1','124','987')
insert into tb1 values('1','125','987')
insert into tb1 values('1','123','986')
insert into tb1 values('1','124','986')
insert into tb1 values('1','125','986')
insert into tb1 values('2','123','987')
insert into tb1 values('2','124','987')
insert into tb1 values('3','333','666')
insert into tb1 values('3','333','777')--要求显示的结果如下:
1 123 987
124 986
125
2 123 987
124
3 333 666
777
解决方案 »
- SQL串“ print” 不不来,怪哉,急急急急。。。
- 怎么批量删除表中的某个列名
- 高难度问题求解!
- 我在win7系统下通过MyEclipse连接数据库,用用户sa的sql认证可以连接,但用windows nt认证则失败
- 请教存储过程中如何将数据写入到文件
- ((@)(@))ODBC-DBASE 5.0 数据库,单用户,在删除某行记录时,发生警告"由于表被占用,无法锁定数据库",查询代码在此之前并无其他连接(op
- MS SQL Server安装不起来!!!!(在线等待)
- 关于时间类型
- IBM的DB2提供这样的功能…………MS-SQL里有吗?嘿嘿——
- 为了使用sql server,我下在了opta2000.jar,程序编译通过,可是报错:db.executeQuery: No suitable driver,请问该怎么设置?
- 请教高手:查询添加记录最晚的表的名称
- out of memory & ora-03113:通信通道的文件结束
select id=case when a.rowid=1 then a.id else '' end),a.ph,b.sb
from
(select rowid=row_number()over(order by id,ph asc),* from (select distinct id,ph from tb)M)a
inner join
(select row_id=row_number()over(partition by id order by sb desc),* from (select distinct id,sb from tb) N)b
on a.id=b.id
--有待改进
row_number()是2005版本以后才有的 2000用不了
select
id = case when isnull(a.bh,b.bh) > 1 then '' else isnull(a.id,b.id) end,
ph = isnull(a.ph,''),
sb = isnull(b.sb,'')
from (
select a.id, a.ph, bh = (select count(distinct ph) from tb1 where id = a.id and ph <= a.ph)
from tb1 a
group by a.id, a.ph
) a full join (
select a.id, a.sb, bh = (select count(distinct sb) from tb1 where id = a.id and sb <= a.sb)
from tb1 a
group by a.id, a.sb
) b on a.id = b.id and a.bh = b.bh
order by isnull(a.id,b.id),isnull(a.bh,b.bh)/*
id ph sb
---------- ---------- ----------
1 123 986
124 987
125
2 123 987
124
3 333 666
777 (7 行受影响)
*/
分太少了,记得结贴哦