我只需要根据下面的查询返回两组数据,但我得到了四组
SELECT DISTINCT *,
CASE WHEN likes.face_id = persona.face_id THEN '1' ELSE '0' END liked,
CASE WHEN dislikes.face_id = persona.face_id THEN '1' ELSE '0' END disliked,
CASE WHEN comments.face_id = persona.face_id THEN '1' ELSE '0' END commented
FROM persona, likes, dislikes, comments
RIGHT JOIN tagged ON tagged.phone_id = 'FA' WHERE persona.face_id = tagged.face_id
以下是我从查询的表格
标签表:
phone_id face_id likeCount dislikeCount commentCount FA GA 1 1 1 FA SA 1 0 0
人物角色表:
face_id name age GA Frank 34 SA Mark 24
点赞表:
face_id phone_id GA FA SA FA
厌恶表:
face_id phone_id SA FA
注释表:
face_id phone_id comment GA FA good
我从上面的查询中得到四个值(四组数据),而不是两个值(两组数据)
下面的数据集是在php 中回显时上述查询的结果
"数据":[{"face_id":"GA","likeCount":"1","dislikeCount","commentCount":
{"face_id":"SA","likeCount":"1","dislikeCount","0","commentCount":
{"face_id":"GA","likeCount":"1","dislikeCount","commentCount":
{"face_id":"SA","likeCount":"1","dislikeCount":"0","commentedCount":《0》,"name":"Frank","age":"24","点赞":"一","评论":"0"
一种更通用的方法
SELECT * ,
CASE WHEN likes.face_id = persona.face_id THEN '1' ELSE '0' END liked,
CASE WHEN dislikes.face_id = persona.face_id THEN '1' ELSE '0' END disliked,
CASE WHEN comments.face_id = tagged.face_id THEN '1' ELSE '0' END commented
FROM tagged JOIN persona ON persona.face_id = tagged.face_id
LEFT JOIN likes ON likes.face_id = persona.face_id AND likes.phone_id = 'FA'
LEFT JOIN dislikes ON dislikes.face_id = tagged.face_id AND dislikes.phone_id = 'FA'
LEFT JOIN comments ON comments.face_id = tagged.face_id AND comments.phone_id = 'FA'
WHERE tagged.phone_id = 'FA'
但是有一个问题,face_id都显示为空