表a
id count role isTJ
----------------------------
1 32 AAA 0
2 23 AAA 0
4 89 BBB 0
5 21 BBB 0
6 43 BBB 0
7 56 BBB 0
8 77 BBB 0 把每个角色(role)中COUNT值最大的前三项的isTJ=1最后结果
表a
id count role isTJ
----------------------------
1 32 AAA 1
2 23 AAA 1
4 89 BBB 1
5 21 BBB 0
6 43 BBB 0
7 56 BBB 1
8 77 BBB 1
更新SQL怎么写
id count role isTJ
----------------------------
1 32 AAA 0
2 23 AAA 0
4 89 BBB 0
5 21 BBB 0
6 43 BBB 0
7 56 BBB 0
8 77 BBB 0 把每个角色(role)中COUNT值最大的前三项的isTJ=1最后结果
表a
id count role isTJ
----------------------------
1 32 AAA 1
2 23 AAA 1
4 89 BBB 1
5 21 BBB 0
6 43 BBB 0
7 56 BBB 1
8 77 BBB 1
更新SQL怎么写
解决方案 »
- 本地计算机上的SQLSERVERAGENT 服务启动后又停止了 一些服务自动停止,如果它们没有什么可以做的,例如"性能日志和警报"
- 设计表的时候 你们都用主外键约束了么?
- SQL Trigger啊
- select * from tz_jy where f_draw_no not in (select f_draw_no from tz_info )为何没数据?
- 该存储过程执行一般情况下需要1分30多秒,为什么?谢谢!
- 分不多,求一句SQL语句 多表交叉的,谢谢了
- 行转列的问题。
- Oracle、SQL Server选择 --请求
- SQL Server 和Oracle提供的XML支持集能通过统一查询获得么?
- 急急急急。解答了就给分
- 关于搜索功能
- 帮忙检查下存储过程 先谢谢了
FROM A T WHERE [count]
IN(SELECT TOP 3 [count] FROM A WHERE [ROLE]=T.[ROLE] ORDER BY [COUNT] DESC)
-- Author :SQL77(只为思齐老)
-- Date :2010-01-26 15:52:49
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([id] int,[count] int,[role] varchar(3),[isTJ] int)
insert #TB
select 1,32,'AAA',0 union all
select 2,23,'AAA',0 union all
select 4,89,'BBB',0 union all
select 5,21,'BBB',0 union all
select 6,43,'BBB',0 union all
select 7,56,'BBB',0 union all
select 8,77,'BBB',0
--------------开始查询--------------------------
UPDATE T SET ISTJ=1
FROM #TB T WHERE [count]
IN(SELECT TOP 3 [count] FROM #TB WHERE [ROLE]=T.[ROLE] ORDER BY [COUNT] DESC)select * from #TB
----------------结果----------------------------
/* (所影响的行数为 7 行)
(所影响的行数为 5 行)id count role isTJ
----------- ----------- ---- -----------
1 32 AAA 1
2 23 AAA 1
4 89 BBB 1
5 21 BBB 0
6 43 BBB 0
7 56 BBB 1
8 77 BBB 1(所影响的行数为 7 行)
*/
FROM A T WHERE [count]
IN(SELECT TOP 3 [count] FROM A WHERE [ROLE]=T.[ROLE] ORDER BY [COUNT] DESC)up
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2010-01-26 15:51:33
-- 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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([id] int,[count] int,[role] varchar(3),[isTJ] int)
insert [a]
select 1,32,'AAA',0 union all
select 2,23,'AAA',0 union all
select 4,89,'BBB',0 union all
select 5,21,'BBB',0 union all
select 6,43,'BBB',0 union all
select 7,56,'BBB',0 union all
select 8,77,'BBB',0
--------------开始查询--------------------------
update
t
set
isTJ=1
from
a t
where
[count] IN(SELECT TOP 3 [count] FROM a WHERE [role]=T.[role] ORDER BY [count] DESC)
select * from a
----------------结果----------------------------
/* id count role isTJ
----------- ----------- ---- -----------
1 32 AAA 1
2 23 AAA 1
4 89 BBB 1
5 21 BBB 0
6 43 BBB 0
7 56 BBB 1
8 77 BBB 1(7 行受影响)
*/