SQL 查询导致我 sleep 不足

Question

所以我正在为考试复习，并在 SQL 河(或荒地)中撞到了一块大石头

我制作了以下表格并插入了以下数据:

create table Permissions
(
    fileName VARCHAR(40),
    userID VARCHAR (16),
    type VARCHAR(10),
    startdate DATE,
    duration NUMBER (5),
    constraint Pri_key PRIMARY KEY (userID,fileName)
);

create table Files
(
    name VARCHAR(20),
    fsize INT,
    numberofpermissions INT,
    constraints PRI_KEY2 PRIMARY KEY (name)
);

create table Users
(
    id VARCHAR(20),
    password VARCHAR (20),
    constraint Pri_key3 PRIMARY KEY (id)
);

-- after all tables create:
alter table Permissions
    add constraint Forn_key FOREIGN KEY (userID) REFERENCES Users(id)
    INITIALLY DEFERRED DEFERRABLE;
alter table Permissions
    add constraint Forn_key2 FOREIGN KEY (filename) REFERENCES Files(name)
    INITIALLY DEFERRED DEFERRABLE;
insert into Permissions VALUES ('Agenda','Jones','read','19-JAN-10',30);
insert into Permissions VALUES ('Agenda','Chun','read','19-JAN-10',30);
insert into Permissions VALUES ('Agenda','Rashid','write','17-JAN-10',50);
insert into Permissions VALUES ('Finance','Chun','write','05-DEC-09',50);
insert into Permissions VALUES ('AnnualReport','Jones','write','12-DEC-09',50);
insert into Users VALUES ('Jones', 'duck');
insert into Users VALUES ('Chun', 'tiger');
insert into Users VALUES ('Adams', 'shark');
insert into Users VALUES ('Rashid', 'puma');
insert into Files VALUES ('Agenda', 32, 3);
insert into Files VALUES ('FinanceTables',645, 0);
insert into Files VALUES ('Finance', 120, 1);
insert into Files VALUES ('AnnualReport', 1205, 1);
commit;

我现在正在尝试编写一个 SQL 命令来显示每个拥有
总大小超过 50 个文件的权限:用户的
id，用户有权访问的所有文件的总大小，以及
用户的密码。

这是我到目前为止所拥有的，但是当我尝试添加任何内容以获取密码时，SQL+ 发出嘶嘶声，很快我的屏幕上就会出现一个洞!

SELECT permissions.userID, sum(fsize) AS Totalsize
FROM files, permissions
where permissions.filename = files.name
group by permissions.userid
having SUM(fsize) > 50;

Answer 1

在oracle中，您需要通过以下方式指定整个组

SELECT permissions.userID, users.password, sum(fsize) AS Totalsize
FROM files, permissions, users
where permissions.filename = files.name
and users.id = permissions.userID
group by permissions.userid, permissions.password
having SUM(fsize) > 50;

这与可以隐含 group by 的 MySQL 不同，但这更正确。