代码之家 › 专栏 › 技术社区 › sayth

SQL Server top函数返回最小长度字符串

sql-server sql

sayth · 技术社区 · 6 年前

考虑到这个问题

查询站点中最短和最长的两个城市以其名义)。如果有多个最小或最大的城市, 按字母顺序排列时,请选择第一个。
Input Format

The STATION table is described as follows:

经度。

Sample Input

Let's say that CITY only has four entries: DEF, ABC, PQRS and WXY

Sample Output

ABC 3
PQRS 4

这是我的解决方案。

SELECT TOP 1 * FROM CITY, LEN(CITY) FROM STATION ORDER BY LEN(CITY), CITY ASC;
SELECT TOP 1 * FROM CITY, LEN(CITY) FROM STATION ORDER BY LEN(CITY) DESC;

W3 schools TOP

SELECT TOP 3 * FROM Customers;

我的解决办法应该行得通。

但是,我收到了这个错误。

Msg 156, Level 15, State 1, Server WIN-ILO9GLLB9J0, Line 6 
Incorrect syntax near the keyword 'FROM'. 
Msg 156, Level 15, State 1, Server WIN-ILO9GLLB9J0, Line 7 
Incorrect syntax near the keyword 'FROM'.

3 回复 | 直到 6 年前

Wolfgang Kais 6 年前

星号用于“选择所有列”,如果要添加更多列,列名和星号必须用逗号分隔。因为要显式指定列,所以不要使用星号。还有:你用过这个词 FROM

SELECT TOP 1 CITY, LEN(CITY) AS LENGTH FROM STATION ORDER BY LEN(CITY), CITY;
SELECT TOP 1 CITY, LEN(CITY) AS LENGTH FROM STATION ORDER BY LEN(CITY) DESC, CITY;

如果您只想用一个查询检索这两行,我们 UNION

SELECT TOP 1 CITY, LEN(CITY) AS LENGTH FROM STATION ORDER BY LEN(CITY), CITY
UNION ALL
SELECT TOP 1 CITY, LEN(CITY) AS LENGTH FROM STATION ORDER BY LEN(CITY) DESC, CITY;

D-Shih 6 年前

这个错误导致了你 len 函数可能会放入 select 从句代替 from 条款

ROW_NUMBER 具有 窗口函数 创建行号顺序的步骤 CITY ASC rn = 1 划船。

select CITY,LEN(CITY) from (
    SELECT *,ROW_NUMBER() OVER(PARTITION BY LEN(CITY) ORDER BY CITY ASC) rn 
    FROM STATION 
) t1
where t1.rn =1

sqlfiddle

Dimith 6 年前

SELECT CITY, length FROM
(
    select TOP 1 CITY, LEN(CITY) as length
    from STATION
    order by LEN(CITY)
) [Min]
UNION ALL
SELECT CITY, length FROM
(
    select TOP 1 CITY, LEN(CITY) as length
    from STATION
    order by LEN(CITY) desc
) [Max]