------------------------------------ -- Author: happyflystone -- Version:V1.001 -- Date:2008-09-11 15:31:57 -------------------------------------- Test Data: ta If object_id('ta') is not null Drop table ta Go Create table ta([Group] int,ID int,Name nvarchar(1)) Go Insert into ta select 1,1,'a' union all select 1,2,'b' union all select 1,3,'c' union all select 2,4,'d' union all select 2,5,'e' Go --Start select [group]=case when exists(select 1 from ta where [group] = a.[group] and id < a.id) then '' else ltrim(id) end, id, name from ta a--Result: /*group id name ------------ ----------- ---- 1 1 a 2 b 3 c 4 4 d 5 e(所影响的行数为 5 行)*/ --End
更正一下,有一个地方写错:------------------------------------ -- Author: happyflystone -- Version:V1.001 -- Date:2008-09-11 15:31:57 -------------------------------------- Test Data: ta If object_id('ta') is not null Drop table ta Go Create table ta([Group] int,ID int,Name nvarchar(1)) Go Insert into ta select 1,1,'a' union all select 1,2,'b' union all select 1,3,'c' union all select 2,4,'d' union all select 2,5,'e' Go --Start select [group]=case when exists(select 1 from ta where [group] = a.[group] and id < a.id) then '' else ltrim([group]) end, id, name from ta a--Result: /*group id name ------------ ----------- ---- 1 1 a 2 b 3 c 2 4 d 5 e(所影响的行数为 5 行)*/ --End
select B.[Group],A.ID,A.Name from tableA A left join (select [Group],min(id)id from tableA group by [Group])B on A.[Group]=B.[Group] and a.id=b.id --------------------- 1 1 a NULL 2 b NULL 3 c 2 4 d NULL 5 e
select B.[Group],A.ID,A.Name from tableA A left join (select [Group],min(id)id from tableA group by [Group])B on A.[Group]=B.[Group] and a.id=b.id
select case when (select count(1) from tableA c where a.group=c.group and c.id>a.id)=0 then a.group else '' end,a.id,a.name from tableA a
If object_id('tablea') is not null Drop table tablea Go Create table tablea([Group] int,ID int,Name nvarchar(1)) Go Insert into tablea select 1,1,'a' union all select 1,2,'b' union all select 1,3,'c' union all select 2,4,'d' union all select 2,5,'e' Goselect case when(select count(1) from tableA c where a.[group]=c.[group] and c.id<a.id)=0 then cast(a.[group] as varchar(10)) else '' end,a.id,a.name from tableA a
If object_id('tablea') is not null Drop table tablea Go Create table tablea([Group] int,ID int,Name nvarchar(1)) Go Insert into tablea select 1,1,'a' union all select 1,2,'b' union all select 1,3,'c' union all select 2,4,'d' union all select 2,5,'e' Goselect case when(select count(1) from tableA c where a.[group]=c.[group] and c.id<a.id)=0 then cast(a.[group] as varchar(10)) else '' end,a.id,a.name from tableA adrop table tablea --结果 (5 行受影响) id name ---------- ----------- ---- 1 1 a 2 b 3 c 2 4 d 5 e(5 行受影响)
If object_id('tablea') is not null Drop table tablea Go Create table tablea([Group] int,ID int,Name nvarchar(1)) Go Insert into tablea select 1,1,'a' union all select 1,2,'b' union all select 1,3,'c' union all select 2,4,'d' union all select 2,5,'e' Goselect case when(select count(1) from tableA c where a.[group]=c.[group] and c.id<a.id)=0 then cast(a.[group] as varchar(10)) else '' end as [group],a.id,a.name from tableA adrop table tablea--结果 (5 行受影响) group id name ---------- ----------- ---- 1 1 a 2 b 3 c 2 4 d 5 e(5 行受影响)
-- Author: happyflystone
-- Version:V1.001
-- Date:2008-09-11 15:31:57
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta([Group] int,ID int,Name nvarchar(1))
Go
Insert into ta
select 1,1,'a' union all
select 1,2,'b' union all
select 1,3,'c' union all
select 2,4,'d' union all
select 2,5,'e'
Go
--Start
select [group]=case when exists(select 1 from ta where [group] = a.[group] and id < a.id)
then ''
else ltrim(id) end,
id,
name
from ta a--Result:
/*group id name
------------ ----------- ----
1 1 a
2 b
3 c
4 4 d
5 e(所影响的行数为 5 行)*/
--End
-- Author: happyflystone
-- Version:V1.001
-- Date:2008-09-11 15:31:57
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta([Group] int,ID int,Name nvarchar(1))
Go
Insert into ta
select 1,1,'a' union all
select 1,2,'b' union all
select 1,3,'c' union all
select 2,4,'d' union all
select 2,5,'e'
Go
--Start
select [group]=case when exists(select 1 from ta where [group] = a.[group] and id < a.id)
then ''
else ltrim([group]) end,
id,
name
from ta a--Result:
/*group id name
------------ ----------- ----
1 1 a
2 b
3 c
2 4 d
5 e(所影响的行数为 5 行)*/
--End
left join (select [Group],min(id)id from tableA group by [Group])B
on A.[Group]=B.[Group] and a.id=b.id
---------------------
1 1 a
NULL 2 b
NULL 3 c
2 4 d
NULL 5 e
left join (select [Group],min(id)id from tableA group by [Group])B
on A.[Group]=B.[Group] and a.id=b.id
from tableA a
Drop table tablea
Go
Create table tablea([Group] int,ID int,Name nvarchar(1))
Go
Insert into tablea
select 1,1,'a' union all
select 1,2,'b' union all
select 1,3,'c' union all
select 2,4,'d' union all
select 2,5,'e'
Goselect case when(select count(1) from tableA c where a.[group]=c.[group]
and c.id<a.id)=0 then cast(a.[group] as varchar(10)) else '' end,a.id,a.name
from tableA a
Drop table tablea
Go
Create table tablea([Group] int,ID int,Name nvarchar(1))
Go
Insert into tablea
select 1,1,'a' union all
select 1,2,'b' union all
select 1,3,'c' union all
select 2,4,'d' union all
select 2,5,'e'
Goselect case when(select count(1) from tableA c where a.[group]=c.[group]
and c.id<a.id)=0 then cast(a.[group] as varchar(10)) else '' end,a.id,a.name
from tableA adrop table tablea
--结果 (5 行受影响)
id name
---------- ----------- ----
1 1 a
2 b
3 c
2 4 d
5 e(5 行受影响)
Drop table tablea
Go
Create table tablea([Group] int,ID int,Name nvarchar(1))
Go
Insert into tablea
select 1,1,'a' union all
select 1,2,'b' union all
select 1,3,'c' union all
select 2,4,'d' union all
select 2,5,'e'
Goselect case when(select count(1) from tableA c where a.[group]=c.[group]
and c.id<a.id)=0 then cast(a.[group] as varchar(10)) else '' end as [group],a.id,a.name
from tableA adrop table tablea--结果
(5 行受影响)
group id name
---------- ----------- ----
1 1 a
2 b
3 c
2 4 d
5 e(5 行受影响)