mysql in 匹配规则,mysql强制结果的顺序与IN子句的顺序匹配

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

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

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.

Here is the current query:

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

These entries

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)

should not be a match.

Additionally:

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)

should be a match.

解决方案

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/%'