mysql强制结果的顺序与IN子句的顺序匹配 [英] mysql force order of results found to match order of IN clause

问题描述

此问题通常不同于> 询问有关通过IN子句对最终结果进行排序的问题.

This question is different to a commonly asked question about ordering the final results by the IN clause.

我想强制包含IN子句的查询返回的结果与IN子句的顺序匹配.

I would like to force the results returned by the query that contains the IN clause, to match the order of the IN clause.

这是我正在研究的原始问题来自.

我想更改下面的查询，以便在按datetime排序formation_page_hits表时，每个session_id的progress=4和progress=7之前都包含一行包含progress=2的行.

I'd like to alter the query below so that a row containing progress=2 occurs before progress=4 and progress=7 for each session_id when ordering the formation_page_hits table by datetime.

这是当前查询:

SELECT  COUNT(*)
FROM    (
    SELECT  session_id
    FROM    formation_page_hits
    WHERE   progress IN (2, 4, 7)
            AND datetime >= '2011-03-23'
            AND datetime < '2011-03-24'
    GROUP BY
            session_id
    HAVING  COUNT(DISTINCT progress) = 3
    ) q

这些条目

datetime,               session_id, progress
('2011-03-01 01:02:11', 'abc',      2)
('2011-03-01 01:02:12', 'abc',      4)
('2011-03-01 01:02:13', 'abc',      7)

应该与查询匹配，但是:

should be a match for the query, but:

datetime,               session_id, progress
('2011-03-01 01:02:11', 'abc',      4)
('2011-03-01 01:02:12', 'abc',      2)
('2011-03-01 01:02:13', 'abc',      7)

不应该匹配.

另外:

datetime,               session_id, progress
('2011-03-01 01:02:11', 'abc',      4)
('2011-03-01 01:02:12', 'abc',      2)
('2011-03-01 01:02:13', 'abc',      4)
('2011-03-01 01:02:14', 'abc',      7)

应该是一个匹配项.

推荐答案

更常见的方法是对自连接进行两次加倍，以在日期时间递增的情况下进行三路连接.但是，这并不是一个性能良好的查询.

The more common way is to double self-join to end up with a three way join ON ascending date time. That, however, is hardly a well performing query.

select *
from
(
    SELECT  session_id, group_concat(concat('|',progress,'/') order by datetime) list
    FROM    formation_page_hits
    WHERE   progress IN (2, 4, 7)
            AND datetime >= '2011-03-23'
            AND datetime < '2011-03-24'
    GROUP BY session_id
    HAVING  COUNT(DISTINCT progress) = 3
) X
where list like '%|2/%|4/%|7/%'

这篇关于mysql强制结果的顺序与IN子句的顺序匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！