INNER JOIN 查询中的 MySQL 聚合函数

Question

这是我正在处理的查询:

SELECT *
FROM `mav_proposals`
INNER JOIN
  (SELECT id AS mav_id, 
             mav_proposal_id
   FROM mav) AS mav ON `mav_proposals`.`mav_id` = `mav`.`mav_id`
WHERE `paid_at` IS NULL
  AND (`mav`.`mav_proposal_id` IS NULL
       OR `mav_proposals`.`sold_at` IS NOT NULL)
  AND `hidden` = 0
  AND EXISTS
    (SELECT *
     FROM `mav`
     WHERE `mav_proposals`.`mav_id` = `mav`.`id`)

<小时/>

这是一个问题的reextester:http://rextester.com/HDI75486

我希望每个 mav 只显示最新的 mav_proposal。

这是期望的结果:

+-----+--------+----------+-------+---------------------+---------+---------+--------+--------+-----------------+
|  id | mav_id | buyer_id | price |          created_at | sold_at | paid_at | hidden | mav_id | mav_proposal_id |
+-----+--------+----------+-------+---------------------+---------+---------+--------+--------+-----------------+
|  1  |      1 |     3704 | 10800 | 15.03.2017 17:56:25 |    NULL |    NULL |  false |      1 |            NULL |
|  4  |      2 |     4462 | 10800 | 09.03.2017 16:27:22 |    NULL |    NULL |  false |      2 |            NULL |
+-----+--------+----------+-------+---------------------+---------+---------+--------+--------+-----------------+

我尝试在第一个 INNER JOIN 中使用聚合 MAX(mav.id) 函数，但没有成功。

有什么想法吗？

Answer 1

几件事:

1. 在我看来，您需要查询所有 mav_proposals 表，以获取每个 mav_ID 的最大值。 (SELECT max(mav_proposal_ID) max_Proposal, Mav_ID
来自 mav_proposals
按 MAV_ID 分组)

2. 我不知道什么存在正在购买你......所以我消除了它

3. 我不知道为什么存在内联 mav。所以我把它删除了。

假设:

1. 这确实假设 max_proposal 的 max(ID) 始终是 mav_ID 的“最新”。

接受这一点，我们可以将内联 View 合并为一个存在或一个连接:这是连接方法(通常是第二快)

SELECT m.Id, Buyer_ID, price, Created_at, Sold_at, Paid_at, hidden,  m.ID, mav_proposal_ID
FROM MAV M
INNER JOIN mav_proposals MP 
 on M.id = MP.mav_id
INNER JOIN (SELECT max(ID) max_Proposal, Mav_ID
            FROM mav_proposals
            GROUP BY MAV_ID) A
  on MP.ID = A.Max_Proposal
 and M.ID = A.Mav_ID
WHERE MP.paid_at IS NULL
  AND (M.mav_proposal_id IS NULL  
    OR MP.sold_at IS NOT NULL)
  AND MP.hidden = 0 

这是 in 方法(通常最慢):

SELECT m.Id, Buyer_ID, price, Created_at, Sold_at, Paid_at, hidden,  m.ID, mav_proposal_ID
FROM MAV M
INNER JOIN mav_proposals MP 
 on M.id = MP.mav_id

WHERE MP.paid_at IS NULL
  AND (M.mav_proposal_id IS NULL  
    OR MP.sold_at IS NOT NULL)
  AND MP.hidden = 0 

 and (MP.ID, M.ID)  IN (SELECT max(ID) max_Proposal, Mav_ID
            FROM mav_proposals
            GROUP BY MAV_ID)

这是存在的(通常最快)

SELECT m.Id, Buyer_ID, price, Created_at, Sold_at, Paid_at, hidden,  m.ID, mav_proposal_ID
FROM MAV M
INNER JOIN mav_proposals MP 
 on M.id = MP.mav_id

WHERE MP.paid_at IS NULL
  AND (M.mav_proposal_id IS NULL  
    OR MP.sold_at IS NOT NULL)
  AND MP.hidden = 0 
 and Exists (SELECT max(ID) max_Proposal, Mav_ID
             FROM mav_proposals MP1
             GROUP BY MAV_ID
             HAVING max_proposal = mp.id
                and mp1.mav_id = m.id)