dongmou3615
2010-05-28 10:10
浏览 81
已采纳

在一个查询中计算两个字段的问题

guys i need to count new private messages and old one from a table

so first thing come to mind is using mysql_num_rows and easy thing to do

  // check new pms
  $user_id  = $userinfo['user_id'];
  $sql = "SELECT author_id  FROM bb3privmsgs_to WHERE user_id='$user_id' AND (pm_new='1' OR  pm_unread='1')";
  $result = $db->sql_query($sql) ;
  $new_pms = $db->sql_numrows($result);
  $db->sql_freeresult($result);

  // check old pms
  $sql = "SELECT author_id  FROM bb3privmsgs_to WHERE user_id='$user_id' AND (pm_new='0' OR  pm_unread='0')";
  $result = $db->sql_query($sql) ;
  $old_pms = $db->sql_numrows($result);
  $db->sql_freeresult($result);

but how can i count these two fields just in one statement and shorter lines ?~

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

4条回答 默认 最新

  • dongzha0149 2010-05-28 10:17
    已采纳

    Use this query instead:

    SELECT SUM(CASE WHEN pm_new = '1' OR pm_unread = '1' THEN 1 ELSE 0 END) AS new_pms,
           SUM(CASE WHEN pm_new = '0' OR pm_unread = '0' THEN 1 ELSE 0 END) AS old_pms
      FROM bb3privmsgs_to
     WHERE user_id='$user_id'
    

    Here's a MySQL-specific version that reads more cleanly:

    SELECT COUNT(IF(pm_new = '1' OR pm_unread = '1', 1, NULL)) AS new_pms,
           COUNT(IF(pm_new = '0' OR pm_unread = '0', 1, NULL)) AS old_pms
      FROM bb3privmsgs_to
     WHERE user_id='$user_id'
    
    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • doutan5724 2010-05-28 10:17

    In SQL Server, you can do something like this:

    SELECT 
       SUM(CASE WHEN pm_new='1' OR  pm_unread='1' THEN 1 ELSE 0 END),
       SUM(CASE WHEN pm_new='0' OR  pm_unread='0' THEN 1 ELSE 0 END) 
    FROM 
       bb3privmsgs_to WHERE user_id='$user_id'
    

    I'll suppose you can do about the same thing in mySql, let me get back to you on the details...

    评论
    解决 无用
    打赏 举报
  • dskvfdxgdo2422392 2010-05-28 10:19

    MySQL will cast comparisons to 1 or 0. You can use SUM() to add up the portion of the WHERE clause you were trying to count results for.

    This is a (MySQL specific) shorter alternative to the CASE WHEN examples.

    SELECT 
      SUM(pm_new='1' OR pm_unread='1') as new_pms, 
      SUM(pm_new='0' OR pm_unread='0') as old_pms
    FROM bb3privmsgs_to
    WHERE user_id='$userid'
    
    评论
    解决 无用
    打赏 举报
  • dtxpz8785 2010-05-28 11:09

    As a lazy alternative to some of the other suggestions:

    SELECT SUM(newPMS) AS newPMS,
           SUM(oldPMS) AS oldPMS
      FROM ( SELECT COUNT(author_id) AS newPMS,
                    0 AS oldPMS
               FROM bb3privmsgs_to
              WHERE user_id='$user_id'
                AND (pm_new='1' OR pm_unread='1')
             UNION
             SELECT 0 AS newPMS
                    COUNT(author_id) AS oldPMS
               FROM bb3privmsgs_to
              WHERE user_id='$user_id'
                AND (pm_new='0' OR pm_unread='0')
           )
    
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题