SELECT impressions.*
FROM impressions
WHERE impressions.user_id = 2
AND impressions.action_name = 'show'
AND (impressions.message IS NOT NULL)
GROUP BY impressionable_id, impressionable_type
我想从表格中选择印象ID降序后在Impressionable_id和impresssionable_type排序中唯一的所有最后印象,并获取最近的10
进一步解释
id, impressionabale_type, impressionable_id, action_name
50012, assignment, 2, show
50011, assignment, 1, show
50010, person, 1, show
50009, assignment, 1, show
50008, person, 5, show
50007, person, 4, show
50006, person, 3, show
50005, person, 1, show
50004, person, 1, show
50003, person, 2, show
50002, person, 2, show
50001, person, 1, show
50000, person, 1, show
理想情况下,我想要这个
50012, assignment, 2, show
50011, assignment, 1, show
50010, person, 1, show
50008, person, 5, show
50007, person, 4, show
50006, person, 3, show
50003, person, 2, show
我曾尝试过与众不同,但我的SQL知识充其量是公平的。
我懂了
PG::GroupingError: ERROR: column "impressions.id" must appear in the GROUP BY clause or be used in an aggregate function
有人可以照亮一下吗
也许这将满足您的需求:
SELECT t2.*
FROM (
SELECT DISTINCT impressionable_id, impressionabale_type
FROM impressions
WHERE impressions.action_name = 'show'
) t1, LATERAL (
SELECT *
FROM impressions
WHERE (t1.impressionable_id,t1.impressionabale_type) = (impressionable_id,impressionabale_type)
ORDER BY id DESC
LIMIT 1
) t2
ORDER BY id DESC
LIMIT 10
这将找到和的所有唯一组合,impressionable_id
并且impressionable_type
对于每个组合,都将id
在LATERAL
子查询中找到具有最大组合的行。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句