看代碼吧~
WITH Name AS (
SELECT
*
FROM
(
SELECT
xzqdm,
SUBSTRING (zldwdm, 1, 9) xzdm,
COUNT (*) sl
FROM
sddltb_qc
WHERE
xzqdm IN ('130432', '210604')
GROUP BY
xzqdm,
SUBSTRING (zldwdm, 1, 9)
) AS A
ORDER BY
xzqdm,
xzdm,
sl
) SELECT
xzqdm,
xzdm,
sl
FROM
(
SELECT
*, ROW_NUMBER () OVER (
PARTITION BY xzqdm
ORDER BY
sl DESC
) AS Row_ID
FROM
Name
) AS A
WHERE
Row_ID = 2
ORDER BY
xzqdm
其中
select * from (select xzqdm,substring(zldwdm,1,9) xzdm,count(*) sl from sddltb_qc where xzqdm in ('130432','210604') group by xzqdm,substring(zldwdm,1,9)) as a order by xzqdm,xzdm,sl
執(zhí)行結(jié)果：
添加行序號：ROW_NUMBER () OVER (ORDER BY A.bsm ASC) AS 序號
分組添加序號：ROW_NUMBER () OVER (PARTITION BY xzqdm ORDER BY A.bsm ASC) AS 序號
補充：pgsql 表隨機取幾條數(shù)據(jù)
取100條
select * from map_route_info_composite order by random() limit 100
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。
您可能感興趣的文章:- PostgreSQL upsert(插入更新)數(shù)據(jù)的操作詳解
- 使用Postgresql 實現(xiàn)快速插入測試數(shù)據(jù)
- postgresql限制某個用戶僅連接某一個數(shù)據(jù)庫的操作
- PostgreSQL用戶、數(shù)據(jù)庫及表的管理、操作與授權(quán)方式
- 解決postgreSql遠(yuǎn)程連接數(shù)據(jù)庫超時的問題
- 解決sqoop從postgresql拉數(shù)據(jù),報錯TCP/IP連接的問題
- PostgreSql 導(dǎo)入導(dǎo)出sql文件格式的表數(shù)據(jù)實例
