我想展示他们的最高折扣。但我仍然想展示没有报价的业务。
业务存储在business_tb

business_id | business_name
------------+---------------
     1      |      aaa
     2      |      bbb
     3      |      ccc

这些企业提供的折扣存储在deal_offer_tb
deal_offer_id | business_id | deal_id
--------------+-------------+----------
       1      |      1      |    3
       2      |      1      |    2
       3      |      2      |    0
       4      |      1      |    1
       5      |      3      |    3

折扣类型存储在deal_tb中。
deal_id | discount
--------+----------
    1   |    40%
    2   |    30%
    3   |    20%
    4   |    10%

所以我想要的展示应该是这样的:
 1 |  aaa | 40%
 2 |  bbb | ---
 3 |  ccc | 20%

但我现在的问题是:
SELECT a.business_id, a.business_name, c.discount
    FROM business_tb a
    LEFT JOIN (SELECT min(deal_id) AS deal_id, business_id FROM deal_offer_tb GROUP BY business_id) b ON a.business_id = b.business_id
    LEFT JOIN deal_tb c ON b.deal_id = c.deal_id

我只得到:
 1 | aaa | 40%
 3 | ccc | 20%

它不显示没有提供折扣的业务。
我怎样才能得到我想要的结果呢?
更新:我不知道之前发生了什么,但我的查询按我所希望的方式进行。感谢那些回答的人的努力。感谢你,大好时光!

最佳答案

我将通过使用子查询来找到每个业务的最大折扣,加入deal_offer_tbdeal_tb表。然后,将此子查询连接到business_tb表以获得最终结果。请注意,我使用一个初始的LEFT JOIN来解释一个给定的业务甚至可能没有与之相关联的交易。在这种情况下,我给该企业分配了0的最大折扣(这是有意义的,从那时起,完全正常的价格将适用)。

SELECT
    t1.business_id,
    t1.business_name,
    COALESECE(t2.max_discount, 0) AS max_discount
FROM business_tb t1
LEFT JOIN
(
    SELECT t1.business_id, MAX(t2.discount) AS max_discount
    FROM deal_offer_tb t1
    INNER JOIN deal_tb t2
        ON t1.deal_id = t2.deal_id
    GROUP BY t1.business_id
) t2
    ON t1.business_id = t2.business_id

关于php - 显示最高报价,但仍显示不报价,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/43795353/

10-12 01:13