mysql - 为 mysql 中的每个不同值的每个数据类型选择几个最大类型

Question

userid      data_type,          timespentaday
 1       League of Legends          500
 1       Hearthstone                1500
 1       Hearthstone                1400
 2       World of Warcraft          1200
 1       Dota 2                     100
 2       Final Fantasy              500
 1       Dota 2                     700

鉴于此数据。我想查询每个用户在每个上花费的最多时间。

期望的输出:

User    League Of Legends    Hearthstone   World of Warcraft     Dota 2    
 1           500                1500             0                  700
 2           0                  0                1200               0

我试过类似的东西

SELECT t1.* FROM user_info GROUP BY userid JOIN(
 SELECT(
         (SELECT max(timespentaday) where data_type='League of Legends'),
          (SELECT max(timespentaday) where data_type='Hearhstone'),
          (SELECT max(timespentaday) where data_type='Dota 2)'
FROM socialcount AS t2
) as t2
ON t1.userid = t2.userid

Answer 1

基本上要做到这一点，你需要每组最大的 n.. 有一篇很好的文章，但要点在 mysql 中，你必须使用变量来接近这个.. 特别是在表上做一个枢轴(一个假的枢轴，因为 MySQL 没有原生支持)。

SELECT userid,
    MAX(CASE WHEN data_type = "League of Legends" THEN timespentaday ELSE 0 END) as "League of Legends",
    MAX(CASE WHEN data_type = "Hearthstone" THEN timespentaday ELSE 0 END) as "Hearthstone",
    MAX(CASE WHEN data_type = "Dota 2" THEN timespentaday ELSE 0 END) as "Dota 2",
    MAX(CASE WHEN data_type = "World of Warcraft" THEN timespentaday ELSE 0 END) as "World of Warcraft",
    MAX(CASE WHEN data_type = "Final Fantasy" THEN timespentaday ELSE 0 END) as "Final Fantasy"
FROM
(   SELECT *, @A := if(@B = userid, if(@C = data_type, @A + 1, 1), 1) as count_to_use, @B := userid, @C := data_type
    FROM
    (   SELECT userid, timespentaday, data_type
        FROM gamers
        CROSS JOIN(SELECT @A := 0, @B := 0, @C := '') temp
        ORDER BY userid ASC, data_type ASC, timespentaday DESC
    ) t
    HAVING count_to_use = 1
)t1
GROUP BY userid

DEMO

注意:

MySQL DOCS关于使用用户定义变量的警告非常清楚:

As a general rule, you should never assign a value to a user variableand read the value within the same statement. You might get theresults you expect, but this is not guaranteed. The order ofevaluation for expressions involving user variables is undefined andmay change based on the elements contained within a given statement;in addition, this order is not guaranteed to be the same betweenreleases of the MySQL Server. In SELECT @a, @a:=@a+1, ..., you mightthink that MySQL will evaluate @a first and then do an assignmentsecond. However, changing the statement (for example, by adding aGROUP BY, HAVING, or ORDER BY clause) may cause MySQL to select anexecution plan with a different order of evaluation.