mysql - SQL 脚本未返回正确的表

Question

我有如下表格:

CREATE TABLE SKILL(
sname         VARCHAR(30)     NOT NULL,
    CONSTRAINT SKILL_pkey PRIMARY KEY ( sname ) );


CREATE TABLE APPLICANT(
anumber         DECIMAL(6)      NOT NULL,
fname           VARCHAR(20)     NOT NULL,
lname       VARCHAR(30) NOT NULL,
dob             DATE        NOT NULL,
city        VARCHAR(30) NOT NULL,
state       VARCHAR(20) NOT NULL,
phone       DECIMAL(10) NOT NULL,
fax     DECIMAL(10)     ,
email       VARCHAR(50)     ,
    CONSTRAINT APPLICANT_pkey PRIMARY KEY ( anumber ) );


CREATE TABLE SPOSSESSED(
anumber         DECIMAL(6)  NOT NULL,
sname       VARCHAR(30) NOT NULL,
slevel      DECIMAL(2)  NOT NULL,
    CONSTRAINT SPOSSESSED_pkey PRIMARY KEY ( anumber, sname ), 
    CONSTRAINT SPOSSESSED_fkey1 FOREIGN KEY ( anumber )
                REFERENCES APPLICANT ( anumber )
                ON DELETE CASCADE,
    CONSTRAINT SPOSSESSED_fkey2 FOREIGN KEY ( sname )
                REFERENCES SKILL ( sname ),
    CONSTRAINT SPOSSESSED_check1 CHECK ( slevel IN 
                    ( 1,2,3,4,5,6,7,8,9,10 ) ) );

我被命令:

Create a relational table that contains information about the names of all skills and the largest skill level possessed by an applicant and a number of applicant who possesses a skill at the highest level. Ignore the skills not possessed by any applicant. All data must be loaded into the table by the same SQL statement that creates the table. Enforce the appropriate primary key and referential integrity constraints (if any) after data is loaded.

据我了解，我想出了 2 个单独的脚本来首先实现选择:

SELECT * FROM SKILL;

SELECT MAX(SPOSSESSED.slevel), APPLICANT.anumber
FROM APPLICANT RIGHT OUTER JOIN SPOSSESSED
ON APPLICANT.anumber = SPOSSESSED.anumber
GROUP BY anumber;

不知道我选对了没有？谁能帮忙？越看需求越糊涂。

编辑#1:

示例数据技能:

INSERT INTO SKILL VALUES ( 'C++ programming' );
INSERT INTO SKILL VALUES ( 'C programming' );
INSERT INTO SKILL VALUES ( 'Java programming' );
INSERT INTO SKILL VALUES ( 'SQL programming' );
INSERT INTO SKILL VALUES ( 'driving' );
INSERT INTO SKILL VALUES ( 'painting' );
INSERT INTO SKILL VALUES ( 'cooking' );

申请人:

INSERT INTO APPLICANT VALUES ( 000001, 'Harry', 'Potter', '1980-12-12',  'Perth', 'Western Australia', 645278453, NULL, 'jones@gmail.com' );
INSERT INTO APPLICANT VALUES ( 000002, 'Johnny', 'Walker', '1990-02-13',  'Geelong', 'Victoria', 63569784, 63569785, 'blunder@hotmail.com' );
INSERT INTO APPLICANT VALUES ( 000003, 'Mary', 'Poppins', '1950-01-01',  'Melbourne', 'Victoria', 62389541, NULL, NULL );
INSERT INTO APPLICANT VALUES ( 000004, 'Michael', 'Collins', '1960-05-25',  'Brisbane', 'Queensland', 63336666, NULL, 'mike@hotmail.com');
INSERT INTO APPLICANT VALUES ( 000005, 'Margaret', 'Finch', '1953-12-07',  'Sydney','New South Wales', 64573489, NULL, 'mf@163.com');
INSERT INTO APPLICANT VALUES ( 000006, 'Claudia', 'Kowalewski', '1959-05-03',  'Hobart', 'Tasmania', 64577744, NULL, 'cch@cs.odmg.org');
INSERT INTO APPLICANT VALUES ( 000007, 'James', 'Bond', '1960-06-01','Perth', 'Western Australia', 645278434, NULL, 'james@bigpond.com');

拥有:

INSERT INTO SPOSSESSED VALUES ( 000001, 'Java programming', 9 );
INSERT INTO SPOSSESSED VALUES ( 000001, 'C programming', 4 );
INSERT INTO SPOSSESSED VALUES ( 000001, 'cooking', 9 );
INSERT INTO SPOSSESSED VALUES ( 000002, 'Java programming', 9 );
INSERT INTO SPOSSESSED VALUES ( 000002, 'driving', 9 );
INSERT INTO SPOSSESSED VALUES ( 000003, 'C++ programming', 10 );
INSERT INTO SPOSSESSED VALUES ( 000003, 'Java programming', 9 );
INSERT INTO SPOSSESSED VALUES ( 000003, 'painting', 5 );
INSERT INTO SPOSSESSED VALUES ( 000005, 'SQL programming', 6 );
INSERT INTO SPOSSESSED VALUES ( 000006, 'SQL programming', 8 );
INSERT INTO SPOSSESSED VALUES ( 000007, 'SQL programming', 9 );
INSERT INTO SPOSSESSED VALUES ( 000007, 'cooking', 10 );

我真的不明白我需要做什么，所以我请你看看你是如何解释它的。

但根据我的理解，它应该显示一个表，其中包含 sname、该 sname 的最高级别和该 sname 中最高级别的数字。

这是我能做到的最具体的...

Answer 1

以下查询将创建所有技能的列表，以及每个技能的技能级别最高的申请人。

SELECT
    c.`anumber`,
    a.`sname`
FROM `SPOSSESSED` a
JOIN (
    SELECT 
        `sname`
        MAX(`slevel`) as `slevel`
    FROM `SPOSSESSED`
    GROUP BY `sname`
    ) b
    ON b.`sname` = a.`sname` AND b.`slevel` = a.`slevel`
JOIN `APPLICANT` c
    ON c.`anumber` = a.`anumber`
GROUP BY a.`sname`, c.`anumber`

如果您想要的不是每个技能的最高技能水平的申请人列表，而是每个技能的最高技能水平的申请人的数量，你可以试试这个:

SELECT
    a.`sname`,
    a.`slevel`,
    count(DISTINCT c.`anumber`) as `numapplicants`
FROM `SPOSSESSED` a
JOIN (
    SELECT 
        `sname`
        MAX(`slevel`) as `slevel`
    FROM `SPOSSESSED`
    GROUP BY `sname`
    ) b
    ON b.`sname` = a.`sname` AND b.`slevel` = a.`slevel`
JOIN `APPLICANT` c
    ON c.`anumber` = a.`anumber`
GROUP BY a.`sname`, a.`slevel`

未经测试，可能有错字。