在分组之前使用orderby来自两个不同的表

I have the following query

我有以下查询

SELECT 
  quote.id , 
    quote.createdAt,
    status.status AS 'Status'

FROM
     quote
       JOIN
    quotelog ON quotelog.quote = quote.id
        JOIN
    status ON status.id = quotelog.status

the relationship between quote and quotelog is one to many, what I want is to display the latest quotelog for the quote.

quote和quotelog之间的关系是一对多，我想要的是显示引用的最新quotelog。

Both quote and quotelog have created_at, I'm displaying here the creation date of the quote.

引号和quotelog都有created_at，我在这里显示引用的创建日期。

the result While each quote have multiple status, I only want to get the latest one for each quote

结果虽然每个报价都有多个状态，但我只想获得每个报价的最新报价

id  createdAt   Status
1   07-12-15 6:14   newQuote
2   07-12-15 6:40   newQuote
2   07-12-15 6:40   quoteCancelled
3   07-12-15 7:21   newQuote
3   07-12-15 7:21   quotePaused
4   07-12-15 8:17   newQuote
4   07-12-15 8:17   quoteCompleted

with adding group by quote.id I get this

通过quote.id添加group我得到了这个

id  createdAt   Status
1   07-12-15 6:14   newQuote
2   07-12-15 6:40   newQuote
3   07-12-15 7:21   newQuote
4   07-12-15 8:17   newQuote

I want it to look like this

我希望它看起来像这样

id  createdAt   Status
1   07-12-15 6:14   newQuote
2   07-12-15 6:40   quoteCancelled
3   07-12-15 7:21   quotePaused
4   07-12-15 8:17   quoteCompleted

so what I need is to order by qoutelog.createdAt before grouping by quote.id

所以我需要的是在由quote.id分组之前通过qoutelog.createdAt订购

I found answers in stackoverflow but they only deal with one table either by using HAVING after group by or self join sub query result that contain order by or using Row_number() which Mysql doesn't support

我在stackoverflow中找到了答案但是他们只处理一个表，或者通过在分组后使用HAVING或者使用包含顺序的自连接子查询结果或者使用Row_number（），Mysql不支持

3 个解决方案

#1

Looks like this can be handled with MAX() function :

看起来这可以用MAX（）函数处理：

SELECT 
  quote.id , 
    quote.createdAt,
    max(status.status) AS 'Status'

FROM
     quote
       JOIN
    quotelog ON quotelog.quote = quote.id
        JOIN
    status ON status.id = quotelog.status
GROUP BY quote.id

Since 'q' is bigger then 'n' , it will select quote..* first.

由于'q'大于'n'，它将首先选择引用.. *。

If for each id there can be multiple(more then 2) statuses, including a few with quote...* then you have to specify the logic you want to select them and I'll adjust the query.

如果对于每个id，可以有多个（超过2个）状态，包括一些带引号... *然后你必须指定你想要选择它们的逻辑，我将调整查询。

3 个解决方案

#1

更多相关文章

随机推荐