mysql - 选择两个表中一对多的关系

a16s
id  pic
1   1.jpg
2   2.jpg
3   3.jpg
4   4.jpg

a16s_like
id  p_id u_id   approve
1   1       2   0
2   1       1   1
3   1       5   1
4   1       6   1
5   1       7   0
6   2       2   0
7   2       3   0
8   2       1   1
9   4       4   0
10  4       3   1
11  4       2   1


$sql="SELECT A.id, A.pic

(select count(*) from a16s_like B where B.p_id = A.id) AS Ashow,
(select count(*) from a16s_like B where B.p_id = A.id and B.approve='0') AS Nshow,
(select count(*) from a16s_like B where B.p_id = A.id and B.approve='1') AS Yshow
FROM a16s A  ORDER BY id DESC";

我用了三次批准柱，能用什么方法雄辩吗？
我的目标是
当u_id=2执行select时，我想得到

id pic    Ashowocunt  approve_0_count  approve_1_count     u_id2_approve
1  1.jpg   5          2                3                   0
2  2.jpg   3          2                1                   0
3  3.jpg   0          0                0                   null
4. 4.jpg   3          0                3                   0

u_id=3

id pic    Ashowocunt  approve_0_count  approve_1_count     u_id3_approve
1  1.jpg   0          0                0                   null
2  2.jpg   0          1                0                   0
3  3.jpg   0          0                0                   null
4. 4.jpg   1          0                1                   1

需要显示变量u_id的所有a.id，就像u_id=2或u_id=3一样（有空）

最佳答案

你可以试试这个。
使用CASE快捷键和FULL OUTER JOIN
但是Mysql不支持FULL OUTER JOIN，所以您需要使用LEFT JOIN和Right JOIN来创建它。

SELECT
    A.id,
    A.PIC,
    SUM(CASE WHEN  B.approve IS NULL THEN 0 ELSE 1 END) AS Ashowocunt,
    SUM(CASE WHEN  B.approve=0 THEN 1 ELSE 0 END) AS Nshow,
    SUM(CASE WHEN  B.approve=1 THEN 1 ELSE 0 END) AS Yshow
FROM
(
  SELECT A.id ID
  FROM  a16s AS A
  LEFT JOIN a16s_like  AS B ON A.ID = B.p_id
  UNION
  SELECT B.p_id ID
  FROM  a16s AS A
  RIGHT JOIN a16s_like  AS B ON A.ID = B.p_id
) AS T
LEFT JOIN a16s AS A ON A.ID = T.ID
LEFT JOIN a16s_like  AS B ON T.ID = B.p_id
GROUP BY A.id,A.PIC

编辑
您可以在a16s_like上使用子查询

SELECT
    A.id,
    A.PIC,
    SUM(CASE WHEN  B.approve IS NULL THEN 0 ELSE 1 END) AS Ashowocunt,
    SUM(CASE WHEN  B.approve=0 THEN 1 ELSE 0 END) AS Nshow,
    SUM(CASE WHEN  B.approve=1 THEN 1 ELSE 0 END) AS Yshow
FROM
(
  SELECT A.id ID
  FROM  a16s AS A
  LEFT JOIN a16s_like  AS B ON A.ID = B.p_id
  UNION
  SELECT B.p_id ID
  FROM  a16s AS A
  RIGHT JOIN a16s_like  AS B ON A.ID = B.p_id
) AS T
LEFT JOIN a16s AS A ON A.ID = T.ID
LEFT JOIN
(
SELECT *
FROM  a16s_like
WHERE u_id=2
)  AS B ON T.ID = B.p_id
GROUP BY A.id,A.PIC

另一种方法是在B.approve=1 AND B.u_id=2快捷键上设置CASE

SUM(CASE WHEN  B.approve=1 AND B.u_id=2 THEN 1 ELSE 0 END) AS Yshow

SQLFiddle

关于mysql - 选择两个表中一对多的关系，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/49546280/

join

mysql - 选择两个表中一对多的关系