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

SQL查询获取字符串最长的行

postgresql sql

-2

MB34 · 技术社区 · 7 年前

SELECT
    LENGTH(app) AS app_length, 
    ROW_NUMBER() 
OVER (
    PARTITION BY 
        vid, basevid
    ORDER BY 
        vid, basevid, LENGTH(app) 
    ) AS j,
    vid, basevid, app
FROM DATA4 WHERE hol = '40008'

返回以下数据:

app_length,j,vid,basevid,app
2,1,6260,3174,"AT"
2,2,6260,3174,"AT"
9,3,6260,3174,"AT; TH125"
9,4,6260,3174,"AT; TH125"
18,5,6260,3174,"AT; TH125 gasoline"
18,6,6260,3174,"AT; TH125 gasoline"
2,1,6261,3175,"AT"
2,2,6261,3175,"AT"
9,3,6261,3175,"AT; TH125"
9,4,6261,3175,"AT; TH125"
18,5,6261,3175,"AT; TH125 gasoline"
18,6,6261,3175,"AT; TH125 gasoline"
2,1,6260,3174,"AT"
2,2,6260,3174,"AT"
9,3,6260,3174,"AT; TH125"
9,4,6260,3174,"AT; TH125"
18,5,6260,3174,"AT; TH125 gasoline"
18,6,6260,3174,"AT; TH125 gasoline"
2,1,6262,3176,"AT"
2,2,6262,3176,"AT"
9,3,6262,3176,"AT; TH125"
9,4,6262,3176,"AT; TH125"
18,5,6262,3176,"AT; TH125 gasoline"
18,6,6262,3176,"AT; TH125 gasoline"
2,1,6262,3176,"AT"
2,2,6262,3176,"AT"
9,3,6262,3176,"AT; TH125"
9,4,6262,3176,"AT; TH125"
18,5,6262,3176,"AT; TH125 gasoline"
18,6,6262,3176,"AT; TH125 gasoline"
2,1,6262,3176,"AT"
2,2,6262,3176,"AT"
9,3,6262,3176,"AT; TH125"
9,4,6262,3176,"AT; TH125"
18,5,6262,3176,"AT; TH125 gasoline"
18,6,6262,3176,"AT; TH125 gasoline"
2,1,10182,4599,"AT"
9,2,10182,4599,"AT; TH125"
18,3,10182,4599,"AT; TH125 gasoline"
2,1,10328,4651,"AT"
9,2,10328,4651,"AT; TH125"
18,3,10328,4651,"AT; TH125 gasoline"
2,1,9830,4453,"AT"
2,2,9830,4453,"AT"
9,3,9830,4453,"AT; TH125"
9,4,9830,4453,"AT; TH125"
18,5,9830,4453,"AT; TH125 gasoline"
18,6,9830,4453,"AT; TH125 gasoline"
2,1,9830,4453,"AT"
2,2,9830,4453,"AT"
9,3,9830,4453,"AT; TH125"
9,4,9830,4453,"AT; TH125"
18,5,9830,4453,"AT; TH125 gasoline"
18,6,9830,4453,"AT; TH125 gasoline"
2,1,4650,2560,"AT"
2,2,4650,2560,"AT"
9,3,4650,2560,"AT; TH125"
9,4,4650,2560,"AT; TH125"
18,5,4650,2560,"AT; TH125 gasoline"
18,6,4650,2560,"AT; TH125 gasoline"

如何通过vid和basevid返回具有最长app字符串或最长app\u长度的记录?

app_length,j,vid,basevid,app
18,6,6261,3175,"AT; TH125 gasoline"
18,6,6260,3174,"AT; TH125 gasoline"
18,6,6262,3176,"AT; TH125 gasoline"
18,3,10182,4599,"AT; TH125 gasoline"
18,3,10328,4651,"AT; TH125 gasoline"
18,6,9830,4453,"AT; TH125 gasoline"
18,6,4650,2560,"AT; TH125 gasoline"

这个表有4000万条记录,所以这也必须很快。

1 回复 | 直到 7 年前

Gordon Linoff 7 年前

你可以用 distinct on

SELECT DISTINCT ON (hol, vid, base_vid) d.*
FROM DATA4 d
WHERE hol = '40008'
ORDER BY hol, vid, basevid, LENGTH(app) DESC;

这可能是Postgres中最快的方法。如果有索引的话速度会更快 (hol, vid, basevid, LENGTH(app) desc) .