SQL在Where子句性能问题中选择Count

由于where子句中的select count（1）语句，我有以下SQL查询执行可怕。任何人都可以建议一种可以加快速度的方法吗？我的想法是，我只想在找到一张发票的地方返回行。

SELECT people.name, people.address 
FROM people 
WHERE ((SELECT COUNT(1) FROM invoices WHERE invoices.pid = people.id)=1)

已邀请:

3 个回复

诫商

COUNT（1）是迷信你拥有的是每行人数=一个像行动一样的光标/循环所以，尝试像这样的JOIN

SELECT people.name, people.address 
FROM
   people 
   JOIN
   invoices ON invoices.pid = people.id
GROUP BY
   people.name, people.address 
HAVING
   COUNT(*) = 1

我也希望你有索引，至少在invoices.pid和people.pid，名称，地址上

扦帽次杏

使用JOIN：

SELECT people.name, people.address
FROM people
JOIN invoices ON invoices.pid = people.id
GROUP BY people.name, people.address
HAVING Count(*) = 1

磨标烫徽啪

我认为，加入表格在实践和表现方面可能要好得多。

SELECT people.name, people.address 
FROM people INNER JOIN invoices ON invoices.pid = people.id

由于正在编辑OP而进行编辑：您是否只想要那些只拥有一张发票的人？如果是这样，那么忽略这一点，看看其他答案之一。

要回复问题请先登录或注册

SQL在Where子句性能问题中选择Count

3 个回复

发起人

sql

performance

问题状态

SQL在Where子句性能问题中选择Count

与内容相关的链接

3 个回复

发起人

sql

performance

问题状态