L*********i
发帖数: 649 | 1 extra Q中，
如何保证 female managers work in EACH and EVERY projects located in Stafford
?
coolbid 前辈：您的解答似乎不能保证以上条件？
我想了好久，还是没想出方法。
例如：如何用MS-SQL 判定 （1，2） 被 （1，2，3） 包括；
但不被 （1， 3） or (1, 4) 包括？
另外：female managers 似乎应该用：
(select distinct E.lname, E.Ssn
from Employee E, Department D
where E.ssn = D.Mgr_ssn
And E.sex='F') fm
您的解答中用的是e2 super_ssn, 我可以理解您的想法。 |
c*****d
发帖数: 6045 | 2 先回答female managers的问题吧
你的sql是对的，我的sql返回的是所有的female supervisors
我昨天没看到department中mgr_ssn这个字段
Stafford
【在 L*********i 的大作中提到】
: extra Q中，
: 如何保证 female managers work in EACH and EVERY projects located in Stafford
: ?
: coolbid 前辈：您的解答似乎不能保证以上条件？
: 我想了好久，还是没想出方法。
: 例如：如何用MS-SQL 判定 （1，2） 被 （1，2，3） 包括；
: 但不被 （1， 3） or (1, 4) 包括？
: 另外：female managers 似乎应该用：
: (select distinct E.lname, E.Ssn
: from Employee E, Department D
|
c*****d
发帖数: 6045 | 3 female managers work in EACH and EVERY projects located in Stafford?
是这么理解吗，比如female manager A works for project I and II located in
Stafford，最后返回的结果是
A I Stafford
A II Stafford
其实思路很简单
在project表里找到stafford的project pnumber
然后到works_on表找对应pno的essn
然后这个essn要满足1.在employee表中是女的 2.在department表中出现的mgr_ssn |
B*****g
发帖数: 34098 | 4 这个估计每个老师留作业都会有这个题
继续授渔：假设在Stafford的project总数是N，那么我们要找的女经理需要满足什么条
件？
Stafford
【在 L*********i 的大作中提到】
: extra Q中，
: 如何保证 female managers work in EACH and EVERY projects located in Stafford
: ?
: coolbid 前辈：您的解答似乎不能保证以上条件？
: 我想了好久，还是没想出方法。
: 例如：如何用MS-SQL 判定 （1，2） 被 （1，2，3） 包括；
: 但不被 （1， 3） or (1, 4) 包括？
: 另外：female managers 似乎应该用：
: (select distinct E.lname, E.Ssn
: from Employee E, Department D
|
L*********i
发帖数: 649 | 5 female manager has to work on total N projects(p1,p2,...pN) which are
Exactly same as the N projects(p1,p2,..pN) located on Stafford (in my case,
they are 10 & 30).
我可以写出sql, 找出女经理work on at least ONE of the projects located on
Stafford,(我感觉coolbid 前辈上面给出的解释似乎从逻辑上也只能满足这个条件？？
？）
我还没想明白的是：如何select the Essn of 女经理 who work on ALL the projects
located on Stafford.
【在 B*****g 的大作中提到】
: 这个估计每个老师留作业都会有这个题
: 继续授渔：假设在Stafford的project总数是N，那么我们要找的女经理需要满足什么条
: 件？
:
: Stafford
|
B*****g
发帖数: 34098 | 6 你自己把自己转进去了，不要在project table太执着，把你回文前9个字念10遍（括号
前）
,
projects
【在 L*********i 的大作中提到】
: female manager has to work on total N projects(p1,p2,...pN) which are
: Exactly same as the N projects(p1,p2,..pN) located on Stafford (in my case,
: they are 10 & 30).
: 我可以写出sql, 找出女经理work on at least ONE of the projects located on
: Stafford,(我感觉coolbid 前辈上面给出的解释似乎从逻辑上也只能满足这个条件？？
: ？）
: 我还没想明白的是：如何select the Essn of 女经理 who work on ALL the projects
: located on Stafford.
|
L*********i
发帖数: 649 | 7 extra Q sql answer by me finally !
--ExtraQ-Lab4
Select distinct E.Lname, E.Ssn
from Employee E, Department D, Works_On W
where E.ssn = D.Mgr_ssn
And E.sex='F'
And E.ssn = W.Essn
And
E.Ssn in
(Select W.Essn
From (Select P.Pnumber
From Project P
Where P.Plocation = 'Stafford') As P_Sd,
Works_On W
Where P_Sd.Pnumber = W.Pno
Group By W.Essn
Having count(*) = (Select count(*)
From Project P
Where P.Plocation = 'Stafford') ) |
B*****g
发帖数: 34098 | 8 1. 能不用IN就别用IN，尽量用join
...
E.Ssn in
....
2. 尽量用join
Select W.Essn
From (Select P.Pnumber
From Project P
Where P.Plocation = 'Stafford') As P_Sd,
Works_On W
Where P_Sd.Pnumber = W.Pno
Group By W.Ess
【在 L*********i 的大作中提到】
: extra Q sql answer by me finally !
: --ExtraQ-Lab4
: Select distinct E.Lname, E.Ssn
: from Employee E, Department D, Works_On W
: where E.ssn = D.Mgr_ssn
: And E.sex='F'
: And E.ssn = W.Essn
: And
: E.Ssn in
: (Select W.Essn
|
L*********i
发帖数: 649 | 9 多谢指点！
您的建议会提高 performance and speed , am I right ?
我回去按您的指教再修改。
老师上课提到过一点，但没细讲。
她说一般情况，无论我们怎么些sql query, 内部都会先优化，再执行。
我知道如果在from 里先用join, 就先强制规定了某些执行过程。
请赐教。多谢。
【在 B*****g 的大作中提到】
: 1. 能不用IN就别用IN，尽量用join
: ...
: E.Ssn in
: ....
: 2. 尽量用join
: Select W.Essn
: From (Select P.Pnumber
: From Project P
: Where P.Plocation = 'Stafford') As P_Sd,
: Works_On W
|
B*****g
发帖数: 34098 | 10 你们老师说的基本是对的，你说的完全不对。现阶段你就记住下面2个sql，第一个比第
二个好:
SELECT * FROM a, b WHERE a.id = b.id
SELECT * FROM a WHERE a.id IN (SELECT b.id FROM b)
【在 L*********i 的大作中提到】
: 多谢指点！
: 您的建议会提高 performance and speed , am I right ?
: 我回去按您的指教再修改。
: 老师上课提到过一点，但没细讲。
: 她说一般情况，无论我们怎么些sql query, 内部都会先优化，再执行。
: 我知道如果在from 里先用join, 就先强制规定了某些执行过程。
: 请赐教。多谢。
|
|
|
L*********i
发帖数: 649 | 11 SELECT *
FROM a inner join b ON a.id=b.id
Is this "better" than the two you just posted or not ?
how do you define "better" ?
Thanks for sharing ! |
B*****g
发帖数: 34098 | 12 it is the same as 1
better的意思就是从此时此刻起，如果能用1，就不要用2，甚至你应该把2忘掉
【在 L*********i 的大作中提到】
: SELECT *
: FROM a inner join b ON a.id=b.id
: Is this "better" than the two you just posted or not ?
: how do you define "better" ?
: Thanks for sharing !
|
y****9
发帖数: 144 | 13 Your (1) and (2) seems not equivalent bcoz in your (1) you will have b's
fields If you mean the following:
(1) select a.* from a, b where a.id = b.id
(2) select a.* FROM a WHERE a.id IN (SELECT b.id FROM b)
I would think (2) could be more effcient, it is a semi-join; considering if
b.id is not unique, in (1) you will have duplicates. So my (1) and (2)
could not equivalent even in terms of result.
I don't think there are any problems to use subquery in terms of IN, NOT IN,
EXISTS, NOT EXISTS. Of course one needs to understnad the semi-join, anti-
join and the possibility of return NULL value in such cases: select b.id
from b
【在 B*****g 的大作中提到】
: 你们老师说的基本是对的，你说的完全不对。现阶段你就记住下面2个sql，第一个比第
: 二个好:
: SELECT * FROM a, b WHERE a.id = b.id
: SELECT * FROM a WHERE a.id IN (SELECT b.id FROM b)
|
B*****g
发帖数: 34098 | 14 NOT EXISTS/NOT IN可以用，EXISTS/IN能不用就不用。至于excution plan，都是cost
based...
if
IN,
【在 y****9 的大作中提到】
: Your (1) and (2) seems not equivalent bcoz in your (1) you will have b's
: fields If you mean the following:
: (1) select a.* from a, b where a.id = b.id
: (2) select a.* FROM a WHERE a.id IN (SELECT b.id FROM b)
: I would think (2) could be more effcient, it is a semi-join; considering if
: b.id is not unique, in (1) you will have duplicates. So my (1) and (2)
: could not equivalent even in terms of result.
: I don't think there are any problems to use subquery in terms of IN, NOT IN,
: EXISTS, NOT EXISTS. Of course one needs to understnad the semi-join, anti-
: join and the possibility of return NULL value in such cases: select b.id
|
w*r
发帖数: 2421 | 15 SELECT MGR_WORK.SUPERSSN
FROM (
SELECT MGRS.SUPERSSN,
COUNT(DISTINCT WORKS_ON.PNO) AS PROJ_CNT
FROM (
SELECT DISTINCT SUPERSSN
FROM EMPLOYEE
WHERE SEX = 'F'
) AS MGRS
INNER JOIN
EMPLOYEE
ON
MGR.SUPER_SSN = EMPLOYEE.EMPLOYEE_SSN
INNER JOIN
WORKS_ON
ON
MGRS.SUPERSSN = WORKS_ON.SSN
INNER JOIN
PROJECT
ON
WORKS_ON.PNO = PROJECT.PNUMBER
AND PROJECT.PLOCATION='STANFORD'
) MGR_WORK
INNER JOIN
(
SELECT COUNT(DISTINCT PNUMBER) AS CNT
FROM PROJECT
WHERE PLOCATION = 'STANFORD'
) AS P_CNT
ON
1 = 1
WHERE MGR_WORK.SUPERSSN = P_CNT,CNT |
