27,579
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-09-13 11:38:25
-- Verstion:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[message]
if object_id('[message]') is not null drop table [message]
go
create table [message]([neirong] varchar(2),[liuyanren] varchar(4))
insert [message]
select 'AA','张三' union all
select 'BB','张三' union all
select 'AA','李四' union all
select 'BB','李四' union all
select 'AA','王五' union all
select 'BB','王五'
--------------开始查询--------------------------
select
xuhao=(select count(distinct neirong)+1 from message where liuyanren=t.liuyanren and neirong<t.neirong),*
from
message t
----------------结果----------------------------
/* xuhao neirong liuyanren
----------- ------- ---------
1 AA 张三
2 BB 张三
1 AA 李四
2 BB 李四
1 AA 王五
2 BB 王五
(6 行受影响)
*/
---2000的
select
xuhao=(select count(distinct liuyanren)+1 from message where neirong=t.neirong),*
from
message t
SELECT row_number() over(PARTITION BY liuyanren order by getdate()) as xuhao
,neirong,liuyanren FROM message
SELECT row_number() over(PARTITION BY neirong order by getdate()) as xuhao
,neirong,liuyanren FROM message
xuhao=row_number()over(partition by liuyanren order by neirong)