试图限制mysql结果中显示的数量

uz75evzq 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(254)

我有一个表，存储id，项目，食物类型，图像和价格
我想将结果限制为商店中销售的每种类型中的一种，例如狗粮、猫粮、dvd（降序排列）mysql数据库表中的示例：nunca

id      item                    type        image   price
=========================================================
1       Pedigree Pouches        dog food    img1    $4.99
2       Cesar Classic Selection dog food    img2    $3.99
3       Meaty Strips Chicken    dog food    img3    $2.99
4       Jelly Fish Selection    cat food    img4    $1.99
5       Bananas                 fruit       img5    $2.84
6       Celery                  vegetables  img6    $5.99
7       Apple                   fruit       img7    $0.95
8       Pineapple Juice         Soft drink  img8    $0.99
9       Oranges                 fruit       img9    $1.99
10      Cabbage                 vegetables  img10   $0.99
11      Suicide Squad           dvd         img11   $12.99

查询的最终结果应该是这样的

id      item                    type        image   price
==========================================================
11      Suicide Squad           dvd         img11   $12.99
10      Cabbage                 vegetables  img10   $0.99
9       Oranges                 fruit       img9    $1.99
8       Pineapple Juice         Soft drink  img8    $0.99
4       Jelly Fish Selection    cat food    img4    $1.99
3       Meaty Strips Chicken    dog food    img3    $2.99

我尝试过各种mysql查询，但都没有用

sql mysql

来源：https://stackoverflow.com/questions/50321234/trying-to-limit-the-amount-be-shown-in-a-mysql-result

2条答案

按热度按时间

b4wnujal1#

select * from t where 
id in(select max(t.id) id from t group by t.type) 
order by id desc

我想可能有窗口或连接的替代品，但这个似乎适合这个目的。

赞(0）回复(0）举报 2021-06-21

yc0p9oo02#

典型方法使用相关子查询：

select n.*
from nunca n
where n.id = (select max(n2.id) from nunca n2 where n2.type = n.type);

这将选择每种类型的最后一行。

赞(0）回复(0）举报 2021-06-21

我来回答

试图限制mysql结果中显示的数量

2条答案

相关问题

热门标签

最新问答