mysql - 如何在不同查询条件下联接同一表的列

我在这里说明我的表名称TB包含三列和类似的数据

Cl1.        Cl2           Cl3
0.             X.              A
0.             Y.               B
0.             Z.               C
1.             X.               a
1.             X.               b
1.             X.               c
1.             Z.               d

And I output like this

Cl1.         Cl2.           Cl3.        No
0.             X.              A.          3
0.             Y.              B.          0
0.             Z.              C.          1

Here no column shows repetition of Cl2. Value when Cl1.=1 with respective value of Cl2. When Cl1. = 0 means for Cl1. = 1 here we can see for Cl.=1 and Cl2.=X it's value is repeated twice for Cl1.=1 similar when no match found for Cl1.=1 and Cl2.=Y no gives 0

I hope i have described my problem

I did many attempts but I didn't get any solution. My problem goes out of mind. My few attempts are like

Select Cl1. ,Cl2. ,Cl3. ,IF(Cl2.=(Select Cl2. From TB where Cl1.=1),Cl2. ,0) as no.
From TB
where Cl2.=0

我也尝试对同一张表使用join type语句，但这也无济于事。
请大家帮我。如果我在描述我的问题时犯了任何错误，那么我是堆栈溢出的新手，对不起

最佳答案

试试这个，@ Rajesh

SELECT
    t1.cl1,
    t1.cl2,
    t1.cl3,
    IFNULL(cnt, 0) as No
FROM
(
    SELECT cl1, cl2, cl3 FROM tb WHERE cl1=0
) AS t1
LEFT OUTER JOIN
(
    SELECT cl2, COUNT(cl2) AS cnt FROM tb WHERE cl1 <> 0 GROUP BY cl2
) AS t2
ON t1.cl2=t2.cl2

关于mysql - 如何在不同查询条件下联接同一表的列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/54313483/