select
col1=case when not exists(select 1 from tb where col1=t.col1 and col3<t.col3) then '' else col1 end,
col2=case when not exists(select 1 from tb where col1=col1 and col2=col2 and col3<t.col3) then '' else col2 end,
col3
from
tb t
col1=case when not exists(select 1 from tb where col1=t.col1 and col3<t.col3) then '' else col1 end,
col2=case when not exists(select 1 from tb where col1=col1 and col2=col2 and col3<t.col3) then '' else col2 end,
col3
from
tb t
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col1] varchar(2),[col2] varchar(2),[col3] int)
insert [tb]
select 'AA','BB',10 union all
select 'AA','BB',20 union all
select 'AA','CC',30 union all
select 'AA','CC',40
---查询---
select
col1=case when not exists(select 1 from tb where col1=t.col1 and col3<t.col3) then col1 else '' end,
col2=case when not exists(select 1 from tb where col1=col1 and col2=t.col2 and col3<t.col3) then col2 else '' end,
col3
from
tb t---结果---
col1 col2 col3
---- ---- -----------
AA BB 10
20
CC 30
40(所影响的行数为 4 行)
go
create table [tb]([col1] varchar(10),[col2] varchar(10),[col3] int)
insert [tb] select 'AA','BB',10
union all select 'AA','BB',20
union all select 'AA','CC',30
union all select 'AA','CC',40select
case when not exists(select 1 from tb where col3<t.col3) then col1 else '' end as col1,
case when not exists(select 1 from tb where col2=t.col2 and col3<t.col3) then col2 else '' end as col2,
col3
from tb t
/*
col1 col2 col3
---------- ---------- -----------
AA BB 10
20
CC 30
40(4 行受影响)
*/
-- Author: happyflystone
-- Date : 2009-05-05 21:48:44
-- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
--
-------------------------------------------------------------------------- Test Data: T1
IF OBJECT_ID('T1') IS NOT NULL
DROP TABLE T1
Go
CREATE TABLE T1(col1 NVARCHAR(2),col2 NVARCHAR(2),col3 INT)
Go
INSERT INTO T1
SELECT 'AA','BB',10 UNION ALL
SELECT 'AA','BB',20 UNION ALL
SELECT 'AA','CC',30 UNION ALL
SELECT 'AA','CC',40
GO
--Start
;With t
as
(select rowid1 = row_number() over (partition by col1,col2 order by col1,col2),
rowid2 = row_number() over (partition by col1 order by col1,col2),*
from t1)
select case when rowid2 = 1 then col1 else '' end col1,
case when rowid1 = 1 then col2 else '' end col2,col3
from t--Result:
/*col1 col2 col3
---- ---- -----------
AA BB 10
20
CC 30
40*/
--End
go
create table [tb]([col1] varchar(10),[col2] varchar(10),[col3] int)
insert [tb] select 'AA','BB',10
union all select 'AA','BB',20
union all select 'AA','CC',30
union all select 'AA','CC',40select
case when col3=(select top 1 col3 from tb where col1=t.col1 order by col3) then col1 else '' end as col1,
case when col3=(select top 1 col3 from tb where col2=t.col2 order by col3) then col2 else '' end as col2,
col3
from tb t
/*
col1 col2 col3
---------- ---------- -----------
AA BB 10
20
CC 30
40(4 行受影响)
*/
SELECT 'AA','BB',10 UNION ALL
SELECT 'AA','BB',20 UNION ALL
SELECT 'Ab','CC',30 UNION ALL
SELECT 'AA','CC',40
union all select 'AA','BB',20
union all select 'AB','CC',30
union all select 'AA','CC',40
DROP TABLE TECREATE TABLE TE(
COL1 VARCHAR(20),
COL2 VARCHAR(20),
COL3 INT
)INSERT TE (COL1,COL2,COL3)
SELECT 'AA', 'BB', 10
UNION ALL
SELECT 'AA', 'BB', 20
UNION ALL
SELECT 'AA', 'CC' , 30
UNION ALL
SELECT 'AA' , 'CC' , 40 SELECT ROW_NUMBER() OVER(ORDER BY NEWID()) AS id , * INTO #TE FROM TE
--一步一个脚印
SELECT COL1 = CASE WHEN NOT EXISTS(SELECT 1 FROM #TE WHERE COL1 = T.COL1 AND T.ID > ID ) THEN COL1 ELSE '' END,
COL2 = CASE WHEN NOT EXISTS(SELECT 1 FROM #TE WHERE COL1 = T.COL1 AND COL2= T.COL2 AND T.ID >ID) THEN COL2 ELSE '' END,
COL3
FROM #TE T--结果
AA BB 20
10
CC 40
30
case when not exists(select 1 from t1 where t.col1 = col1 and col2+ltrim(col3) < t.col2+ltrim(t.col3)) then col1 else '' end as col1,
case when not exists(select 1 from t1 where t.col1 = col1 and col2=t.col2 and (col3)<(t.col3)) then col2 else '' end as col2,
col3
from t1 t
order by col1+col2
--Result:
/*col1 col2 col3
---- ---- -----------
AA BB 10
20
CC 40
AB CC 30*/
--End
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col1] varchar(2),[col2] varchar(2),[col3] int)
insert [tb]
SELECT 'AA','BB',10 UNION ALL
SELECT 'AA','BB',20 UNION ALL
SELECT 'AB','CC',30 UNION ALL
SELECT 'AA','DD',40
---查询---
select
col1=case when px1=1 then col1 else '' end,
col2=case when px2=1 then col2 else '' end,
col3
from
(select
px1=(select count(1)+1 from tb where col1=t.col1 and col2=t.col2 and col3<t.col3),
px2=(select count(1)+1 from tb where col2=t.col2 and col3<t.col3),
*
from tb t
) t
---结果---
col1 col2 col3
---- ---- -----------
AA BB 10
20
AB CC 30
AA DD 40(所影响的行数为 4 行)
-- Author: happyflystone
-- Date : 2009-05-05 21:48:44
-- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
--
-------------------------------------------------------------------------- Test Data: T1
IF OBJECT_ID('T1') IS NOT NULL
DROP TABLE T1
Go
CREATE TABLE T1(col1 NVARCHAR(2),col2 NVARCHAR(2),col3 INT)
Go
INSERT INTO T1
SELECT 'AA','BB',10 UNION ALL
SELECT 'AA','BB',20 UNION ALL
SELECT 'AB','CC',30 UNION ALL
SELECT 'AA','CC',40
GO
--Start
;With t
as
(select rowid1 = row_number() over (partition by col1,col2 order by col1,col2),
rowid2 = row_number() over (partition by col1 order by col1,col2),*
from t1)
select case when rowid2 = 1 then col1 else '' end col1,
case when rowid1 = 1 then col2 else '' end col2,col3
from tselect
case when not exists(select 1 from t1 where t.col1 = col1 and col2+ltrim(col3) < t.col2+ltrim(t.col3)) then col1 else '' end as col1,
case when not exists(select 1 from t1 where t.col1 = col1 and col2=t.col2 and (col3)<(t.col3)) then col2 else '' end as col2,
col3
from t1 t
order by col1+col2select
col1=case when px1=1 then col1 else '' end,
col2=case when px2=1 then col2 else '' end,
col3
from
(select
px1=(select count(1)+1 from t1 where col1=t.col1 and col2=t.col2 and col3<t.col3),
px2=(select count(1)+1 from t1 where col2=t.col2 and col3<t.col3),
*
from t1 t
) t--Result:
/*col1 col2 col3
---- ---- -----------
AA BB 10
20
CC 40
AB CC 30col1 col2 col3
---- ---- -----------
AA BB 10
20
CC 40
AB CC 30col1 col2 col3
---- ---- -----------
AA BB 10
20
AB CC 30
AA 40*/
--End