我有这三个表:
create table Nation ("nationkey" integer,
"name" text,
"regionkey" integer,
"comment" text,
"null" text,
foreign key (regionkey) references Region);
create table Supplier ("suppkey" integer,
"name" text,
"address" text,
"nationkey" integer,
"phone" text,
"acctbal" real,
"comment" text,
"null" text,
foreign key (nationkey) references Nation);
create table Customer ("custkey" integer,
"name" text,
"address" text,
"nationkey" integer,
"phone" text,
"acctbal" real,
"mktsegment" text,
"comment" text,
"null" text,
foreign key (nationkey) references Nation);
我必须编写一个 sql 查询,该查询返回客户多于供应商的国家/地区的名称。查询需要位于 Sqlite3 中。我真的是SQL的新手,不知道该怎么做。
对于特定的国家/地区密钥,您可以使用 COUNT 获取相应客户的数量:
SELECT COUNT(*)
FROM Customer
WHERE nationkey = ?
供应商也是如此。
然后,可以将这些 COUNT 查询用作相关子查询,以比较每个Nation
记录的这些值:
SELECT name
FROM Nation
WHERE (SELECT COUNT(*)
FROM Customer
WHERE nationkey = Nation.nationkey) >
(SELECT COUNT(*)
FROM Supplier
WHERE nationkey = Nation.nationkey)
另一种
使用显式JOIN
s的可能解决方案:
SELECT n.name
FROM Nation n
JOIN Customer c ON n.nationkey = c.nationkey
JOIN Supplier s ON n.nationkey = s.nationkey
GROUP BY n.name
HAVING COUNT(c.nationkey) > COUNT(s.nationkey)