douang1243
2015-08-12 00:05 阅读 72
已采纳

MySQL SUM,ORDER BY等

I have this table:
Table

What I need: SUM(v+t+p) if m = '7' and SUM(v+t+p) if m = '8', and order it (by subtract (SUM(v+t+p) if m = '8')-SUM(v+t+p) if m = '7').

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    doutang1884 doutang1884 2015-08-12 00:11

    This is a very general form of what you are looking for (probably):

    SELECT [somestuff?] 
    , SUM(IF([condition1], [calculation1], 0) AS X1
    , SUM(IF([condition2], [calculation2], 0) AS X2
    FROM theTable
    WHERE [something]
    GROUP BY [something else]
    ORDER BY X1 - X2
    ;
    

    Several sections are possibly optional for you.

    To be more specific...

    SELECT SUM(IF(m = '8', v+t+p, 0) AS sum8
    , SUM(IF(m = '7', v+t+p, 0) AS sum7
    FROM theTable
    ORDER BY sum8-sum7  // This ORDER BY won't really do anything; 
                        // there is only one row since we didn't 
                        // GROUP BY anything
    ;
    

    Edit: (for your back pocket to save some time, hopefully something useful)

    create table thing
    (   id int auto_increment primary key,
        n int not null,
        m int not null,
        v int not null,
        t int not null,
        p int not null
    );
    
    insert thing(n,m,v,t,p) values (2,8,0,0,0);
    insert thing(n,m,v,t,p) values (14,8,0,0,0);
    insert thing(n,m,v,t,p) values (48,7,123,123,123);
    insert thing(n,m,v,t,p) values (48,8,12,1,2);
    insert thing(n,m,v,t,p) values (390,8,0,0,0);
    

    Edit:

    SELECT n
    , SUM(IF(m = '8', v+t+p, 0) AS sum8
    , SUM(IF(m = '7', v+t+p, 0) AS sum7
    , SUM(IF(m = '8', v+t+p, 0)
      - SUM(IF(m = '7', v+t+p, 0) AS sumDiff
    FROM the_table
    GROUP BY n
    ORDER BY sumDiff
    ;
    
    点赞 评论 复制链接分享

相关推荐