我有一个客户表,希望从提交表中找到重复的条目,并将其显示在客户表的新字段中(如最终表中)
客户表
id email
1 abc@gmail.com
3 xyz@gmail.com
4 ymz@gmail.com
提交表
id Submitteduser
1 abc@gmail.com
2 abc@gmail.com
3 xyz@gmail.com
4 ymz@gmail.com
最终表格
id email count
1 abc@gmail.com 2
3 xyz@gmail.com 1
4 ymz@gmail.com 1
帮助我使这个查询
尝试此查询:点击此处查看Fiddle
SELECT a.id, a.email, COUNT(b.Submitteduser) FROM Customer AS a
JOIN Submission AS b
ON a.email = b.Submitteduser
GROUP BY b.Submitteduser
您可以使用以下查询
select Submission.id, Customer.email, count(Customer.*) from Customer inner join Submission on (email = Submitteduser) group by Submission.Submitteduser