--> 生成测试数据: #表a CREATE TABLE #表a(字段1 INT,字段2 VARCHAR(1),字段3 INT) INSERT INTO #表a SELECT 1,'a',12 UNION ALL SELECT 2,'a',12 UNION ALL SELECT 3,'a',12 UNION ALL SELECT 4,'b',13 UNION ALL SELECT 5,'b',13 UNION ALL SELECT 6,'b',13--SQL查询如下:UPDATE #表a SET 字段3=CASE WHEN 字段1=(SELECT TOP 1 字段1 FROM #表a AS A WHERE A.字段2=#表a.字段2 ORDER BY 字段1) THEN 字段3 ELSE 0 END;SELECT * FROM #表a;DROP TABLE #表a;/* 字段1 字段2 字段3 ----------- ---- ----------- 1 a 12 2 a 0 3 a 0 4 b 13 5 b 0 6 b 0(6 row(s) affected) */
update 表a set 字段3=(select max(字段3) from 表a A where A.字段2=表a.字段2)
借用小梁数据 CREATE TABLE #表a(字段1 INT,字段2 VARCHAR(1),字段3 INT) INSERT INTO #表a SELECT 1,'a',12 UNION ALL SELECT 2,'a',12 UNION ALL SELECT 3,'a',12 UNION ALL SELECT 4,'b',13 UNION ALL SELECT 5,'b',13 UNION ALL SELECT 6,'b',13update #表a set 字段3=(select max(字段3) from #表a A where A.字段2=#表a.字段2) select * from #表a /* 字段1 字段2 字段3 ----------- ---- ----------- 1 a 12 2 a 12 3 a 12 4 b 13 5 b 13 6 b 13(所影响的行数为 6 行) */
查询:select 字段1,字段2,字段3=case when exists(select 1 from a where 字段2=t.字段2 and 字段1<t.字段1) then 0 else 字段3 end from a t更新参考以上几位大牛的
SQL2005:INSERT INTO #a SELECT 1,'a',12 UNION ALL SELECT 2,'a',12 UNION ALL SELECT 3,'a',12 UNION ALL SELECT 4,'b',13 UNION ALL SELECT 5,'b',13 UNION ALL SELECT 6,'b',13UPDATE a SET a.字段3 = 0 FROM (SELECT ROW_NUMBER() OVER(partition BY 字段2 ORDER BY 字段2) as num,* FROM #a) a WHERE a.num != 1
update tb set 字段3=0 where 字段1 in ( select 字段1 from tb A where exists (select * from Tb B where A.字段2=B.字段2 and A.字段3>B.字段3) )
字段3=CASE WHEN 字段1=(SELECT TOP 1 字段1
FROM 表a AS A
WHERE A.字段2=表a.字段2
ORDER BY 字段1)
THEN 字段3
ELSE 0 END;
where 字段1
in
(
select 字段1
from tb A
where exists (select 1 from Tb B where A.字段2=B.字段2 and A.字段3>B.字段3)
)
update a set 字段3=case when not exists(select 1 from a where 字段3=t.字段3 and 字段2=t.字段2 and 字段1<t.字段1) then 字段3 else 0 end from a t
-- Author: liangCK 小梁
-- Date : 2009-05-16 10:47:28
---------------------------------
--> 生成测试数据: #表a
CREATE TABLE #表a(字段1 INT,字段2 VARCHAR(1),字段3 INT)
INSERT INTO #表a
SELECT 1,'a',12 UNION ALL
SELECT 2,'a',12 UNION ALL
SELECT 3,'a',12 UNION ALL
SELECT 4,'b',13 UNION ALL
SELECT 5,'b',13 UNION ALL
SELECT 6,'b',13--SQL查询如下:UPDATE #表a SET
字段3=CASE WHEN 字段1=(SELECT TOP 1 字段1
FROM #表a AS A
WHERE A.字段2=#表a.字段2
ORDER BY 字段1)
THEN 字段3
ELSE 0 END;SELECT * FROM #表a;DROP TABLE #表a;/*
字段1 字段2 字段3
----------- ---- -----------
1 a 12
2 a 0
3 a 0
4 b 13
5 b 0
6 b 0(6 row(s) affected)
*/
CREATE TABLE #表a(字段1 INT,字段2 VARCHAR(1),字段3 INT)
INSERT INTO #表a
SELECT 1,'a',12 UNION ALL
SELECT 2,'a',12 UNION ALL
SELECT 3,'a',12 UNION ALL
SELECT 4,'b',13 UNION ALL
SELECT 5,'b',13 UNION ALL
SELECT 6,'b',13update #表a set 字段3=(select max(字段3) from #表a A where A.字段2=#表a.字段2)
select * from #表a
/*
字段1 字段2 字段3
----------- ---- -----------
1 a 12
2 a 12
3 a 12
4 b 13
5 b 13
6 b 13(所影响的行数为 6 行)
*/
查询:select 字段1,字段2,字段3=case when exists(select 1 from a where 字段2=t.字段2 and 字段1<t.字段1) then 0 else 字段3 end
from a t更新参考以上几位大牛的
SELECT 1,'a',12 UNION ALL
SELECT 2,'a',12 UNION ALL
SELECT 3,'a',12 UNION ALL
SELECT 4,'b',13 UNION ALL
SELECT 5,'b',13 UNION ALL
SELECT 6,'b',13UPDATE a SET a.字段3 = 0 FROM (SELECT ROW_NUMBER() OVER(partition BY 字段2 ORDER BY 字段2) as num,* FROM #a) a
WHERE a.num != 1
set 字段3=0
where
字段1 in
(
select 字段1
from tb A
where exists (select * from Tb B where A.字段2=B.字段2 and A.字段3>B.字段3)
)