选择行如何在sql中查询 [英] select rows how to query in sql
本文介绍了选择行如何在sql中查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个用例
a 3
a 4
a 5
a 6
a 5
b 3
b 5
b 3
如何得到这样的输出
a 4
a 5
b 5
b 3
选择a和b的最高数字,但只选择2行
choose the hight number for a and b, but just 2 rows
那是我现在写的查询,似乎不起作用
that's the query I wrote now, seems it is not working
SELECT id, barcode, actualsku, inventorycount
FROM ( SELECT pallet.id AS id,
pallet.barcode AS barcode,
inventoryunit.sku AS actualsku,
Count(inventoryunit.id) AS inventorycount
FROM (SELECT *
FROM mft.asset
WHERE container_type = 'PALLET'
AND location_type = 'PRIME' :: mft.location_type
AND is_deleted = FALSE
AND ( attributes ->> 'sku' :: text ) IS NOT NULL) pallet
LEFT OUTER JOIN (SELECT *
FROM mft.asset
WHERE asset_type = 'INVENTORYUNIT' :: mft.asset_type
AND is_deleted = FALSE) inventoryunit
ON pallet.id = inventoryunit.parent_id
GROUP BY inventoryunit.sku,
pallet.id,
pallet.barcode,
pallet.attributes ) test
WHERE (SELECT COUNT(*) FROM test as t
WHERE t.actualsku = test.actualsku
AND t.inventorycount <= test.inventorycount
) <= 2
推荐答案
通常使用窗口函数来完成:
This is typically done using window functions:
select col1, col2
from (
select col1, col2,
row_number() over (partition by col1 order by col2 desc) as rnk
from the_table
) t
where rnk <= 2
order by col1, col2;
在线示例: http://rextester.com/WKLTSB43296
这篇关于选择行如何在sql中查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文