22,210
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([单号] int,[品名] nvarchar(21),[规格] nvarchar(23))
Insert #T
select 1001,N'A',N'13c' union all
select 1001,N'A',N'8c' union all
select 1001,N'B',N'13c' union all
select 1001,N'B',N'9c' union all
select 1002,N'A',N'13c' union all
select 1003,N'B',N'7c'
Go
--测试数据结束
SELECT
*,
DENSE_RANK() OVER (PARTITION BY
单号
ORDER BY
品名
) 行号
FROM
#T;
SELECT 单号,品名,规格 CASE 品名 WHEN 'A' THEN '1' WHEN 'B' THEN '2' END AS 行号 FROM TABLE
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL
DROP TABLE t
GO
CREATE TABLE t(
单号 VARCHAR(10)
,品名 VARCHAR(10)
,规格 VARCHAR(10)
)
GO
INSERT INTO t
SELECT 1001,'A','13c'
UNION ALL SELECT 1001,'A','8c'
UNION ALL SELECT 1001,'B','13c'
UNION ALL SELECT 1001,'B','9c'
UNION ALL SELECT 1002,'A','13c'
UNION ALL SELECT 1003,'B','7c'
GO
SELECT *
,dense_rank() OVER (PARTITION BY [单号] ORDER BY [品名]) AS [排序]
FROM T
/*
单号 品名 规格 排序
---------- ---------- ---------- --------------------
1001 A 13c 1
1001 A 8c 1
1001 B 13c 2
1001 B 9c 2
1002 A 13c 1
1003 B 7c 1
*/