一个查询sql

Question

一张表t_stu_course,两个属性 学生id和课程di
t_stu_course
stu_id varchar 学生id
course_id varchar 课程id
 
要求找到所有 同课程3次以上的两个人,结果表示为 idA,idB,c 分别表示学生a的id,学生b的id,两人课程相同的次数
例子,
stu_id course_id
chen   A
chen   B
chen   C
chen   D
zhou   A
zhou   B
zhou   C
zhou   E
hello   A
hello   B
 
 
那么chen和zhou同课次数为3次,hello和chen同课次数为2次,hello和zhou同课次数为2次
输出为
idA     idB     c
chen   zhou  3
 

Answer 1

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')
BEGIN
DROP TABLE tba
END
GO
CREATE TABLE tba
(
stu_id VARCHAR(10),
course_id VARCHAR(10)
)
GO
INSERT INTO tba
SELECT 'chen', 'A' UNION
SELECT 'chen', 'B' UNION
SELECT 'chen', 'C' UNION
SELECT 'chen', 'D' UNION
SELECT 'zhou', 'A' UNION
SELECT 'zhou', 'B' UNION
SELECT 'zhou', 'C' UNION
SELECT 'zhou', 'E' UNION
SELECT 'hello', 'A' UNION
SELECT 'hello', 'B'
GO
SELECT A.stu_id,B.stu_id,COUNT(A.course_id) AS Num
FROM tba AS A INNER JOIN tba AS B ON A.stu_id < B.stu_id AND A.course_id = B.course_id
GROUP BY A.stu_id,B.stu_id 
HAVING COUNT(A.course_id) > 2stu_id stu_id Num
chen zhou 3

Answer 2

create table #t_stu_course (stu_id varchar(10),course_id varchar(10))
insert into #t_stu_course
select 'chen','A' union all
select 'chen','B' union all
select 'chen','C' union all
select 'chen','D' union all
select 'zhou','A' union all
select 'zhou','B' union all
select 'zhou','C' union all
select 'zhou','E' union all
select 'hello','A' union all
select 'hello','B'select a.stu_id,b.stu_id,count(a.course_id) 
from #t_stu_course a inner join #t_stu_course b 
on a.course_id=b.course_id and a.stu_id<b.stu_id 
group by a.stu_id,b.stu_id
having count(a.course_id)>=3--结果
chen zhou 3
--消息
(1 行受影响)

Answer 3

--try
DECLARE @test TABLE (stu_id VARCHAR(10),course_id VARCHAR(10))
INSERT INTO @test
SELECT 'chen', 'A' UNION
SELECT 'chen', 'B' UNION
SELECT 'chen', 'C' UNION
SELECT 'chen', 'D' UNION
SELECT 'zhou', 'A' UNION
SELECT 'zhou', 'B' UNION
SELECT 'zhou', 'C' UNION
SELECT 'zhou', 'E' UNION
SELECT 'hello', 'A' UNION
SELECT 'hello', 'B'SELECT A.stu_id,B.stu_id,COUNT(A.course_id) AS total
FROM @test AS A JOIN @test AS B ON  A.course_id = B.course_id
WHERE A.stu_id<>B.stu_id
GROUP BY A.stu_id,B.stu_id 
HAVING COUNT(A.course_id) > 2--结果集应该是这样吧
/*
stu_id   stu_id  total
zhou     chen    3
chen     zhou    3 
*/

Answer 4

create table #t_stu_course (stu_id varchar(10),course_id varchar(10))
insert into #t_stu_course
select 'chen','A' union all
select 'chen','B' union all
select 'chen','C' union all
select 'chen','D' union all
select 'zhou','A' union all
select 'zhou','B' union all
select 'zhou','C' union all
select 'zhou','E' union all
select 'hello','A' union all
select 'hello','B'select a.stu_id,b.stu_id,count(a.course_id) 
from #t_stu_course a inner join #t_stu_course b 
on a.course_id=b.course_id and a.stu_id<b.stu_id 
group by a.stu_id,b.stu_id
having count(a.course_id)>=3--结果
chen    zhou    3
--消息
(1 行受影响)

Answer 5

--orDECLARE @test TABLE (stu_id VARCHAR(10),course_id VARCHAR(10))INSERT INTO @test
SELECT 'chen', 'A' UNION
SELECT 'chen', 'B' UNION
SELECT 'chen', 'C' UNION
SELECT 'chen', 'D' UNION
SELECT 'zhou', 'A' UNION
SELECT 'zhou', 'B' UNION
SELECT 'zhou', 'C' UNION
SELECT 'zhou', 'E' UNION
SELECT 'hello', 'A' UNION
SELECT 'hello', 'B'SELECT A.stu_id,B.stu_id,COUNT(A.course_id) AS total
FROM @test AS A JOIN @test AS B ON  A.course_id = B.course_id
WHERE A.stu_id<B.stu_id  --此处
GROUP BY A.stu_id,B.stu_id 
HAVING COUNT(A.course_id) > 2

Answer 6

--连接条件 t1.course_id = t2.course_id  会导致重复数据
--t1.stu_id < t2.stu_id ID总有大小之分，这个条件保证了成对的组合按前小后大的顺序只出现1次

Answer 7

create table #ta (stu_id varchar(10), course_id varchar(10))
insert into #ta select 'chen', 'A'
insert into #ta select 'chen', 'B'
insert into #ta select 'chen', 'C'
insert into #ta select 'chen', 'D'
insert into #ta select 'zhou', 'A'
insert into #ta select 'zhou', 'B'
insert into #ta select 'zhou', 'C'
insert into #ta select 'zhou', 'E'
insert into #ta select 'hello', 'A'
insert into #ta select 'hello', 'B'
select a.stu_id,b.stu_id, count(a.course_id) from #ta a,#ta b 
where a.course_id = b.course_id and a.stu_id <> b.stu_id and a.stu_id < b.stu_id
group by a.stu_id,b.stu_id
having count(a.course_id) = 3