一表中的字段如下:
FSeq whCode Re
1 wh1 r1a
1 wh1 rb
1 wh2 rb2
2 wa afd
2 ab cc
3 aaf adf
4 df 66
4 db adf
以FSeqNo为分组,现在进行以下要求取值
1.取每一组的第一记录,怎样写?
2.当每组记录有两条以上时就取第二条记录,若该组只有一条记录就取第一条记录,这语句怎样写?
请分别帮写出SQL2000和SQL2005的写法,谢谢
FSeq whCode Re
1 wh1 r1a
1 wh1 rb
1 wh2 rb2
2 wa afd
2 ab cc
3 aaf adf
4 df 66
4 db adf
以FSeqNo为分组,现在进行以下要求取值
1.取每一组的第一记录,怎样写?
2.当每组记录有两条以上时就取第二条记录,若该组只有一条记录就取第一条记录,这语句怎样写?
请分别帮写出SQL2000和SQL2005的写法,谢谢
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([FSeq] int,[whCode] varchar(3),[Re] varchar(3))
insert [TB]
select 1,'wh1','r1a' union all
select 1,'wh1','rb' union all
select 1,'wh2','rb2' union all
select 2,'wa','afd' union all
select 2,'ab','cc' union all
select 3,'aaf','adf' union all
select 4,'df','66' union all
select 4,'db','adf'
GO--> 查询结果
SELECT * FROM [TB]
--取每一组的第一记录
select a.* from [TB] a where [whCode] =
(select top 1 [whCode] from [TB] where [FSeq] = a.[FSeq]) and
[Re]=(select top 1 [Re] from [TB] where [FSeq] = a.[FSeq]) order by a.[FSeq]--> 删除表格
--DROP TABLE [TB]
select FSeq ,whCode ,Re from
(
select t.* , px = (select count(1) from tb where FSeq = t.FSeq and (whCode < t.whCode or (whCode = t.whCode and Re < t.Re))) + 1 from tb t
) m
where px = 1--sql 2000
select t.* from tb t where not exists(select 1 from tb where FSeq = t.FSeq and (whCode < t.whCode or (whCode = t.whCode and Re < t.Re)))--sql 2005
select FSeq ,whCode ,Re from
(
select t.* , px = row_number() over(partition by FSeq order by whCode, Re) from tb t
) m
where px = 1
select m.FSeq , m.whCode , m.Re from
(
select t.* , px = (select count(1) from tb where FSeq = t.FSeq and (whCode < t.whCode or (whCode = t.whCode and Re < t.Re))) + 1 from tb t
) m where px <= 2 and px = (select max(px) from
(
select t.* , px = (select count(1) from tb where FSeq = t.FSeq and (whCode < t.whCode or (whCode = t.whCode and Re < t.Re))) + 1 from tb t
) n where px <= 2 and n.FSeq = m.FSeq) --sql 2005
select m.FSeq , m.whCode , m.Re from
(
select t.* , px = row_number() over(partition by FSeq order by whCode, Re) from tb t
) m where px <= 2 and px = (select max(px) from
(
select t.* , px = row_number() over(partition by FSeq order by whCode, Re) from tb t
) n where px <= 2 and n.FSeq = m.FSeq)
只有编号为FSeq=1的能取到第二条记录,只有一条也可以显示,但是其他超过一条记录的显示的还是第一条记录,不是第二条。