现有数据: 列1 列2
E0015 CC0001
E0015 CC0002
E0016 CC0001
E0016 CC0002
E0016 CC0003我希望把数据通过查询语句变成:
列1 列2
E0015 CC0001
CC0002
E0016 CC0001
CC0002
CC0003
E0015 CC0001
E0015 CC0002
E0016 CC0001
E0016 CC0002
E0016 CC0003我希望把数据通过查询语句变成:
列1 列2
E0015 CC0001
CC0002
E0016 CC0001
CC0002
CC0003
现有数据: 列1 列2
E0015 CC0001
E0015 CC0002
E0016 CC0001
E0016 CC0002
E0016 CC0003 我希望把数据通过查询语句变成:
列1 列2
E0015 CC0001
CC0002
E0016 CC0001
CC0002
CC0003
E0015 CC0001
CC0002
E0016 CC0001
CC0002
CC0003这样吧
列1,
case when exists(select 1 from tb where 列1=t.列1 and 列2<t.列2) then '' else 列1 end as 列2
from
tb t
列2=(case when id when 1 then 列2 else '' end)
from
(
select id=row_number() over(partition by 列1 order by 列2),列1,列2
from tb
) v
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([列1] varchar(5),[列2] varchar(6))
insert [tb]
select 'E0015','CC0001' union all
select 'E0015','CC0002' union all
select 'E0016','CC0001' union all
select 'E0016','CC0002' union all
select 'E0016','CC0003'
go select
case when exists(select 1 from tb where 列1=t.列1 and 列2<t.列2) then '' else 列1 end as 列1,
列2
from
tb t/**
列1 列2
----- ------
E0015 CC0001
CC0002
E0016 CC0001
CC0002
CC0003(所影响的行数为 5 行)
**/
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([列1] varchar(5),[列2] varchar(6))
insert [TB]
select 'E0015','CC0001' union all
select 'E0015','CC0002' union all
select 'E0016','CC0001' union all
select 'E0016','CC0002' union all
select 'E0016','CC0003'select 列1=case when 列2=(select min(列2) from TB where t.列1=列1) then 列1 else '' end,
列2 from [TB] t
/*
列1 列2
----- ------
E0015 CC0001
CC0002
E0016 CC0001
CC0002
CC0003(所影响的行数为 5 行)*/
drop table TB
select 列1=(case when id when 1 then 列1 else '' end),
列2
from
(
select id=row_number() over(partition by 列1 order by 列2),列1,列2
from tb
) v
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-11-08 23:19:51
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([列1] varchar(5),[列2] varchar(6))
insert [tb]
select 'E0015','CC0001' union all
select 'E0015','CC0002' union all
select 'E0016','CC0001' union all
select 'E0016','CC0002' union all
select 'E0016','CC0003'
--------------开始查询--------------------------
---2000
select
case when exists(select 1 from tb where 列1=t.列1 and 列2<t.列2) then '' else 列1 end as 列1,
列2
from
tb t---2005
select
case id when 1 then 列1 else '' end as 列1,列2
from
(
select
id=row_number() over(partition by 列1 order by 列2),*
from
tb
) t
----------------结果----------------------------
/* 列1 列2
----- ------
E0015 CC0001
CC0002
E0016 CC0001
CC0002
CC0003(5 行受影响)
*/