-->> Author: 让你望见影子的墙(HEROWANG)生成测试数据 Date:2009-04-12 15:37:24 IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb Go CREATE TABLE tb(UID INT,title NVARCHAR(1),content NVARCHAR(2),author NVARCHAR(3),topicUID INT,ID INT) Go INSERT INTO tb SELECT 1,'a','aa','z',1,1 UNION ALL SELECT 2,'b','bb','zz',1,2 UNION ALL SELECT 3,'c','cc','zz',2,1 UNION ALL SELECT 4,'d','dd','zzz',3,1 UNION ALL SELECT 5,'e','ee','zzz',3,2 GOSELECT * FROM TB with wang as (select row=row_number() over (partition by TopicUID order by uid),* from tb) select * from tbUID title content author topicUID ID ----------- ----- ------- ------ ----------- ----------- 1 a aa z 1 1 2 b bb zz 1 2 3 c cc zz 2 1 4 d dd zzz 3 1 5 e ee zzz 3 2(5 行受影响)
with wang as (select row=row_number() over (partition by TopicUID order by uid),* from tb) select * from wangrow UID title content author topicUID ID -------------------- ----------- ----- ------- ------ ----------- ----------- 1 1 a aa z 1 1 2 2 b bb zz 1 2 1 3 c cc zz 2 1 1 4 d dd zzz 3 1 2 5 e ee zzz 3 2(5 行受影响)
---测试数据--- if object_id('[tb]') is not null drop table [tb] go create table [tb]([UID] int,[title] varchar(1),[content] varchar(2),[author] varchar(3),[topicUID] int) insert [tb] select 1,'a','aa','z',1 union all select 2,'b','bb','zz',1 union all select 3,'c','cc','zz',2 union all select 4,'d','dd','zzz',3 union all select 5,'e','ee','zzz',3
---查询--- select *, id=(select count(1)+1 from tb where topicUID=t.topicUID and UID<t.UID) from tb t---结果---UID title content author topicUID id ----------- ----- ------- ------ ----------- ----------- 1 a aa z 1 1 2 b bb zz 1 2 3 c cc zz 2 1 4 d dd zzz 3 1 5 e ee zzz 3 2(所影响的行数为 5 行)
select *,row=(select count(*)+1 from tb where topicuid=t.topicuid and uid<t.uid) from tb tUID title content author topicUID ID row ----------- ----- ------- ------ ----------- ----------- ----------- 1 a aa z 1 1 1 2 b bb zz 1 2 2 3 c cc zz 2 1 1 4 d dd zzz 3 1 1 5 e ee zzz 3 2 2(5 行受影响)
if object_id('tb') is not null drop table tb go create table tb([UID] varchar(10),[title] varchar(10),[content] varchar(10),[author] varchar(10),[topicUID] varchar(10)) insert tb select 1,'a','aa','z',1 insert tb select 2,'b','bb','zz',1 insert tb select 3,'c','cc','zz',2 insert tb select 4,'d','dd','zzz',3 insert tb select 5,'e','ee','zzz',3 goselect *,id=rank() over(partition by [topicUID] order by [UID]) from tbselect *,id=row_number() over(partition by [topicUID] order by [UID]) from tbselect *,id=(select count(1) from tb where [topicUID]=t.[topicUID] and [UID]<=t.[UID]) from tb t/* UID title content author topicUID id ---------- ---------- ---------- ---------- ---------- ----------- 1 a aa z 1 1 2 b bb zz 1 2 3 c cc zz 2 1 4 d dd zzz 3 1 5 e ee zzz 3 2 */
-->> Author: 让你望见影子的墙(HEROWANG)生成测试数据 Date:2009-04-12 15:37:24
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
Go
CREATE TABLE tb(UID INT,title NVARCHAR(1),content NVARCHAR(2),author NVARCHAR(3),topicUID INT,ID INT)
Go
INSERT INTO tb
SELECT 1,'a','aa','z',1,1 UNION ALL
SELECT 2,'b','bb','zz',1,2 UNION ALL
SELECT 3,'c','cc','zz',2,1 UNION ALL
SELECT 4,'d','dd','zzz',3,1 UNION ALL
SELECT 5,'e','ee','zzz',3,2
GOSELECT * FROM TB
with
wang as (select row=row_number() over (partition by TopicUID order by uid),* from tb)
select * from tbUID title content author topicUID ID
----------- ----- ------- ------ ----------- -----------
1 a aa z 1 1
2 b bb zz 1 2
3 c cc zz 2 1
4 d dd zzz 3 1
5 e ee zzz 3 2(5 行受影响)
wang as (select row=row_number() over (partition by TopicUID order by uid),* from tb)
select * from wangrow UID title content author topicUID ID
-------------------- ----------- ----- ------- ------ ----------- -----------
1 1 a aa z 1 1
2 2 b bb zz 1 2
1 3 c cc zz 2 1
1 4 d dd zzz 3 1
2 5 e ee zzz 3 2(5 行受影响)
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([UID] int,[title] varchar(1),[content] varchar(2),[author] varchar(3),[topicUID] int)
insert [tb]
select 1,'a','aa','z',1 union all
select 2,'b','bb','zz',1 union all
select 3,'c','cc','zz',2 union all
select 4,'d','dd','zzz',3 union all
select 5,'e','ee','zzz',3
---查询---
select
*,
id=(select count(1)+1 from tb where topicUID=t.topicUID and UID<t.UID)
from tb t---结果---UID title content author topicUID id
----------- ----- ------- ------ ----------- -----------
1 a aa z 1 1
2 b bb zz 1 2
3 c cc zz 2 1
4 d dd zzz 3 1
5 e ee zzz 3 2(所影响的行数为 5 行)
select *,row=(select count(*)+1 from tb where topicuid=t.topicuid and uid<t.uid)
from tb tUID title content author topicUID ID row
----------- ----- ------- ------ ----------- ----------- -----------
1 a aa z 1 1 1
2 b bb zz 1 2 2
3 c cc zz 2 1 1
4 d dd zzz 3 1 1
5 e ee zzz 3 2 2(5 行受影响)
drop table tb
go
create table tb([UID] varchar(10),[title] varchar(10),[content] varchar(10),[author] varchar(10),[topicUID] varchar(10))
insert tb select 1,'a','aa','z',1
insert tb select 2,'b','bb','zz',1
insert tb select 3,'c','cc','zz',2
insert tb select 4,'d','dd','zzz',3
insert tb select 5,'e','ee','zzz',3
goselect *,id=rank() over(partition by [topicUID] order by [UID]) from tbselect *,id=row_number() over(partition by [topicUID] order by [UID]) from tbselect *,id=(select count(1) from tb where [topicUID]=t.[topicUID] and [UID]<=t.[UID]) from tb t/*
UID title content author topicUID id
---------- ---------- ---------- ---------- ---------- -----------
1 a aa z 1 1
2 b bb zz 1 2
3 c cc zz 2 1
4 d dd zzz 3 1
5 e ee zzz 3 2
*/