mysql强制发现的结果顺序与IN子句的顺序匹配

发布于 2024-10-27 07:14:46 字数 1529 浏览 0 评论 0原文

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

我想更改下面的查询，以便每个 progress=2 的行出现在 progress=4 和 progress=7 之前按日期时间排序formation_page_hits表时的code>session_id。

以下是当前查询：

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)

应该与查询匹配，但是：

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)

应该是匹配的。

原文

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.

This is the original question that I am working from.

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

星星的轨迹 2024-11-03 07:14:47

更常见的方法是双重自连接，以升序日期时间的三向连接结束。然而，这并不是一个性能良好的查询。

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

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

回复收藏 0 原文

~没有更多了~