使用 group by in 并返回行的标识

发布于 2024-12-04 19:50:22 字数 454 浏览 2 评论 0原文

对于这张表...

id  type   food     price 
--------------------------
1   veg    carrot   10    
2   veg    turnip   11
3   fruit  bramble  6
4   fruit  rasp     4
5   fruit  current  9
...

我可以像这样返回每种食物类型最昂贵食物的最高价格...

select max(price) from tableName group by type;

但是我想返回包含每种食物类型最昂贵食物的每一行的 id 号。每种食物类型只返回一行。即返回这个......

id  
----
2
5 
...

这是我真正问题的简化版本。

原文

For this table...

id  type   food     price 
--------------------------
1   veg    carrot   10    
2   veg    turnip   11
3   fruit  bramble  6
4   fruit  rasp     4
5   fruit  current  9
...

I can return the max price of the most expensive food for each food type like this...

select max(price) from tableName group by type;

But I'd like to return the id number of each row that contains the most expensive food for each food type. And return one and only one row per food type. Ie return this....

id  
----
2
5 
...

This is a simplified version of my real problem.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

空心空情空意 2024-12-11 19:50:22

SELECT id
FROM ( SELECT id, type
       FROM table
       ORDER BY price DESC) AS h
GROUP BY type

SELECT id
FROM ( SELECT id, type
       FROM table
       ORDER BY price DESC) AS h
GROUP BY type

回复收藏 0 原文

甜扑 2024-12-11 19:50:22

当更多食物具有相同类型和价格时，这个可怕的查询将起作用。
我几乎不会在生产中使用它，因为这是无法维护的。

SELECT MIN(id) AS id 
FROM (
    SELECT t.id AS id, t.type
    FROM tableName t
    JOIN (
        SELECT MAX(price) AS m, type
        FROM tableName 
        GROUP BY type
    ) sub 
        ON sub.m=t.price AND sub.type=t.type
    ORDER BY id
) whatever
GROUP BY type

This horrible query will work when more food have the same type and price.
I would hardly ever use this in production as this is unmaintainable.

SELECT MIN(id) AS id 
FROM (
    SELECT t.id AS id, t.type
    FROM tableName t
    JOIN (
        SELECT MAX(price) AS m, type
        FROM tableName 
        GROUP BY type
    ) sub 
        ON sub.m=t.price AND sub.type=t.type
    ORDER BY id
) whatever
GROUP BY type

回复收藏 0 原文

月亮邮递员 2024-12-11 19:50:22

SELECT max(id) from
(SELECT t1.* FROM tableName t1 
JOIN (SELECT type, max(price) AS price FROM tableName GROUP BY type) t2 
ON t2.type = t1.type AND t2.price = t1.price) t3
GROUP BY userId;

SELECT max(id) from
(SELECT t1.* FROM tableName t1 
JOIN (SELECT type, max(price) AS price FROM tableName GROUP BY type) t2 
ON t2.type = t1.type AND t2.price = t1.price) t3
GROUP BY userId;

回复收藏 0 原文

~没有更多了~