create table #T --#T是一个临时表,创建存储在系统数据库的tempdb数据库中,用了以后重启数据库服务就会释放.
(
[id] int,
[name] varchar(1),
[memo] varchar(2)
)
go
select * from #T
go
insert into #T
select 1,N'A',N'A1' union all
select 2,N'A',N'A2' union all
select 3,N'A',N'A3' union all
select 4,N'B',N'B1' union all
select 5,N'B',N'B2'
go
select * from #T
表T应该是:
[id] [name] [memo]
1 A A1
2 A A2
3 A A3
4 B B1
5 B B2我想要得到这个结果:[id] [name] [memo]
1 A A1
2 0 A2
3 0 A3
4 B B1
5 0 B2
就是name重复的,就显示0.在线等,谢谢
(
[id] int,
[name] varchar(1),
[memo] varchar(2)
)
go
select * from #T
go
insert into #T
select 1,N'A',N'A1' union all
select 2,N'A',N'A2' union all
select 3,N'A',N'A3' union all
select 4,N'B',N'B1' union all
select 5,N'B',N'B2'
go
--select * from #T
SELECT
ID,
CASE WHEN NOT EXISTS(SELECT 1 FROM #T WHERE NAME=T.NAME AND ID<T.ID) THEN NAME ELSE '0' END AS NAME,
MEMO
FROM #T TID NAME MEMO
----------- ---- ----
1 A A1
2 0 A2
3 0 A3
4 B B1
5 0 B2(所影响的行数为 5 行)
再问下,如果是两个表关联起来的呢:
比如
T1表ID NAME
1 A
2 BT2表
NAME MEMO
A A1
A A2
A A3
B B1
B B2T1和T2关联起来得到:
ID NAME MEMO
1 A A1
0 0 A2
0 0 A3
2 B B1
0 0 B1谢谢
先把两表连接起来插入一个IDENTITY的临时表(或者排序列),最后同上