22,209
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-16 09:39:38
-- 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)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[sname] varchar(4),[orgname] varchar(4))
insert [tb]
select 1,'张三','华为' union all
select 2,'李四','华为' union all
select 3,'王五','金蝶'
--------------开始查询--------------------------
select
ID,sname,orgname,id0
from
(
select
*,
id0=row_number() over(PARTITION BY orgname order by id)
from
[tb])t
order by
4
----------------结果----------------------------
/*ID sname orgname id0
----------- ----- ------- --------------------
1 张三 华为 1
3 王五 金蝶 1
2 李四 华为 2
(3 行受影响)
*/
create table tb(ID int, sname varchar(10) , orgname varchar(10))
insert into tb values(1 , '张三' , '华为')
insert into tb values(2 , '李四' , '华为')
insert into tb values(3 , '王五' , '金蝶')
go
select * , px = (select count(1) from tb where orgname = t.orgname and id < t.id) + 1 from tb t order by px , orgname
drop table tb
/*
ID sname orgname px
----------- ---------- ---------- -----------
1 张三 华为 1
3 王五 金蝶 1
2 李四 华为 2
(所影响的行数为 3 行)
*/
select * , px = (select count(1) from tb where orgname = t.orgname and id < t.id) + 1 from tb order by px , orgname