你也太快了那我只能写个2000的了 select a.snum,max(id) as id,'重复'as Demo from tb a,(select snum,count(*) a from tb group by snum having count(*)>1) b where a.snum=b.snum group by a.snum
改了一下,是这样:;with t as ( select *, ROW_NUMBER() over(partition by SNUM order by ID desc) as rownum, COUNT(*) over(partition by SNUM) as c from tb )
update t set DEMO = '重复' where c > 1 and rownum = 1
---------------------------------------------------------------- -- Author :DBA_Huangzj(發糞塗牆) -- Date :2013-11-25 13:06:02 -- Version: -- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) -- Dec 28 2012 20:23:12 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[snum] int,[demo] sql_variant) insert [tb] select 1,123,null union all select 2,123,null union all select 3,1234,null union all select 4,1234,null union all select 5,54231,null --------------开始查询-------------------------- SELECT * FROM TB go UPDATE TB SET demo=b.demo FROM TB INNER JOIN ( SELECT MAX(ID)id,SNUM,'重复' Demo FROM TB GROUP BY SNUM HAVING COUNT(id)>1)b ON TB.id=b.id AND TB.snum=b.snum go SELECT * FROM tb----------------结果---------------------------- /* id snum demo ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 123 NULL 2 123 NULL 3 1234 NULL 4 1234 NULL 5 54231 NULL id snum demo ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 123 NULL 2 123 重复 3 1234 NULL 4 1234 重复 5 54231 NULL */
借用上面的数据,这个是效果:if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[snum] int,[demo] sql_variant) insert [tb] select 1,123,null union all select 2,123,null union all select 3,1234,null union all select 4,1234,null union all select 5,54231,null ;with t as ( select *, ROW_NUMBER() over(partition by SNUM order by ID desc) as rownum, COUNT(*) over(partition by SNUM) as c from tb )
update t set DEMO = '重复' where c > 1 and rownum = 1 select * from tb /* 1 123 NULL 2 123 重复 3 1234 NULL 4 1234 重复 5 54231 NULL */
if OBJECT_ID('tb') is not null drop table tb go create table tb ( id int identity, snum int, demo varchar(20) ) go insert tb(snum,demo) select 24,null union all select 26,null union all select 32,null union all select 24,null union all select 26,null union all select 21,null union all select 32,null union all select 13,null union all select 15,null go update tb set demo='重复' where id in(select id from (select snum,MAX(id) as id from tb group by snum having COUNT(1)>=2) t)select * from tb /* id snum demo --------------------------- 1 24 NULL 2 26 NULL 3 32 NULL 4 24 重复 5 26 重复 6 21 NULL 7 32 重复 8 13 NULL 9 15 NULL */
;with ceb as( select ROW_NUMBER() over(partition by SNUM order by ID desc) as rn,ID,SNUM,DEMO from 表 ) select ID,SNUM,DEMO,'重复' as 重复 from ceb where rn=1
不好意思上面是盲写的,忽略了一点。。借用上面的数据。。THANKSdrop table #tb create table #tb([id] int,[snum] int,[demo] sql_variant) insert #tb select 1,123,null union all select 2,123,null union all select 3,1234,null union all select 4,1234,null union all select 5,54231,null;with ceb as( select ROW_NUMBER() over(partition by SNUM order by ID desc) as rn, COUNT(1) over(PARTITION by SNUM) as row, ID,SNUM,DEMO from #tb ) update ceb set DEMO='重复' where rn=1 and row>1
;with tt as ( select *, ROW_NUMBER()over(partition by snum order by id) 'num' from tb ), tbdemo as( select t1.* from tt t1,(select snum,max(num) 'num' from tt group by snum)t2 where t1.snum=t2.snum and t1.num=t2.num)update tbdemo set tbdemo.demo='重复' where tbdemo.num >1
;with tt as ( select *, ROW_NUMBER()over(partition by snum order by id) 'num' from tb )update tb set demo='重复' where id in( select MAX(id) 'id' from tt where num >1 group by snum)
select a.snum,max(id) as id,'重复'as Demo from tb a,(select snum,count(*) a from tb group by snum having count(*)>1) b
where a.snum=b.snum group by a.snum
改了一下,是这样:;with t
as
(
select *,
ROW_NUMBER() over(partition by SNUM order by ID desc) as rownum,
COUNT(*) over(partition by SNUM) as c
from tb
)
update t
set DEMO = '重复'
where c > 1 and rownum = 1
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-11-25 13:06:02
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[snum] int,[demo] sql_variant)
insert [tb]
select 1,123,null union all
select 2,123,null union all
select 3,1234,null union all
select 4,1234,null union all
select 5,54231,null
--------------开始查询--------------------------
SELECT * FROM TB
go
UPDATE TB
SET demo=b.demo
FROM TB INNER JOIN (
SELECT MAX(ID)id,SNUM,'重复' Demo
FROM TB
GROUP BY SNUM
HAVING COUNT(id)>1)b ON TB.id=b.id AND TB.snum=b.snum
go
SELECT * FROM tb----------------结果----------------------------
/*
id snum demo
----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 123 NULL
2 123 NULL
3 1234 NULL
4 1234 NULL
5 54231 NULL
id snum demo
----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 123 NULL
2 123 重复
3 1234 NULL
4 1234 重复
5 54231 NULL
*/
go
create table [tb]([id] int,[snum] int,[demo] sql_variant)
insert [tb]
select 1,123,null union all
select 2,123,null union all
select 3,1234,null union all
select 4,1234,null union all
select 5,54231,null
;with t
as
(
select *,
ROW_NUMBER() over(partition by SNUM order by ID desc) as rownum,
COUNT(*) over(partition by SNUM) as c
from tb
)
update t
set DEMO = '重复'
where c > 1 and rownum = 1
select *
from tb
/*
1 123 NULL
2 123 重复
3 1234 NULL
4 1234 重复
5 54231 NULL
*/
drop table tb
go
create table tb
(
id int identity,
snum int,
demo varchar(20)
)
go
insert tb(snum,demo)
select 24,null union all
select 26,null union all
select 32,null union all
select 24,null union all
select 26,null union all
select 21,null union all
select 32,null union all
select 13,null union all
select 15,null
go
update tb
set demo='重复'
where id in(select id from
(select snum,MAX(id) as id from tb group by snum having COUNT(1)>=2) t)select * from tb
/*
id snum demo
---------------------------
1 24 NULL
2 26 NULL
3 32 NULL
4 24 重复
5 26 重复
6 21 NULL
7 32 重复
8 13 NULL
9 15 NULL
*/
;with ceb as(
select ROW_NUMBER() over(partition by SNUM order by ID desc) as rn,ID,SNUM,DEMO from 表
)
select ID,SNUM,DEMO,'重复' as 重复 from ceb where rn=1
create table #tb([id] int,[snum] int,[demo] sql_variant)
insert #tb
select 1,123,null union all
select 2,123,null union all
select 3,1234,null union all
select 4,1234,null union all
select 5,54231,null;with ceb as(
select ROW_NUMBER() over(partition by SNUM order by ID desc) as rn,
COUNT(1) over(PARTITION by SNUM) as row,
ID,SNUM,DEMO from #tb
)
update ceb set DEMO='重复' where rn=1 and row>1
;with tt as
(
select *, ROW_NUMBER()over(partition by snum order by id) 'num'
from tb
), tbdemo as(
select t1.* from tt t1,(select snum,max(num) 'num' from tt
group by snum)t2
where t1.snum=t2.snum and t1.num=t2.num)update tbdemo
set tbdemo.demo='重复'
where tbdemo.num >1
(
select *, ROW_NUMBER()over(partition by snum order by id) 'num'
from tb
)update tb
set demo='重复'
where id in(
select MAX(id) 'id' from tt
where num >1
group by snum)