計算重複記錄並選擇前 2 個

我有一個令人不安的問題，我只想選擇創建的每個 dbspace 的前 2 個塊。

使用的查詢：

SELECT
a.dbsnum,
b.chknum,
a.name,
b.fname
FROM
sysdbspaces a, syschunks b where a.dbsnum = b.dbsnum
GROUP BY
1,2,3,4
ORDER BY 1,2

輸出：

dbsnum  1
chknum  1
name    rootdbs
fname   /dev/informix/rootdbs01

dbsnum  1
chknum  344
name    rootdbs
fname   /dev/informix/chunk1081

dbsnum  1
chknum  965
name    rootdbs
fname   /dev/informix/chunk1280

dbsnum  1
chknum  1099
name    rootdbs
fname   /dev/informix/chunk1281

我想要的是：

dbsnum  1
chknum  1
name    rootdbs
fname   /dev/informix/rootdbs01

dbsnum  1
chknum  344
name    rootdbs
fname   /dev/informix/chunk1081

我試圖將邏輯放在一起以選擇例如從存在的每個 dbspace 中創建的前 2 個塊。rootdbs 總共有 4 個塊，我只想要前 2 個。

我可以使用 korn shell 腳本來實現這一點：

for i in `echo "output to pipe cat without headings select unique(dbsnum) from sysdbspaces" | dbaccess sysmaster 2> /dev/null | sed '/^$/d'`
do
fname=`echo "output to pipe cat without headings select a.fname from (select 
first 2
a.dbsnum,
b.chknum,
a.name,
b.fname
FROM
sysdbspaces a, syschunks b where a.dbsnum = b.dbsnum
AND a.dbsnum = '$i'
--AND a.is_temp = 1
GROUP BY
1,2,3,4
ORDER BY 1,2) a" | dbaccess sysmaster 2> /dev/null | sed '/^$/d'`
echo "$fname"
done 

正如 Akina 在評論中提到的那樣，如果總共只有 4 條記錄，並且您想要前 2 條，並且您已經按照您想要的方式訂購了它們，那麼您只需將LIMIT子句添加到您的查詢中，如下所示：

SELECT
a.dbsnum,
b.chknum,
a.name,
b.fname
FROM
sysdbspaces a, syschunks b where a.dbsnum = b.dbsnum
GROUP BY
1,2,3,4
ORDER BY 1,2
LIMIT 2;

以上是針對Informix語法的，並假設您不小心錯誤地將問題標記為“sql-server”（因為那是 Microsoft SQL Server 標記）。否則，如果您想要 Microsoft SQL Server 的語法，就是這樣：

SELECT TOP 2
a.dbsnum,
b.chknum,
a.name,
b.fname
FROM
sysdbspaces a, syschunks b where a.dbsnum = b.dbsnum
GROUP BY
1,2,3,4
ORDER BY 1,2

select * from (SELECT
   row_number() over (partition by 1,2,3,4 order by 1,2) as RowNo,
   a.dbsnum,
   b.chknum,
   a.name,
   b.fname
   FROM
   sysdbspaces a, syschunks b where a.dbsnum = b.dbsnum
   GROUP BY
   1,2,3,4) as innerSelect
where RowNo <=2

引用自：https://dba.stackexchange.com/questions/285000