如何使用内部联接和计数来防止第二个表上的重复

Question

SELECT T.column_11, 
count(column_11) count 
FROM wp_tablesome_table_4695 T 
JOIN wp_fea_submissions S ON T.column_2 = S.title 
group BY T.column_11
wp_tablesome_table_4695
column 2 | column 11
1        | location 1
2        | location 2
3        | Location 3
wp_fea_submissions
title 
1
1        
2        
3

结果

column 11| count
1        | 2
2        | 1
3        | 1

计数结果也是对重复的S进行计数。标题如何防止对重复进行计数

Answer 1

计数需要在子查询中完成。

SELECT T.column11, 
count(column11) count 
FROM wp_tablesome_table_4695 T 
INNER JOIN  (select distinct title 
from wp_fea_submissions
)  S ON T.column2 = S.title 
group BY T.column11;

或

with cte as ( select distinct title
from wp_fea_submissions
) select wp.column11, count(wp.column11) as cnt
from cte 
inner join wp_tablesome_table_4695 wp on wp.column2=cte.title
group by wp.column11;

https://dbfiddle.uk/iVprcr8z