MYSQL Select As Case when 行数语句

Question

我正在尝试查找每个用户的通知数量，我遇到了一个小问题，我的查询完美地满足了我的需要，然后我稍微改变了我的表格。

工作查询:

$numNotifications = mysql_num_rows(mysql_query("
SELECT  
      N.*, 
      P.*
      FROM 
          notifications N, 
          posts P
      WHERE 
          N.userID='$session' 
      AND 
          (
          N.action='1' OR N.action='2'
          ) 
      AND 
           N.uniqueID=P.id AND  P.state='0'"
));

但是，某些行的 uniqueID 现在有所不同。当 N.aciton 为“1”时，则应将 N.uniqueID 与 P.id 进行比较，但是当 N.action 为“2”时，应将该表中的一行与 P.id 进行比较。

示例查询(应该有效，但无效)

$numNotifications = mysql_num_rows(mysql_query("
SELECT  
      N.*, 
      P.*, 
      C.*, 
     (CASE WHEN (
                  N.action = 2 AND N.state = 0
                 )
            THEN 
                 C.postID ELSE N.uniqueID END
      ) AS postId  
      FROM 
          notifications N, 
          posts P, 
          comments C 
      WHERE 
          N.userID='$session' 
      AND 
          (
          N.action='1' OR N.action='2'
          ) 
      AND 
           postId=P.id AND  P.state='0'"
));

我的 3 个表结构图:

http://i41.tinypic.com/nyzolg.png

Answer 1

给你:)

SELECT
    COUNT(`notif_id`) AS `number_of_new_notifications`
FROM
    (
        SELECT
            `notifications`.`id` AS `notif_id`
        FROM
            `notifications`
        JOIN
            `posts`
        ON
            `notifications`.`uniqueID`=`posts`.`id`
        WHERE
            `notifications`.`userID`='$session'
        AND
            `notifications`.`action`=1
        AND
            `notifications`.`state`=0
        AND
            `posts`.`state`=0
        UNION ALL
        SELECT
            `notifications`.`id` AS `notif_id`
        FROM
            `notifications`
        JOIN
            `comments`
        ON
            `notifications`.`uniqueID`=`comments`.`id`
        JOIN
            `posts`
        ON
            `comments`.`postID`=`posts`.`id`
        WHERE
            `notifications`.`userID`='$session'
        AND
            `notifications`.`action`=2
        AND
            `notifications`.`state`=0
        AND
            `comments`.`state`=0
        AND
            `posts`.`state`=0
    ) AS notification_ids;