我有以下表格:
Name: Posts
Id Creator Title Body Tags Time
1 1 Test Test Test 123456789
和
Name: Action
Id Creator Type Target
1 1 like 1
2 3 like 1
3 2 like 1
4 1 delete 1
我想返回第一个表中的所有值和一个布尔值,说明另一个表中是否有记录WHERE Type='delete'
.
第二个表中的Target
字段对应于第一个表中的Id
。
我有以下查询(不起作用(。首先,它为操作表中的每个相应行返回一个行实例,即使对于"删除"以外的操作也是如此。
SELECT `Posts`.*, IF(`Action`.`Type`='deleted',1,0) AS "Deleted"
FROM `Posts` JOIN
`Action` ON `Action`.`Target` = `Posts`.`Id`
ORDER BY `Id` DESC;
我不希望为Type='like'
或除Type='delete'
之外的任何其他表返回第一个表中行的多个副本。
此示例中我想要的结果:
Id Creator Title Body Tags Time Deleted
1 1 Test Test Test 123456789 1
我希望这是有道理的,对于如此冗长的问题/解释,我深表歉意。
SELECT Posts.*, IF(Action.Id IS NULL,0,1) as Deleted
FROM Posts
LEFT JOIN Action
ON Action.Target = Posts.Id and Type='delete'
如果你不在乎性能
SELECT Posts.*,
exists
(SELECT 1
FROM Action
WHERE Target = Posts.Id
AND TYPE='delete') AS Deleted
FROM Posts
如您所见,您在数据集中使用deleted
delete
SELECT `Posts`.*,
IF(`Action`.`Type`='delete',1,0) AS "Deleted"
FROM `Posts`
JOIN `Action` ON `Action`.`Target` = `Posts`.`Id`
WHERE `Action`.`Type` != 'like'
ORDER BY `Id` DESC;