mysql - 当一张表为空时连接表

Question

我需要加入三个表:User、Notification 和 UserNotification。后者只是 User 和 Notificaion 之间的交叉引用表，其中包含 UserID(fk 到 User)和 LastReadNotificationID(fk 到 Notification)列。表 UserNotification 应包含对 Web 应用程序的用户通知功能中上次读取的通知的引用。因此，如果我们在Notification中有两条ID为1和2的记录，在UserNotification中有一条fk为Notification = 1的记录，则意味着用户尚未阅读我想在下次登录时显示的最后创建的通知。

现在，我需要在登录时从 User 表中选择所有列，并将另一列 (Notify) 添加到结果集中。 Notify 应该是一个 bool 值，在以下情况下应该为 false:

• 通知为空
• 或通知包含 ID = 10 等的记录并且 UserNotification 确实具有相应的外键。

Notify 应该为 true，如果:

• 通知包含一条记录并且UserNotification 为空。
• 或者通知包含 ID = 10 等的记录但是 UserNotification 不有相应的外键。

问题是我无法编写满足上述所有要求的查询。我目前的查询有效，除非通知为空(因此是 UserNotification)。在这种情况下，我的查询返回 Notify = true;

如果尝试了许多不同的方法来解决这个问题(左连接、右连接、if、case when、ifnull 等)，但我陷入困境。请帮忙。

我现在使用的查询:

SELECT ID, FirstName, LastName, Email, Password, Roles, LastLoginTime, LoginCount, Active, 
(SELECT IFNULL((SELECT 0 FROM UserNotification UN, User U 
WHERE UN.UserId = U.ID AND U.Email = :email 
AND UN.LastReadNotificationID <=> (SELECT MAX(ID) FROM Notification WHERE Display = 1)), 1)) AS Notify 
FROM User WHERE Email = :email;

3 个表:

CREATE TABLE `User` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `FirstName` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  `LastName` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  `Email` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  `Password` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
  `Roles` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
  `LastLoginTime` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  `LoginCount` int(11) DEFAULT NULL,
  `Active` bit(1) NOT NULL DEFAULT b'1',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `Email` (`Email`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

CREATE TABLE `UserNotification` (
  `UserID` bigint(20) NOT NULL,
  `LastReadNotificationID` int(11) NOT NULL,
  UNIQUE KEY `UserID_UNIQUE` (`UserID`),
  KEY `fk_UserNotification_Notification_ID` (`LastReadNotificationID`),
  CONSTRAINT `fk_UserNotification_Notification_ID` FOREIGN KEY  (`LastReadNotificationID`) REFERENCES `Notification` (`ID`) ON DELETE CASCADE,
  CONSTRAINT `fk_UserNotification_User_ID` FOREIGN KEY (`UserID`) REFERENCES `User` (`ID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `Notification` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Text` text NOT NULL,
  `Created` timestamp NOT NULL,
  `UserID` bigint(20) NOT NULL,
  `Display` bit(1) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `fk_Notification_User_ID` (`UserID`),
  CONSTRAINT `fk_Notification_User_ID` FOREIGN KEY (`UserID`) REFERENCES `User` (`ID`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

Answer 1

您尝试的问题可能归结为一个基本问题:您希望使用左联接 (A -> B -> C) 将三个表链接在一起，但您还希望能够推断表中是否还有任何其他行不属于连接逻辑和 ID 对的范围。

B 和 C 都可以为空，这就是为什么使用左连接来解决这个问题是很自然的。 (当然，B 的“空”是针对每个用户的。)问题是 B 位于中间，即使 C 不是。但是，当 B 为空时，您无法仅根据连接结果确定有关 C 的任何结论。

select
    u.ID as UserID, FirstName, LastName, Email, Password,
    Roles, LastLoginTime, LoginCount, Active,
    case
        when max_n.IsEmpty = 1 or un.LastReadNotification = max_n.ID then 0
        -- the following is equivalent and eliminates the IsEmpty flag entirely
        -- when coalesce(max_n.ID, 0) = coalesce(un.LastReadNotification, 0) then 1
        else 1 -- isn't it sufficient to just return 1 at this point?
        -- when un.LastReadNotification is null then 1 -- notification wasn't empty btw
        -- when un.LastReadNotification < agg_n.MaxID then 1 -- can't be greater, right?
    end as Notify
from
    User u
    left outer join UserNotification un
        on un.UserID = u.ID
    cross join (
        select
            case when max(ID) is not null then 0 else 1 end as IsEmpty,
            max(ID) as ID
        from Notification
    ) max_n

与交叉连接相比，您可能更喜欢子查询:

select
    u.ID as UserID, FirstName, LastName, Email, Password,
    Roles, LastLoginTime, LoginCount, Active,
    case
        when coalesce((select max(ID) from Notification), 0)
               = coalesce(LastReadNotification, 0) 
        else 1
    end as Notify
from User u left outer join UserNotification un on un.UserID = u.ID

最终，您真正需要知道的是是否存在 ID 大于每个用户上次看到的 ID 的通知。仅获取最高的通知 ID 就足以让您做出全面的决定。

这里还有另一个想法:在用户表中添加初始值为 0 的 LastReadNotification 以及在“空”通知表中添加 ID 为 0 的虚拟行可能会更容易吗？本质上，你根本不需要了解任何有关空性的事情。如果你实现了 0 行，你最终会得到这样的结果:

select
    ID as UserID, FirstName, LastName, Email, Password,
    Roles, LastLoginTime, LoginCount, Active,
    case when exists (
        select 1 
        from Notification n
        where n.ID > u.LastReadNotification
    ) then 1 else 0 end as NotifyA,
    (
        select sign(count(*))
        from Notification n
        where n.ID > u.LastReadNotification
    ) as NotifyB
from User