mysql查询排序和排序

Question

假设我有一个名为“users”的表，它有 40 行记录，每行都有字段:

id, firstname, group_id, login_count, stay_on_page_count

组有

administrator (1), manager (2), employee (3)

是否可以创建一个查询，以这种方式对行进行排序和排序

    group _id      stay_on_page_count  login_count
    =========      ==================  ===========
    1              100mins             100
    1              90mins              90
    2              100mins             100
    3              100mins             100

    1              80mins              80
    1              70mins              70
    2              90mins              90
    3              90mins              90

    1              60mins              60
    1              50mins              50
    2              80mins              80
    3              80mins              80

    1              40mins              40
    1              30mins              30
    2              70mins              70
    3              70mins              70

基本上我想使用查询结果创建一个 4x4 GridView 。伪代码大概是

SELECT all FROM user table and to group the result in to cluster of 4, 
    while each 4 should have ORDER BY group_id ASC as first priority (1,1,2,3) 
    AND stay_on_page_count ORDER BY DESC as second priority, 
    AND login_count ORDER BY DESC as last or third priority

我不知道伪代码是否足够解释，但这是我唯一能想到的:)

如果可能的话，它会牺牲性能吗？有没有更好的方法来实现这一点？

我正在使用 Mysql 和 PHP (CakePHP 2.x)

谢谢

Answer 1

一种方法(使用 summarised 作为包含问题中列出的汇总值的表格):

select * from
(select s1.*, 
        @rank1:=@rank1+1 as rankcalc,
        floor(@rank1/2) rankgroup,
        @rank1%2 rankingroup
 from (select @rank1:=1) r1
 cross join
 (select * from summarised where group_id=1 order by stay_on_page_count desc) s1
 union all
 select s2.*, 
        @rank2:=@rank2+1 as rankcalc,
        @rank2 rankgroup,
        2 rankingroup
 from (select @rank2:=0) r2
 cross join
 (select * from summarised where group_id=2 order by stay_on_page_count desc) s2
 union all
 select s3.*, 
        @rank3:=@rank3+1 as rankcalc,
        @rank3 rankgroup,
        3 rankingroup
 from (select @rank3:=0) r3
 cross join
 (select * from summarised where group_id=3 order by stay_on_page_count desc) s3
) sq order by rankgroup, rankingroup

SQLFiddle here .

请注意，此解决方案取决于在子查询中指定的顺序中评估的排序，而不是被优化器覆盖 - 这应该适用于当前版本的 MySQL，但可能不适用于 MariaDB(开源MySQL 的分支)或 MySQL 的 future 版本。