这些查询正确吗？
我有以下表格：
商城：
+-----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------------+------+-----+---------+----------------+
| MallID | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar(45) | NO | | NULL | |
+-----------+----------------------+------+-----+---------+----------------+商店：
+------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------------------+------+-----+---------+----------------+
| StoreID | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| MallID | smallint(5) unsigned | NO | MUL | NULL | |
| Name | varchar(45) | NO | | NULL | |
| Revenue | int(10) | NO | | NULL | |
+------------+----------------------+------+-----+---------+----------------+客户：
+------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------------------+------+-----+---------+----------------+
| CustomerID | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| StoreID | smallint(5) unsigned | NO | MUL | NULL | |
| Name | varchar(45) | NO | | NULL | |
| Age | smallint(3) | NO | | NULL | |
+------------+----------------------+------+-----+---------+----------------+Store.MallID是Mall.MallID的外键，Customer.StoreID是Store.StoreID的外键。
我想选择所有商店总收入超过100000的商场。
select * from Mall where 100000 < ( select sum(Revenue) from Store where Mall.MallID = Store.MallID);
我想选择没有顾客的购物中心的名称。
select Name from Mall where 0 = ( select count(*) from Customer, Store where Mall.MallID = Store.MallID and Store.StoreID = Customer.StoreID);
这些查询正确吗？
回答 4
Stack Overflow用户
发布于 2013-09-18 11:30:56
您的查询按您的预期执行。我将使用ANSI标准join语法编写第二篇文章，并鼓励您使用缩写作为表别名：
select Name
from Mall m
where 0 = (select count(*)
from Customer c join
Store s
on Mall.MallID = Store.MallID
where Store.StoreID = Customer.StoreID
);另一种方法是将其作为一个单一的连接和聚合来完成：
select m.Name
from Mall m left outer join
Store s
on s.MallID = m.MallID left outer join
Customer c
on s.StoredId = c.StoreId
group by m.Name
having count(c.CustomerId) = 0;在MySQL中，我会阻止您在子查询中进行聚合，然后加入它。尽管是一个非常好的SQL解决方案，但MySQL实际上为此类子查询创建了派生表，这有时会对性能产生负面影响。
Stack Overflow用户
发布于 2013-09-18 11:18:26
它们是正确的，但是有更好的方法(IMHO)来编写它们：
SELECT m.*
FROM Mall m
JOIN (SELECT MallID, SUM(Revenue) totalRev
FROM Store
GROUP BY MallID
HAVING totalRev > 100000) s
ON s.MallID = m.MallID
SELECT m.Name
FROM Mall m
LEFT JOIN (SELECT DISTINCT s.MallID
FROM Customer c
JOIN Store s
ON c.StoreID = s.StoreID) s
ON m.MallID = s.MallID
WHERE s.MallID IS NULLStack Overflow用户
发布于 2013-09-18 11:33:27
他们是对的，我已经帮你查过了：
http://sqlfiddle.com/#!2/6a496/12
您可以使用一些建议的sql命令，也可以保留您的命令(编写查询的方式太多了!)
1.
SELECT *
FROM Mall
where MallID in (
SELECT MallID
FROM Store
GROUP BY MallID
HAVING SUM(Revenue) > 100000)2.
SELECT Name
FROM Mall
WHERE (SELECT count(*)
FROM Customer, Store
WHERE Mall.MallID = Store.MallID and Store.StoreID = Customer.StoreID
)=0;https://stackoverflow.com/questions/18870503
复制相似问题
腾讯云开发者
