declare @t table(F1 varchar(8),F2 varchar(8)) insert into @t values('01','a ') insert into @t values('01','aa ') insert into @t values('02','b ') insert into @t values('02','bb ') insert into @t values('02','bbb')select b.f1,a.f2 from @t a left join (select f1,min(f2) f2 from @t group by f1) b on a.f1=b.f1 and a.f2=b.f2
select isnull(b.f1,'') f1,a.f2 from @t a left join (select f1,min(f2) f2 from @t group by f1) b on a.f1=b.f1 and a.f2=b.f2/* f1 f2 -------- -------- 01 a aa 02 b bb bbb */
select F1=case when not exists(select 1 from @t where F1=t.F1 and F2<t.F2) then F1 else '' end, F2 from @t t order by F1,F2
--抄袭钻钻,呵呵 select (case when exists(select 1 from 表 where F1=t.F1 and F2<t.F2) then '' else F1 end) as ,F2 from 表 t order by F1,F2
--> --> (Roy)生成測試數據
set nocount on; declare @T table([F1] nvarchar(2),[F2] nvarchar(3)) Insert @T select N'01',N'a' union all select N'01',N'aa' union all select N'02',N'b' union all select N'02',N'bb' union all select N'02',N'bbb'
Select [F1]=case when [F2]=(select top 1 [F2] from @T where [F1]=t.[F1]) then [F1] else '' end, [F2] from @T t F1 F2 ---- ---- 01 a aa 02 b bb bbb2005 --> --> (Roy)生成測試數據
set nocount on; declare @T table([F1] nvarchar(2),[F2] nvarchar(3)) Insert @T select N'01',N'a' union all select N'01',N'aa' union all select N'02',N'b' union all select N'02',N'bb' union all select N'02',N'bbb'
Select [F1]=case when [F2]=min([F2])over(partition by [F1]) then [F1] else '' end, [F2] from @T t F1 F2 ---- ---- 01 a aa 02 b bb bbb
set nocount on; declare @T table([F1] nvarchar(2),[F2] nvarchar(3)) Insert @T select N'01',N'a' union all select N'01',N'aa' union all select N'02',N'b' union all select N'02',N'bb' union all select N'02',N'bbb'select case when rowid = 1 then f1 else '' end as f1, f2 from ( select rowid = row_number() over(partition by f1 order by f1,f2) ,* from @t) a/*f1 f2 ---- ---- 01 a aa 02 b bb bbb*/
set nocount on; declare @T table([F1] nvarchar(2),[F2] nvarchar(3)) Insert @T select N'01',N'aa' union all select N'01',N'aa' union all select N'02',N'bbb' union all select N'02',N'bbb' union all select N'02',N'bbb'union all select N'02',N'bbb'union all select N'02',N'bbb'select case when rowid = 1 then f1 else '' end as f1, f2 from ( select rowid = row_number() over(partition by f1 order by f1,f2) ,* from @t) a/*f1 f2 ---- ---- 01 aa aa 02 bbb bbb bbb bbb bbb */
if object_id ('T') is not null drop table T create table T ( F1 int, F2 varchar(3) ) insert into T select '01','a' union all select '01','aa' union all select '02','b' union all select '02','bb' union all select '02','bbb' GO select (case when exists (select 1 from T where F1=A.F1 AND F2<A.F2) THEN '' ELSE F1 END),f2 from T as a ------1 a 0 aa 2 b 0 bb 0 bbb
declare @t table(F1 varchar(8),F2 varchar(8)) insert into @t values('01','a ') insert into @t values('01','aa ') insert into @t values('02','b ') insert into @t values('02','bb ') insert into @t values('02','bbb')select a.F1,a.F2 from @t a,(select min(F2) t from @t group by F1) b where a.F2=b.t group by all a.F2,a.F1
happyflystone 要获得100分了。o(∩_∩)o...
看了很多朋友写的代码,觉得写得很不错,但是大多数依赖于F1和F2的大小。在此,小弟想些了一个通用的脚本,采用临时表和公用表达式来实现。代码如下:if object_id('tempdb.dbo.#temp') is not null drop table #temp create table #temp ( f1 varchar(5), f2 varchar(5) )declare @do_loop int ,@loop int ,@f1 varchar(5) select @do_loop=1 ,@loop=count(*) from dbo.t with (nolock)while @do_loop<=@loop begin ;with temp as ( select ROW_NUMBER() OVER(ORDER BY f1 asc) AS Row_Number ,f1 ,f2 from dbo.t )select top 1 @f1=f1 from temp where Row_Number=@do_loop;with temp1 as (select ROW_NUMBER() OVER(ORDER BY f1 asc) AS Row_Number ,f1 ,f2 from dbo.t )insert into #temp select case when exists (select top 1 1 from temp1 where f1=@f1 and Row_Number<@do_loop) then '' else f1 end as f1 ,f2 from temp1 where Row_Number=@do_loopset @do_loop=@do_loop+1 end select * from #temp 结果: f1 f2 01 a aa 02 bb b bbb
if object_id('t') is not null drop table t go create table t(f1 varchar(10),f2 varchar(10)) go insert into t select '01','a' union all select '01','aa' union all select '02','b' union all select '02','bb' union all select '02','bbb' goselect f1=case when exists(select 1 from t where f2<t1.f2 and f1=t1.f1) then '' else f1 end,f2 from t t1
来顶了,好贴就好好学习了
insert into @t values('01','a ')
insert into @t values('01','aa ')
insert into @t values('02','b ')
insert into @t values('02','bb ')
insert into @t values('02','bbb')select b.f1,a.f2 from @t a
left join
(select f1,min(f2) f2 from @t group by f1) b
on a.f1=b.f1 and a.f2=b.f2
left join
(select f1,min(f2) f2 from @t group by f1) b
on a.f1=b.f1 and a.f2=b.f2/*
f1 f2
-------- --------
01 a
aa
02 b
bb
bbb
*/
F1=case when not exists(select 1 from @t where F1=t.F1 and F2<t.F2) then F1
else '' end,
F2 from @t t order by F1,F2
--抄袭钻钻,呵呵
select (case when exists(select 1 from 表 where F1=t.F1 and F2<t.F2) then '' else F1 end) as ,F2 from 表 t order by F1,F2
set nocount on;
declare @T table([F1] nvarchar(2),[F2] nvarchar(3))
Insert @T
select N'01',N'a' union all
select N'01',N'aa' union all
select N'02',N'b' union all
select N'02',N'bb' union all
select N'02',N'bbb'
Select [F1]=case when [F2]=(select top 1 [F2] from @T where [F1]=t.[F1]) then [F1] else '' end,
[F2]
from @T t
F1 F2
---- ----
01 a
aa
02 b
bb
bbb2005
--> --> (Roy)生成測試數據
set nocount on;
declare @T table([F1] nvarchar(2),[F2] nvarchar(3))
Insert @T
select N'01',N'a' union all
select N'01',N'aa' union all
select N'02',N'b' union all
select N'02',N'bb' union all
select N'02',N'bbb'
Select [F1]=case when [F2]=min([F2])over(partition by [F1]) then [F1] else '' end,
[F2]
from @T t
F1 F2
---- ----
01 a
aa
02 b
bb
bbb
declare @T table([F1] nvarchar(2),[F2] nvarchar(3))
Insert @T
select N'01',N'a' union all
select N'01',N'aa' union all
select N'02',N'b' union all
select N'02',N'bb' union all
select N'02',N'bbb'select
case when rowid = 1 then f1 else '' end as f1,
f2
from (
select rowid = row_number() over(partition by f1 order by f1,f2) ,*
from @t) a/*f1 f2
---- ----
01 a
aa
02 b
bb
bbb*/
declare @T table([F1] nvarchar(2),[F2] nvarchar(3))
Insert @T
select N'01',N'aa' union all
select N'01',N'aa' union all
select N'02',N'bbb' union all
select N'02',N'bbb' union all
select N'02',N'bbb'union all
select N'02',N'bbb'union all
select N'02',N'bbb'select
case when rowid = 1 then f1 else '' end as f1,
f2
from (
select rowid = row_number() over(partition by f1 order by f1,f2) ,*
from @t) a/*f1 f2
---- ----
01 aa
aa
02 bbb
bbb
bbb
bbb
bbb
*/
if object_id ('T') is not null
drop table T
create table T
( F1 int,
F2 varchar(3)
)
insert into T select '01','a'
union all select '01','aa'
union all select '02','b'
union all select '02','bb'
union all select '02','bbb'
GO
select (case when exists
(select 1 from T where F1=A.F1 AND F2<A.F2) THEN '' ELSE F1 END),f2 from T as a
------1 a
0 aa
2 b
0 bb
0 bbb
insert into @t values('01','a ')
insert into @t values('01','aa ')
insert into @t values('02','b ')
insert into @t values('02','bb ')
insert into @t values('02','bbb')select a.F1,a.F2 from @t a,(select min(F2) t from @t group by F1) b where a.F2=b.t group by all a.F2,a.F1
要获得100分了。o(∩_∩)o...
drop table #temp
create table #temp
(
f1 varchar(5),
f2 varchar(5)
)declare @do_loop int
,@loop int
,@f1 varchar(5)
select @do_loop=1
,@loop=count(*) from dbo.t with (nolock)while @do_loop<=@loop
begin
;with temp as
(
select ROW_NUMBER() OVER(ORDER BY f1 asc) AS Row_Number
,f1
,f2
from dbo.t
)select top 1 @f1=f1 from temp
where Row_Number=@do_loop;with temp1 as
(select ROW_NUMBER() OVER(ORDER BY f1 asc) AS Row_Number
,f1
,f2
from dbo.t
)insert into #temp
select case when exists (select top 1 1 from temp1
where f1=@f1 and Row_Number<@do_loop)
then ''
else f1 end as f1
,f2
from temp1
where Row_Number=@do_loopset @do_loop=@do_loop+1
end
select * from #temp
结果:
f1 f2
01 a
aa
02 bb
b
bbb
if object_id('t') is not null
drop table t
go
create table t(f1 varchar(10),f2 varchar(10))
go
insert into t
select '01','a' union all
select '01','aa' union all
select '02','b' union all
select '02','bb' union all
select '02','bbb'
goselect f1=case when exists(select 1 from t where f2<t1.f2 and f1=t1.f1) then '' else f1 end,f2 from t t1