如何从另一个查询中获取mysql查询结果

lb3vh1jj 于 2021-06-21 发布在 Mysql

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

我有3个mysql表：

shop
        id1  shop_name 
         1   shop1
         2   shop2
         3   shop3

    queue_number
        id2  id_shop  queue_current_number
         1      1             32
         2      2             56
         3      3             22

    queue_customer
        id3  id_queue   your_number   
         1      1            33
         2      1            34
         3      1            35
         4      2            57

我从这个问题开始：

SELECT *
FROM shop
LEFT JOIN queue_number ON queue_number.id_shop = shop.id1
WHERE id_shop = 1
GROUP BY id2

我的结果：
商店1>队列\当前\编号=32
shop2>队列\当前\编号=56
shop3>队列\当前\编号=22
我想得到这样的结果：
shop1>队列\当前\编号=35
shop2>队列\当前\编号=57
shop3>队列\当前\编号=22
谢谢！

sql mysql concat

来源：https://stackoverflow.com/questions/52293637/how-to-get-mysql-query-results-from-another-query

2条答案

按热度按时间

jgzswidk1#

我假设您正在寻找最高的值，或者通过优先选择queue\u customer而不是queue\u number（使用coalesce…），或者通过想要两个值中的最高值（使用if（coalesce…）。

SELECT COALESCE( c.your_number, n.queue_current_number ) 
    /* or maybe you want IF( COALESCE( c.your_number, 0 ) > COALESCE( n.queue_current_number, 0 ), c.your_number, n.queue_current_number ) */
    FROM shop s
    LEFT JOIN queue_number n   ON s.id1 = n.id_shop 
                              AND NOT EXISTS( SELECT * FROM queue_number n2
                                              WHERE n.id_shop = n2.id_shop
                                                AND n.queue_current_number < n2.queue_current_number )
    LEFT JOIN queue_customer c ON n.id2 = c.id_queue
                              AND NOT EXISTS( SELECT * FROM current_number c2
                                              WHERE c.id_queue = c2.id_queue
                                                AND n.your_number < n2.your_number )
    WHERE s.id_shop = 1

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

thtygnil2#

使用子查询尝试以下操作：

SELECT shop.id1, shop.name, COALESCE(younumber,queue_current_number) as q_current_number
FROM shop
LEFT JOIN queue_number ON queue_number.id_shop = shop.id1
left join
(select id_queue,max(your_number) as younumber
from queue_customer group by id_queue)c on c.id_queue= shop.id1
WHERE id_shop = 1

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

我来回答

如何从另一个查询中获取mysql查询结果

2条答案

相关问题

热门标签

最新问答