C**********a
发帖数: 14 | 1 Consider the following EMPLOYEE table with attributes for employee
id, employee department and employee salary. An employee can belong
to more than one department; therefore, employee id and the department
number together is the key (composite key):
EMPLOYEE(id, dno, sal).
Write SQL statements for the following WITHOUT using any SQL
functions such as MAX, MIN, COUNT, etc. and WITHOUT using
Groupby-Having.
(a) Find employee id's for those employees who have at
least one other employee with the same salary.
(b) Find employee id's for those employees who have the
maximum salary.
Hint: For example, for set A = f3; 1g, compute AA = f(3; 3); (3; 1); (1; 3)
; (1; 1)g.
Select those members of this set with rst item of a member of
the set is less than the second item giving the set f(1; 3)g. Now
project on the rst item giving the set f1g which does not include
the largest item 3. Now use a set di erence to get the largest item.
(c) Find employee id's for those employees who work for at
least all those departments worked by the employee with employee
id=A4321
Hint: Get Cartesian Product of all EMPLOYEE id's and the set
of department numbers for employee id= A4321. Then do set
MINUS from this set the set created by projecting EMPLOYEE
on id and dno (i.e., SELECT id and dno FROM EMPLOYEE) |
a9
发帖数: 21638 | 2 ac是自连接
b是select top 1 order by
【在 C**********a 的大作中提到】
: Consider the following EMPLOYEE table with attributes for employee
: id, employee department and employee salary. An employee can belong
: to more than one department; therefore, employee id and the department
: number together is the key (composite key):
: EMPLOYEE(id, dno, sal).
: Write SQL statements for the following WITHOUT using any SQL
: functions such as MAX, MIN, COUNT, etc. and WITHOUT using
: Groupby-Having.
: (a) Find employee id's for those employees who have at
: least one other employee with the same salary.
|
c*****d
发帖数: 6045 | 3 1.
select e1.id
from employee e1, employee e2
where e1.sal=e2.sal
and (e1.id<>e2.id or e1.dno<>e2.dno) |
c*****d
发帖数: 6045 | 4 2.
select id from employee where id not in
( select e.id from employee e1, employee e2
where e1.sal < e2.sal
) |
c*****d
发帖数: 6045 | 5 3.
没看懂，假如A4321为a,b,c三个部门工作，要找出a，b,c所有的员工id？ |
B*****g
发帖数: 34098 | 6 大牛又来做家庭作业了？
【在 c*****d 的大作中提到】
: 3.
: 没看懂，假如A4321为a,b,c三个部门工作，要找出a，b,c所有的员工id？
|
e****7
发帖数: 4387 | 7
觉得是要找出所有为a,b,c同时工作的
【在 c*****d 的大作中提到】
: 3.
: 没看懂，假如A4321为a,b,c三个部门工作，要找出a，b,c所有的员工id？
|
c*********e
发帖数: 16335 | 8 lol 春假里的家庭作业
【在 B*****g 的大作中提到】
: 大牛又来做家庭作业了？
|
c*****d
发帖数: 6045 | 9 晚上做维护，没事干瞎转转
【在 B*****g 的大作中提到】
: 大牛又来做家庭作业了？
|
n****f
发帖数: 905 | 10 呵呵， 这次学乖了， 不发包子不给做作业。
【在 B*****g 的大作中提到】
: 大牛又来做家庭作业了？
|
|
|
e****7
发帖数: 4387 | 11 --create table #t (
-- ID INT,
-- DNO INT,
-- SAL MONEY
-- )
--INSERT INTO #t VALUES(1,1,1)
--INSERT INTO #t VALUES(1,3,1)
--INSERT INTO #t VALUES(1,5,1)
--INSERT INTO #t VALUES(1,6,1)
--INSERT INTO #t VALUES(2,1,1)
--INSERT INTO #t VALUES(2,2,1)
--INSERT INTO #t VALUES(2,3,1)
--INSERT INTO #t VALUES(2,4,1)
--INSERT INTO #t VALUES(3,2,1)
--INSERT INTO #t VALUES(3,3,1)
--INSERT INTO #t VALUES(3,4,1)
--INSERT INTO #t VALUES(3,5,1)
--INSERT INTO #t VALUES(3,6,1)
--INSERT INTO #t VALUES(4,1,1)
--INSERT INTO #t VALUES(4,3,1)
--INSERT INTO #t VALUES(4,4,1)
--INSERT INTO #t VALUES(5,2,1)
--INSERT INTO #t VALUES(5,4,1)
WITH un_qualified_list as
(
-- Cartesian of Qualified
select ID, DNO from
(
select distinct ID from #t
) a cross join
(
select distinct dno from #t where ID = 5 -- Let's say id = 5 is the
employee to pick
) b
-- Minus Emp List
except
select ID, dno from #t
), un_qualified_employee AS
(
select distinct ID
from un_qualified_list
)
--Result
select distinct ID
from #t emp
except
select ID from un_qualified_list
--employee 5 is included, but we can filter it out if needed. |
e****7
发帖数: 4387 | 12
这个小哥穷，不像上一个，说给，然后就不见了
【在 n****f 的大作中提到】
: 呵呵， 这次学乖了， 不发包子不给做作业。
|
s**********o
发帖数: 14359 | |
a9
发帖数: 21638 | 14 我觉得c是不是应该这样
select distinct e.id from employee e where e.id not in (select a.id from
employee a left outer join (select dno from employee as b where b.id='A4321'
) c on a.dno=c.dno where c.dno is null) and e.id <> 'A4321'
可这也太麻烦了。
【在 C**********a 的大作中提到】
: Consider the following EMPLOYEE table with attributes for employee
: id, employee department and employee salary. An employee can belong
: to more than one department; therefore, employee id and the department
: number together is the key (composite key):
: EMPLOYEE(id, dno, sal).
: Write SQL statements for the following WITHOUT using any SQL
: functions such as MAX, MIN, COUNT, etc. and WITHOUT using
: Groupby-Having.
: (a) Find employee id's for those employees who have at
: least one other employee with the same salary.
|
e****7
发帖数: 4387 | 15
A4321'
套进我的temp table 去跑了一下，啥结果也没有啊
【在 a9 的大作中提到】
: 我觉得c是不是应该这样
: select distinct e.id from employee e where e.id not in (select a.id from
: employee a left outer join (select dno from employee as b where b.id='A4321'
: ) c on a.dno=c.dno where c.dno is null) and e.id <> 'A4321'
: 可这也太麻烦了。
|
a9
发帖数: 21638 | 16 好像缺条件了，呵呵。
又折腾了一下。
select distinct e.id from employee e where e.id not in (select a.id from
employee a left outer join (select dno from employee as b where b.id='A4321'
) c on a.dno=c.dno where c.dno is null and a.dno in (select dno from
employee as b where b.id='A4321')) and e.id <> 'A4321'
行了不？
【在 e****7 的大作中提到】
:
: A4321'
: 套进我的temp table 去跑了一下，啥结果也没有啊
|
e****7
发帖数: 4387 | 17
A4321'
还是不中啊，你套我的sample data 进去试试看。
多了
【在 a9 的大作中提到】
: 好像缺条件了，呵呵。
: 又折腾了一下。
: select distinct e.id from employee e where e.id not in (select a.id from
: employee a left outer join (select dno from employee as b where b.id='A4321'
: ) c on a.dno=c.dno where c.dno is null and a.dno in (select dno from
: employee as b where b.id='A4321')) and e.id <> 'A4321'
: 行了不？
|
B*****g
发帖数: 34098 | 18 C以前问过很多遍了，其实就是比较count（distinct）
【在 e****7 的大作中提到】
:
: A4321'
: 还是不中啊，你套我的sample data 进去试试看。
: 多了
|
B*****g
发帖数: 34098 | 19 哈哈，人家不让用count
【在 B*****g 的大作中提到】
: C以前问过很多遍了，其实就是比较count（distinct）
|
e****7
发帖数: 4387 | 20
哈哈，打回从写
【在 B*****g 的大作中提到】
: 哈哈，人家不让用count
|
|
|
B*****g
发帖数: 34098 | 21 就用你的table，哈哈
WITH t1 AS (
SELECT distinct DNO
from t
WHERE ID = 5),
t2 AS (
SELECT distinct id
from t
WHERE ID <> 5),
t3 AS (
SELECT *
FROM t1, t2)
SELECT * FROM t3 LEFT JOIN yt_ttt t4 ON t3.ID=t4.ID AND t3.DNO= t4.DNO
SELECT DISTINCT t3.ID
FROM t3
WHERE t3.ID NOT IN (SELECT t3.ID FROM t3 LEFT JOIN t t4 ON t3.ID=t4.ID AND
t3.DNO= t4.DNO WHERE t4.ID IS NULL)
【在 e****7 的大作中提到】
:
: 哈哈，打回从写
|
e****7
发帖数: 4387 | 22 得到的答案正确得到的答案正确
去掉倒数第五行，得到2,3，答案正确。
用了left join和 not in其实思路和我用except 是曲异同工的。
【在 B*****g 的大作中提到】
: 就用你的table，哈哈
: WITH t1 AS (
: SELECT distinct DNO
: from t
: WHERE ID = 5),
: t2 AS (
: SELECT distinct id
: from t
: WHERE ID <> 5),
: t3 AS (
|
e****7
发帖数: 4387 | 23 这出戏总算唱完了，大家也到点儿回家做饭了吧：) |
y****w
发帖数: 3747 | 24 我也在干活，也就只有偷眼看下的功夫。看到一片字母就掩面溜走了。
【在 c*****d 的大作中提到】
: 晚上做维护，没事干瞎转转
|
C**********a
发帖数: 14 | |
n******1
发帖数: 3756 | 26 Beijing 很擅长用临时表
【在 B*****g 的大作中提到】
: 就用你的table，哈哈
: WITH t1 AS (
: SELECT distinct DNO
: from t
: WHERE ID = 5),
: t2 AS (
: SELECT distinct id
: from t
: WHERE ID <> 5),
: t3 AS (
|
P********R
发帖数: 1691 | 27 (a) 在MS SQL Server里用这个JOIN的话，得出的结果应该会有重复（duplicate）的情
况，用IN（subquery)可以排除重复。
SELECT e1.id,
e1.sal
FROM employee AS e1
WHERE e1.sal IN
(SELECT e2.sal
FROM employee AS e2
WHERE e2.id <> e1.id)
ORDER BY e1.sal, e1.id;
(b) 用TOP 1, 倒着排序：
SELECT TOP1 WITH TIES
id,
sal
FROM employee
ORDER BY sal DESC, id; |
B*****g
发帖数: 34098 | 28 CTE
【在 n******1 的大作中提到】
: Beijing 很擅长用临时表
|
c*****d
发帖数: 6045 | 29 你们还这么忙？
【在 y****w 的大作中提到】
: 我也在干活，也就只有偷眼看下的功夫。看到一片字母就掩面溜走了。
|
