select *, aid=(select count(1)+1 from A where tid=t.tid and pid<t.pid) from A t
DECLARE @TB TABLE([tid] INT, [pid] INT) INSERT @TB SELECT 1, 1 UNION ALL SELECT 1, 5 UNION ALL SELECT 2, 3 UNION ALL SELECT 3, 6 UNION ALL SELECT 2, 0SELECT * FROM ( SELECT *,aid=(SELECT COUNT(*) FROM @TB WHERE tid=TA.tid AND pid<=TA.pid) FROM @TB AS TA ) TB ORDER BY tid,aid /* tid pid aid ----------- ----------- ----------- 1 1 1 1 5 2 2 0 1 2 3 2 3 6 1(5 row(s) affected) */
---测试数据--- if object_id('[A]') is not null drop table [A] go create table [A]([tid] int,[pid] int) insert [A] select 1,1 union all select 1,5 union all select 2,3 union all select 3,6 union all select 2,0
---查询--- select *, aid=(select count(1)+1 from A where tid=t.tid and pid<t.pid) from A t order by tid, aid---结果--- tid pid aid ----------- ----------- ----------- 1 1 1 1 5 2 2 0 1 2 3 2 3 6 1(所影响的行数为 5 行)
declare @t table(tid int ,pid int) insert into @t select 1,1 union all select 1,5 union all select 2,3 union all select 3,6 union all select 2, 0 select *,aid=row_number() over (partition by tid order by tid) from @t/*tid pid aid ----------- ----------- -------------------- 1 1 1 1 5 2 2 3 1 2 0 2 3 6 1(5 行受影响)*/
-- ========================================= -- -----------t_mac 小编------------- ---希望有天成为大虾---- -- =========================================IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb GO CREATE TABLE tb(tid INT, [pid] INT) go insert into tb SELECT 1, 1 UNION ALL SELECT 1, 5 UNION ALL SELECT 2, 3 UNION ALL SELECT 3, 6 UNION ALL SELECT 2, 0 go SELECT *,aid=(SELECT COUNT(*) FROM tb WHERE tid=TA.tid AND pid<=TA.pid) FROM tb AS TA ORDER BY tid,aid go /*------------ 1 1 1 1 5 2 2 0 1 2 3 2 3 6 1 -------*/
declare @T1 table([tid] int, [pid] int) insert @T1 select 1, 1 union all select 1, 5 union all select 2, 3 union all select 3, 6 union all select 2, 0--sql 2005 select [tid],[pid],aid=rank() over(partition by [tid] order by [pid]) from @T1tid pid aid ----------- ----------- -------------------- 1 1 1 1 5 2 2 0 1 2 3 2 3 6 1(5 行受影响)
declare @T1 table([tid] int, [pid] int) insert @T1 select 1, 1 union all select 1, 5 union all select 2, 3 union all select 3, 6 union all select 2, 0select [tid],[pid],aid=(select count(1) from @T1 where tid=a.tid and pid<a.pid)+1 from @T1 a order by [tid],[pid]tid pid aid ----------- ----------- -------------------- 1 1 1 1 5 2 2 0 1 2 3 2 3 6 1(5 行受影响)
---------------------------------------------------------------- -- Author :fredrickhu(小F 向高手学习) -- Date :2009-06-24 12:39:55 ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] create table [tb]([tid] int,[pid] int) insert [tb] select 1,1 union all select 1,5 union all select 2,3 union all select 3,6 union all select 2,0 ------开始查询: select *,aid=(select count(1)+1 from tb where tid=t.tid and pid<t.pid) from tb t order by tid,aid ------查询结果: /*------------------------------------------------------------- tid pid aid 1 1 1 1 5 2 2 0 1 2 3 2 3 6 1 */-------------------------------------------------------------
*,
aid=(select count(1)+1 from A where tid=t.tid and pid<t.pid)
from
A t
INSERT @TB
SELECT 1, 1 UNION ALL
SELECT 1, 5 UNION ALL
SELECT 2, 3 UNION ALL
SELECT 3, 6 UNION ALL
SELECT 2, 0SELECT *
FROM (
SELECT *,aid=(SELECT COUNT(*) FROM @TB WHERE tid=TA.tid AND pid<=TA.pid)
FROM @TB AS TA
) TB
ORDER BY tid,aid
/*
tid pid aid
----------- ----------- -----------
1 1 1
1 5 2
2 0 1
2 3 2
3 6 1(5 row(s) affected)
*/
if object_id('[A]') is not null drop table [A]
go
create table [A]([tid] int,[pid] int)
insert [A]
select 1,1 union all
select 1,5 union all
select 2,3 union all
select 3,6 union all
select 2,0
---查询---
select
*,
aid=(select count(1)+1 from A where tid=t.tid and pid<t.pid)
from
A t
order by
tid,
aid---结果---
tid pid aid
----------- ----------- -----------
1 1 1
1 5 2
2 0 1
2 3 2
3 6 1(所影响的行数为 5 行)
insert into @t
select 1,1 union all
select 1,5 union all
select 2,3 union all
select 3,6 union all
select 2, 0
select *,aid=row_number() over (partition by tid order by tid) from @t/*tid pid aid
----------- ----------- --------------------
1 1 1
1 5 2
2 3 1
2 0 2
3 6 1(5 行受影响)*/
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(tid INT, [pid] INT)
go
insert into tb
SELECT 1, 1 UNION ALL
SELECT 1, 5 UNION ALL
SELECT 2, 3 UNION ALL
SELECT 3, 6 UNION ALL
SELECT 2, 0
go
SELECT *,aid=(SELECT COUNT(*) FROM tb WHERE tid=TA.tid AND pid<=TA.pid)
FROM tb AS TA
ORDER BY tid,aid
go
/*------------
1 1 1
1 5 2
2 0 1
2 3 2
3 6 1
-------*/
insert @T1
select 1, 1 union all
select 1, 5 union all
select 2, 3 union all
select 3, 6 union all
select 2, 0--sql 2005
select [tid],[pid],aid=rank() over(partition by [tid] order by [pid])
from @T1tid pid aid
----------- ----------- --------------------
1 1 1
1 5 2
2 0 1
2 3 2
3 6 1(5 行受影响)
insert @T1
select 1, 1 union all
select 1, 5 union all
select 2, 3 union all
select 3, 6 union all
select 2, 0select [tid],[pid],aid=(select count(1) from @T1 where tid=a.tid and pid<a.pid)+1
from @T1 a
order by [tid],[pid]tid pid aid
----------- ----------- --------------------
1 1 1
1 5 2
2 0 1
2 3 2
3 6 1(5 行受影响)
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-06-24 12:39:55
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([tid] int,[pid] int)
insert [tb]
select 1,1 union all
select 1,5 union all
select 2,3 union all
select 3,6 union all
select 2,0
------开始查询:
select *,aid=(select count(1)+1 from tb where tid=t.tid and pid<t.pid)
from tb t
order by tid,aid
------查询结果:
/*-------------------------------------------------------------
tid pid aid
1 1 1
1 5 2
2 0 1
2 3 2
3 6 1
*/-------------------------------------------------------------