具有复杂聚合结果的MYSQL查询

Question

这是一个场景。我有两个表，它们的模式如下

经验：(id，职务)

user_experience：(id，experience_id，created_date)

当用户将体验添加到其待办事项列表中时，user_experience中会添加一条记录。现在我想知道当体验达到2，5或10个列表的里程碑时的日期。

必填结果: id (experience.id)，title，created_date，count (列出体验的用户数)

如果一个体验获得了10个列表，那么结果应该包括对应于它获得2个和5个列表的时间的记录。

我使用这个查询来获得所需的结果，并寻找一个更好的“查询”。

SELECT e.id, e.title, ue_res.created_date, ue_res.count FROM
experience e INNER JOIN
(
    SELECT ue.experience_id, (
        SELECT COUNT(uee.id) FROM user_experience uee WHERE uee.experience_id = ue.experience_id AND uee.created_date <= ue.created_date
    ) AS `count`, ue.created_date FROM user_experience ue
) ue_res
ON e.id = ue_res.experience_id
WHERE ue_res.uecount IN (2,5,10)
ORDER BY e.id,ue_res.created_date ASC 

我不能为成就创建另一个表，因为里程碑可以随时更改，所以我只查找一个查询。

下面是一些示例数据

体验示例数据：

1   |   Bunjee jump from Eifel Tower
2   |   Surf in Rio
3   |   Fly over Grand Canyon
4   |   Go to a Mexican resturant

User_experience的示例数据：

1   |   1   |   2013-03-01
2   |   4   |   2013-03-02
3   |   1   |   2013-03-03
4   |   3   |   2013-03-04
5   |   2   |   2013-03-05
6   |   3   |   2013-03-06
7   |   4   |   2013-03-07
8   |   1   |   2013-03-08
9   |   1   |   2013-03-09
10  |   1   |   2013-03-10
11  |   3   |   2013-03-11
12  |   2   |   2013-03-12
13  |   2   |   2013-03-13

示例数据的结果：

1   |   Bunjee jump from Eifel Tower    |   2013-03-03  |   2
3   |   Fly over Grand Canyon           |   2013-03-06  |   2
4   |   Go to a Mexican resturant       |   2013-03-07  |   2
1   |   Bunjee jump from Eifel Tower    |   2013-03-10  |   5
2   |   Surf in Rio                     |   2013-03-12  |   2

提前感谢:)

Answer 1

下面的查询可能会做到这一点：

SET @helper:=0;

SET @oldid:=0;

SELECT ue.experience_id AS id,
       e.title,
       ue.created_date,
       ue.helper        AS milestone
FROM   (SELECT ue.id,
               ue.experience_id,
               ue.created_date,
               CASE @oldid
                 WHEN ue.experience_id THEN ( @helper := @helper + 1 )
                 ELSE ( @helper := 1 )
               end                            AS helper,
               ( @oldid := ue.experience_id ) oldid
        FROM   user_experience ue
        ORDER  BY ue.experience_id ASC,
                  ue.created_date ASC) ue
       JOIN experience e
         ON ( e.id = ue.experience_id )
WHERE  ue.helper IN ( 2, 5, 10 );  

请注意，数据排序是使运行变量发挥作用的原因。