22,209
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE #T
(
id INT,
city VARCHAR(10),
name VARCHAR(20),
score INT
)
INSERT INTO #T VALUES(1,'港城','奶茶',100)
INSERT INTO #T VALUES(2,'港城','奶茶-一店',80)
INSERT INTO #T VALUES(3,'龙海湾','小吃',70)
INSERT INTO #T VALUES(4,'港城','奶茶-二店',90)
INSERT INTO #T VALUES(5,'龙海湾','健身',85)
INSERT INTO #T VALUES(6,'龙海湾','KFC',75)
INSERT INTO #T VALUES(7,'龙海湾','农家乐',30)
INSERT INTO #T VALUES(8,'龙海湾','KFC-小路口店',30)
INSERT INTO #T VALUES(9,'港城','电影院',98)
INSERT INTO #T VALUES(10,'龙海湾','密室逃脱',65)
不好意思第一次发帖 忘记给大家示例数据的代码了
select a.*
from tb a
join (select city,name,max(score) as max_score from tb where length(name) = 1 group by city,name) b
on a.city = b.city and left(a.name,1) = b.name
order by a.city,b.max_score desc,left(replace(a.name,'-','') + '0000',4)
SELECT * FROM dbo.table1
ORDER BY city ,LEFT(name,1),id ASC,score DESC
试试
CREATE TABLE #T
(
id INT,
city VARCHAR(10),
name VARCHAR(10),
score INT
)
INSERT INTO #T VALUES(1,'a','A',100)
INSERT INTO #T VALUES(2,'a','A-1',80)
INSERT INTO #T VALUES(3,'b','C',70)
INSERT INTO #T VALUES(4,'a','A-2',90)
INSERT INTO #T VALUES(5,'b','D',85)
INSERT INTO #T VALUES(6,'b','B',75)
INSERT INTO #T VALUES(7,'b','E',30)
INSERT INTO #T VALUES(8,'b','B-1',30)
INSERT INTO #T VALUES(9,'a','F',98)
INSERT INTO #T VALUES(10,'b','G',65)
SELECT id,city,name,score FROM
(
SELECT *, (SELECT score FROM #t WHERE name = LEFT(a.name,1) ) AS x FROM #T a
) a ORDER BY city, x DESC,id
CREATE TABLE #T
(
id INT,
city VARCHAR(10),
name VARCHAR(20),
score INT
)
INSERT INTO #T VALUES(1,'港城','奶茶',100)
INSERT INTO #T VALUES(2,'港城','奶茶-一店',80)
INSERT INTO #T VALUES(3,'龙海湾','小吃',70)
INSERT INTO #T VALUES(4,'港城','奶茶-二店',90)
INSERT INTO #T VALUES(5,'龙海湾','健身',85)
INSERT INTO #T VALUES(6,'龙海湾','KFC',75)
INSERT INTO #T VALUES(7,'龙海湾','农家乐',30)
INSERT INTO #T VALUES(8,'龙海湾','KFC-小路口店',30)
INSERT INTO #T VALUES(9,'港城','电影院',98)
INSERT INTO #T VALUES(10,'龙海湾','密室逃脱',65)
SELECT id,city,name,score FROM
(
SELECT *, (SELECT score FROM #t WHERE name = LEFT(a.name,case when CHARINDEX('-',a.name)=0 then 999 else CHARINDEX('-',a.name)-1 end) ) AS x FROM #T a
) a ORDER BY city, x DESC,id
代码基本上都是复制的一楼的