找学习相同课程的人

oyzhjr · 发表于 2013-12-4 16:59

谢谢版版的回复，代码测试略有异常，请继续努力。
若 zhh同时选了两门课，10,30. ztt选了两门课，10,20.
实际返回结果为zhh,10.

oyzhjr · 发表于 2013-12-5 09:13

wuxiang_123 发表于 2013-12-4 15:17
假设表名称是表.

select * from jobs2 where xm in
(
select xm
-- ,avg(bh) as a_bh, varp(bh) as v_bh
from jobs2 group by xm
having avg(bh)=(select avg(bh) from jobs2 where xm='ztt' )
and varp(bh)=(select varp(bh) from jobs2 where xm='ztt')
)

复制代码

根据你的思路，我修改了一下，假设 jobs2 为表名。

根据数学上的韦达定理，若知道两个未知数的和与积，可以求出这两个未知数，这是一元二次方程的韦达定理，但是在SQL中，没有类似EXCEL 的PRODUCT 函数，可用的聚合函数中，只有用统计函数VAR来求方差。
平均值一样的两组数据，若数据本身不一样，方差也不一样。所以可以通过平均值与方差两个条件进行限定。

期待更好的思路。

wuxiang_123 · 发表于 2013-12-5 10:22

SELECT T2.XM,T2.BH FROM
(SELECT B.XM,SUM(1) AS 次数 FROM 表 AS A,表 AS B
WHERE A.XM='ZTT' AND A.BH=B.BH AND B.XM<>'ZTT'
GROUP BY B.XM
HAVING SUM(1)=(SELECT SUM(1) FROM 表 WHERE XM='ZTT'))T1,
(SELECT XM,BH,DSUM("1","表","XM='"&XM&"'") AS 次数 FROM 表)T2
WHERE T1.XM=T2.XM AND T1.次数=T2.次数

复制代码

wuxiang_123 · 发表于 2013-12-5 10:37

SELECT T4.* FROM
(SELECT B.XM,SUM(1) AS 次数 FROM 表 A,表 B
WHERE A.XM='ZTT' AND A.BH=B.BH AND B.XM<>'ZTT'
GROUP BY B.XM)T1,
(SELECT SUM(1) AS 次数 FROM 表 WHERE XM='ZTT')T2,
(SELECT XM,SUM(1) AS 次数 FROM 表 WHERE XM<>'ZTT' GROUP BY XM)T3,
表 T4
WHERE T3.XM=T4.XM AND T1.次数=T2.次数 AND T3.次数=T2.次数 AND T1.XM=T3.XM

复制代码

wuxiang_123 · 发表于 2013-12-6 09:16

先查出与ZTT有不同课程的人，然后再排除这些人，剩下的就是和ZTT完全一样课程的人。

SELECT T2.* FROM
(SELECT DISTINCT B.XM FROM
(SELECT * FROM 表 WHERE XM='ZTT')A
RIGHT JOIN 表 B
ON A.BH=B.BH
WHERE A.XM IS NULL)T1
RIGHT JOIN 表 T2
ON T1.XM=T2.XM
WHERE T2.XM<>'ZTT' AND T1.XM IS NULL

复制代码

wuxiang_123 · 发表于 2013-12-6 09:18

子查询效率低，不爱用：

SELECT * FROM 表 WHERE XM<>'ZTT' AND XM NOT IN
(SELECT B.XM FROM
(SELECT * FROM 表 WHERE XM='ZTT')A
RIGHT JOIN 表 B
ON A.BH=B.BH
WHERE A.XM IS NULL)

复制代码

oyzhjr · 发表于 2013-12-6 13:08

蜚常好。等下把这道题放在EXCEL中，要求用公式来解决，不知道能不能做竞赛题出。

amazeyeli · 发表于 2013-12-6 16:23

用除法的话,在xm字段上建立索引,not in 换成 not exists,效率不会低

当然也可以直接用差集except操作

wuxiang_123 · 发表于 2013-12-7 09:37

amazeyeli 发表于 2013-12-6 16:23
用除法的话,在xm字段上建立索引,not in 换成 not exists,效率不会低

当然也可以直接用差集except操作

貌似access查询不支持差集except

		自动登录	找回密码
密码			免费注册

[求助] 找学习相同课程的人