围绕SQL查询的中期解决方案

本文是小编为大家收集整理的关于围绕SQL查询的中期解决方案的处理/解决方法，可以参考本文帮助大家快速定位并解决问题，中文翻译不准确的可切换到English标签页查看源文。

中文
English

问题描述

EMPLOYEE (fmane, minit, lname, ssn, birthdate, address, sex, salary, superssn, dno)     KEY: ssn
DEPARTMENT (dname, dnumber, mgrssn, mgrstartdate)               KEY: dnumber.
PROJECT  (pname, pnumber, plocation, dnum)                          KEY: pnumber.
WORKS_ON (essn, pno, hours)                                         KEY: (essn, pno)
DEPENDENT  (essn, dependent-name, sex, bdate, relationship)             KEY: (essn, dependent-name)

问的问题是...

给未婚员工的姓氏和SSN 或更多项目.

SELECT e.Lname, e.ssn
FROM Employee AS e
WHERE e.ssn IN (
        SELECT w.essn
        FROM works_on w
        GROUP BY w.essn
        HAVING count(*) >= 2
        )
    AND e.ssn NOT IN (
        SELECT essn
        FROM dependent
        WHERE relationship = 'Spouse'
        );

我的答案是错误的，因为我没有使用e.ssn NOT IN子句.

我的问题是:

1)group by是用什么?严格仅适用于?

2)关于FROM Employee AS e，是AS a错字或命令?

3)having是指吗?可以用其他命令替换吗?

推荐答案

1)是.该组出现在子选择中

(
    select  w.essn
    from   works_on w
    group by w.essn
    having count(*) >= 2
)

，只有在那里有效.

2)" as"创建一个别名，在这种情况下为"员工"" E".这使您可以避免到处写作员工的乏味.某些SQL数据库不需要" AS"，但这通常是一个好主意，因为它使您的意图清晰.

3)" have"就像一个"在小组"中按子句在每个成员上运行的"其中". https://en.wikipedia.org/wiki/wiki/having_%28sql%29

本文地址：https://www.itbaoku.cn/post/597339.html

百度翻译此文有道翻译此文

问题描述

This is the database:

EMPLOYEE (fmane, minit, lname, ssn, birthdate, address, sex, salary, superssn, dno)     KEY: ssn
DEPARTMENT (dname, dnumber, mgrssn, mgrstartdate)               KEY: dnumber.
PROJECT  (pname, pnumber, plocation, dnum)                          KEY: pnumber.
WORKS_ON (essn, pno, hours)                                         KEY: (essn, pno)
DEPENDENT  (essn, dependent-name, sex, bdate, relationship)             KEY: (essn, dependent-name)

The question asked is...

Give the last name and SSN of the unmarried employees who work on two or more projects.

SELECT e.Lname, e.ssn
FROM Employee AS e
WHERE e.ssn IN (
        SELECT w.essn
        FROM works_on w
        GROUP BY w.essn
        HAVING count(*) >= 2
        )
    AND e.ssn NOT IN (
        SELECT essn
        FROM dependent
        WHERE relationship = 'Spouse'
        );

My answer was wrong because I didn't use the e.ssn NOT IN clause.

My questions are:

1) What is the group by used for? strictly for works on only?

2) Regarding FROM Employee AS e, is AS a typo or a command?

3) Does having mean including? Can it be replaced by some other command?

推荐答案

1) Yes. The group by appears in the sub-select

(
    select  w.essn
    from   works_on w
    group by w.essn
    having count(*) >= 2
)

and is only effective there.

2) "as" creates an alias, in this case "e" for "employee". This lets you avoid the tediousness of writing employee everywhere. Some SQL databases don't require the "as" but it's generally a good idea since it makes your intention clear.

3) "Having" is like a "Where" that operates on each member of a group by clause. https://en.wikipedia.org/wiki/Having_%28SQL%29

上一篇：在.dbf文件和Sql Server 2005之间同步数据

下一篇：如何合并两个数据库，数据相同，但PK不同，没有重复的字段？