Welcome to ZaiNaLe Developer Community-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

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

join - SQL count all that appear in multiple rows

This is a confusing question to ask, so I think it is best to just show an example of what I need. I have a data table that looks like Table A below. It is documenting the classes that students take, so there is a column for the class subject and a column for the student's name. What I need is a table that lists every combination of 2 subjects and the number of students in each combination, like Table B. (i.e. John is the only student in both History and Spanish, John and Sam are both in History and Math etc.) I can get the subject combinations with a cross join, but I don't know how to get the number of students in each combination.

Table A :

| class     | name |
| --------- | ---- |
| Spanish   | John |
| History   | Sam  |
| Math      | Sam  |
| History   | John |
| Math      | John |
| Spanish   | Mark |

Table B:

|class1  | class2  | number of students |
|--------|---------|--------------------|
|Spanish | History | 1                  |
|History | Math    | 2                  |
|Spanish | Math    | 1                  |

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

1 Answer

0 votes
by (71.8m points)

You'd have to create a join for the same table A with two different aliases a1, a2 where they have the same key a2.name=a1.name but you have to make sure that the classes are different a2.class < a1.class. You have to use < when comparing the classes so that you avoid duplicates. Last step would be group the results by class from a1 and a2.

SELECT a1.class, a2.class, COUNT(*)
FROM
A a1
JOIN A a2 ON a2.name=a1.name AND a2.class < a1.class
GROUP BY a1.class, a2.class;

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