一个查询sql

[gccdchen]

一张表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

[gccdchen]

[Quote=引用 1 楼 的回复:]

那你输出的结果不应该是这个样子啊。
[/Quote]
为什么啊?那里不对啊?

[gccdchen]

补充一下,,要3次或以上才输出..

[孤独加百列]

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) > 2

stu_id	stu_id	Num
chen	zhou	3

[-Tracy-McGrady-]

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 行受影响)

[以学习为目的]

--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 	
*/

[ORAClE SE]

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 行受影响)

[以学习为目的]

--or

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

[wobuainiyes]

[Quote=引用 4 楼 的回复:]

SQL code

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 'ch……
[/Quote]

学习了，谢

[wobuainiyes]

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

[--小F--]

那你输出的结果不应该是这个样子啊。

[DBA_磊仔]

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