34,575
社区成员
发帖
与我相关
我的任务
分享
SELECT *,
流水号 = DENSE_RANK() OVER(PARTITION BY 类别 ORDER BY 列A)
FROM (
SELECT 列A='A', 类别='LB1' UNION ALL
SELECT 列A='A', 类别='LB1' UNION ALL
SELECT 列A='B', 类别='LB1' UNION ALL
SELECT 列A='C', 类别='LB1' UNION ALL
SELECT 列A='D', 类别='LB2' UNION ALL
SELECT 列A='D', 类别='LB2' UNION ALL
SELECT 列A='E', 类别='LB2'
) tmp
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-05-25 15:40:36
-- 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.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([列A] varchar(1),[类别] varchar(3),[流水号] int)
insert [tb]
select 'A','LB1',0 union all
select 'A','LB1',0 union all
select 'B','LB1',0 union all
select 'C','LB1',0 union all
select 'D','LB2',0 union all
select 'D','LB2',0 union all
select 'E','LB2',0
--------------开始查询--------------------------
select
列A,类别,
(select count(distinct 列A)+1 from tb where 列A<t.列A and 类别=t.类别 )
from
tb t
----------------结果----------------------------
/* 列A 类别
---- ---- -----------
A LB1 1
A LB1 1
B LB1 2
C LB1 3
D LB2 1
D LB2 1
E LB2 2
(7 行受影响)
*/
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([列A] [nvarchar](10),[类别] [nvarchar](10),[流水号] [int])
INSERT INTO [tb]
SELECT 'A','LB1','0' UNION ALL
SELECT 'A','LB1','0' UNION ALL
SELECT 'B','LB1','0' UNION ALL
SELECT 'C','LB1','0' UNION ALL
SELECT 'D','LB2','0' UNION ALL
SELECT 'D','LB2','0' UNION ALL
SELECT 'E','LB2','0'
-->SQL查询如下:
UPDATE T SET
流水号=rn
FROM (
SELECT *,rn=dense_rank()over(partition by [类别] order by [列A])
FROM [tb]
) T
SELECT * FROM tb
/*
列A 类别 流水号
---------- ---------- -----------
A LB1 1
A LB1 1
B LB1 2
C LB1 3
D LB2 1
D LB2 1
E LB2 2
(7 行受影响)
*/
select
列A,类别,
(select count(distinct 流水号)+1 from tb where 列A<t.列A and 类别=t.类别 )
from
tb t
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([A] varchar(1),[类别] varchar(3),[流水号] int,[序号] int)
insert [tb]
select 'A','LB1',0,1 union all
select 'A','LB1',0,1 union all
select 'B','LB1',0,2 union all
select 'C','LB1',0,2 union all
select 'D','LB2',0,1 union all
select 'D','LB2',0,1 union all
select 'E','LB2',0,2
go
select a,类别,流水号,
序号=(select count(distinct a)+1 from tb where 类别=t.类别 and a<t.a)
from tb t
/**
a 类别 流水号 序号
---- ---- ----------- -----------
A LB1 0 1
A LB1 0 1
B LB1 0 2
C LB1 0 3
D LB2 0 1
D LB2 0 1
E LB2 0 2
(7 行受影响)
**/