如何在一行中获得最小值和最大值以及相应的id

vwkv1x7d 于 2021-06-15 发布在 Mysql

关注(0)|答案(3)|浏览(299)

我有一个名为product的表，其中有3个字段：

prod_id  price  pname
1        100    abc
2        200    bbc
3        300    cbc

我想在一行中得到最小值和最大值的数据，即：

minprice  prod_id1   maxprice   prod_id2
 100       1          300        3

我尝试了以下查询，但找不到确切的结果（如果我有更多数据）

SELECT s1.prod_id as prod_id1, 
       s2.prod_id as prod_id2, 
       min(s1.price) as minprice,
       max(s2.price) as maxprice 
FROM product s1
INNER JOIN product s2 ON s1.prod_id = s2.prod_id

sql mysql sql-order-by

来源：https://stackoverflow.com/questions/54072440/how-to-get-min-and-max-values-and-corresponding-ids-in-a-single-row

3条答案

按热度按时间

vkc1a9a21#

你可以这样做 LIMIT :

SELECT
    pmin.price AS minprice, 
    pmin.prod_id AS minprice_prodid,
    pmax.price AS maxprice,
    pmax.prod_id AS maxprice_prodid
FROM (
    SELECT prod_id, price FROM product ORDER BY price LIMIT 1
) pmin
CROSS JOIN (
    SELECT prod_id, price FROM product ORDER BY price DESC LIMIT 1
) pmax

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

nue99wik2#

你可以在下面试试-

select X.id as prod_id1, minprice, Y.id as prod_id2, maxprice
from
(
select id, price as minprice from tablename
where price =(select min(price) from tablename b)
)X cross join
(
select id, price as maxprice from tablename
where price =(select max(price) from tablename c)
)Y

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

btxsgosb3#

SELECT s1.prod_id as prod_id1, 
       s2.prod_id as prod_id2, 
       s1.price as maxprice,
       s2.price as minprice 
FROM products s1
INNER JOIN products s2 ON s1.price > s2.price
order by s1.price desc, s2.price asc
limit 1

演示

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

我来回答

如何在一行中获得最小值和最大值以及相应的id

3条答案

演示

相关问题

热门标签

最新问答