select
id,
name,
case px when 1 then content else '' end as content,
time,
from (select *,px=(select count(1)+1 from tb where content=t.content and [time]<t.[time]) from tb t) tt
id,
name,
case px when 1 then content else '' end as content,
time,
from (select *,px=(select count(1)+1 from tb where content=t.content and [time]<t.[time]) from tb t) tt
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(10),[content] varchar(16),[time] datetime)
insert [tb]
select 1,'敦煌酒家','咱北京自己的品牌','2009-03-25 16:19:57' union all
select 2,'茗香吧','咱北京自己的品牌','2009-03-25 16:19:58' union all
select 3,'真味羊杂割','咱北京自己的品牌','2009-03-25 16:19:59' union all
select 4,'源泉全','逛街经过梅园','2009-03-25 16:20:00' union all
select 6,'桃酥大王','咱北京自己的品牌','2009-03-25 16:20:01'
---查询---
select
id,
name,
case px when 1 then content else '' end as content,
[time]
from (select *,px=(select count(1)+1 from tb where content=t.content and [time]<t.[time]) from tb t) tt---结果---
id name content time
----------- ---------- ---------------- ------------------------------------------------------
1 敦煌酒家 咱北京自己的品牌 2009-03-25 16:19:57.000
2 茗香吧 2009-03-25 16:19:58.000
3 真味羊杂割 2009-03-25 16:19:59.000
4 源泉全 逛街经过梅园 2009-03-25 16:20:00.000
6 桃酥大王 2009-03-25 16:20:01.000(所影响的行数为 5 行)
select
id,
name,
case px when 1 then content else '' end as content,
[time]
from (select *,px=(select count(1)+1 from tb where content=t.content and [time]<t.[time] and id=t.id-1) from tb t) tt---结果---
id name content time
----------- ---------- ---------------- ------------------------------------------------------
1 敦煌酒家 咱北京自己的品牌 2009-03-25 16:19:57.000
2 茗香吧 2009-03-25 16:19:58.000
3 真味羊杂割 2009-03-25 16:19:59.000
4 源泉全 逛街经过梅园 2009-03-25 16:20:00.000
6 桃酥大王 咱北京自己的品牌 2009-03-25 16:20:01.000(所影响的行数为 5 行)
GO
CREATE TABLE TB([ID] INT,[NAME] NVARCHAR(5),[CONTENT] NVARCHAR(8),[TIME] DATETIME)
INSERT TB
SELECT 1,N'敦煌酒家',N'咱北京自己的品牌','2009-03-25 16:19:57' UNION ALL
SELECT 2,N'茗香吧',N'咱北京自己的品牌','2009-03-25 16:19:58' UNION ALL
SELECT 3,N'真味羊杂割',N'咱北京自己的品牌','2009-03-25 16:19:59' UNION ALL
SELECT 4,N'源泉全',N'逛街经过梅园','2009-03-25 16:20:00' UNION ALL
SELECT 6,N'桃酥大王',N'咱北京自己的品牌','2009-03-25 16:20:01'
GOSELECT
ID,NAME,
CONTENT=ISNULL(NULLIF(CONTENT,(SELECT CONTENT FROM TB WHERE ID=A.ID-1)),''),
TIME
FROM TB A
/*
ID NAME CONTENT TIME
----------- ----- -------- ------------------------------------------------------
1 敦煌酒家 咱北京自己的品牌 2009-03-25 16:19:57.000
2 茗香吧 2009-03-25 16:19:58.000
3 真味羊杂割 2009-03-25 16:19:59.000
4 源泉全 逛街经过梅园 2009-03-25 16:20:00.000
6 桃酥大王 咱北京自己的品牌 2009-03-25 16:20:01.000(所影响的行数为 5 行)
*/
ID,NAME,
CONTENT=ISNULL(NULLIF(CONTENT,(SELECT TOP 1 CONTENT FROM TB WHERE ID<A.ID ORDER BY ID DESC)),''),
TIME
FROM TB A
insert into @t select 1,'敦煌酒家 ','咱北京自己的品牌','2009-03-25 16:19:57'
insert into @t select 2,'茗香吧 ','咱北京自己的品牌','2009-03-25 16:19:58'
insert into @t select 3,'真味羊杂割','咱北京自己的品牌','2009-03-25 16:19:59'
insert into @t select 4,'源泉全 ','逛街经过梅园 ','2009-03-25 16:20:00'
insert into @t select 6,'桃酥大王 ','咱北京自己的品牌','2009-03-25 16:20:01'select
id,name,
content=(case when content=(select top 1 content from @t where id<t.id order by id desc) then '' else t.content end),
time
from
@t t/*
id name content time
----------- -------------------- ------------------------------ ------------------------------------------------------
1 敦煌酒家 咱北京自己的品牌 2009-03-25 16:19:57.000
2 茗香吧 2009-03-25 16:19:58.000
3 真味羊杂割 2009-03-25 16:19:59.000
4 源泉全 逛街经过梅园 2009-03-25 16:20:00.000
6 桃酥大王 咱北京自己的品牌 2009-03-25 16:20:01.000
*/