如,有 ID,CLASS,NAME 三个字段,我想先按CLASS 分组 然后每组中的数据再按姓名排序。
CREATE TABLE TEST(ID,INT IDENTITY(1,1) PRIMARY KEY,CLASS INT,NAME NVARCHAR(50))
INSERT TEST(CLASS,NAME)
SELECT (1,'A') UNION ALL
SELECT (1,'D') UNION ALL
SELECT (1,'SD') UNION ALL
SELECT (1,'SD') UNION ALL
SELECT (2,'FF') UNION ALL
SELECT (2,'W') UNION ALL
SELECT (3,'ER') UNION ALL
SELECT (2,'TY') UNION ALL
SELECT (3,'UI') UNION ALL
SELECT (3,'PO') UNION ALL
SELECT (3,'OI')
CREATE TABLE TEST(ID,INT IDENTITY(1,1) PRIMARY KEY,CLASS INT,NAME NVARCHAR(50))
INSERT TEST(CLASS,NAME)
SELECT (1,'A') UNION ALL
SELECT (1,'D') UNION ALL
SELECT (1,'SD') UNION ALL
SELECT (1,'SD') UNION ALL
SELECT (2,'FF') UNION ALL
SELECT (2,'W') UNION ALL
SELECT (3,'ER') UNION ALL
SELECT (2,'TY') UNION ALL
SELECT (3,'UI') UNION ALL
SELECT (3,'PO') UNION ALL
SELECT (3,'OI')
select *
from test
order by name
group by class
CREATE TABLE TEST(ID INT IDENTITY(1,1) PRIMARY KEY,CLASS INT,NAME NVARCHAR(50))
INSERT INTO TEST(CLASS,NAME)
SELECT 1,'A' UNION ALL
SELECT 1,'D' UNION ALL
SELECT 1,'SD' UNION ALL
SELECT 1,'SD' UNION ALL
SELECT 2,'FF' UNION ALL
SELECT 2,'W' UNION ALL
SELECT 3,'ER' UNION ALL
SELECT 2,'TY' UNION ALL
SELECT 3,'UI' UNION ALL
SELECT 3,'PO' UNION ALL
SELECT 3,'OI'SELECT MIN(id) AS id ,CLASS ,MAX(NAME) AS name
FROM TEST GROUP BY CLASS ORDER BY MAX(NAME)
/*
id CLASS name
----------- ----------- --------------------------------------------------
1 1 SD
7 3 UI
5 2 W
*/
INSERT TEST (CLASS,NAME)
SELECT 1,'A' UNION ALL
SELECT 1,'D' UNION ALL
SELECT 1,'SD' UNION ALL
SELECT 1,'SD' UNION ALL
SELECT 2,'FF' UNION ALL
SELECT 2,'W' UNION ALL
SELECT 3,'ER' UNION ALL
SELECT 2,'TY' UNION ALL
SELECT 3,'UI' UNION ALL
SELECT 3,'PO' UNION ALL
SELECT 3,'OI' select b.* from
(select distinct class from test) a
cross apply
(select * from test where class = a.class) b
order by b.class, b.name
select row_number() over(partition by class order by name) as rowindex,* from text