Welcome toVigges Developer Community-Open, Learning,Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
829 views
in Technique[技术] by (71.8m points)

sql - How to check how many times a record is repeated in different tables

I have two tables here:

Table 1:

process_id customer_id
16         1
21         1
22         1

Table 2:

process_id customer_id
16         1
16         1
22         1

I would like to check how many times each row in table 1 is repeated in table 2.

For example, row 1 in table 1 is repeated 2 times in table 2, row 2 repeated 0 times and row 3 repeated 1 time. I'm not sure how to loop through each row in table 1 and get this result.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

As I understood, this is what you are asking for:

select table1.process_id,table1.customer_id,count(table2.process_id) as table2count
from table1 left outer join table2 on table1.process_id==table2.process_id and table1.customer_id=table2.customer_id
group by table1.process_id,table1.customer_id;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to Vigges Developer Community for programmer and developer-Open, Learning and Share

2.1m questions

2.1m answers

28 comments

37.2k users

...