MySQL - 仅当左表中存在一行时才加入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14182609/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL - JOIN only if a row exists from the left table
提问by Keir Simmons
Here is MySQL:
这是 MySQL：
SELECT a.id,
a.name,
a.n,
a.r,
a.pot,
a.ticket_price,
a.starting_tickets,
a.started,
a.end,
COUNT(b.id) tickets_bought
FROM current_lotteries a
JOIN lottery_tickets b ON b.lid=a.id
WHERE a.cid=1
ORDER BY started DESC LIMIT 1
In the search, if there is no row from abut there are rows in b(i.e COUNT(b.id)is not NULL) then this query returns a row with NULLvalues for afields and whatever the value of COUNT(b.id)as tickets_bought. How do I modify this query so it does not return a row (num_rows = 0) if there is no result in table a?
在搜索中，如果没有行 froma但有行 in b（即COUNT(b.id)不是NULL），则此查询返回一行，其中NULL包含a字段值以及COUNT(b.id)as 的任何值tickets_bought。num_rows = 0如果表中没有结果，如何修改此查询使其不返回行 ( ) a？
A Snap.
一个快照。
回答by Michael Berkowski
Absent a GROUP BYclause, MySQL (which permits this where it would be an error in other RDBMS) is applying the aggregate group over all rows in bwhen it should be grouping them. Add GROUP BY a.id
如果没有GROUP BY子句，MySQL（允许在其他 RDBMS 中出现错误的情况下）在b应该对它们进行分组时将聚合组应用于所有行。添加GROUP BY a.id
SELECT a.id,
a.name,
a.n,
a.r,
a.pot,
a.ticket_price,
a.starting_tickets,
a.started,
a.end,
COUNT(b.id) tickets_bought
FROM current_lotteries a
JOIN lottery_tickets b ON b.lid=a.id
WHERE a.cid=1
GROUP BY a.id
ORDER BY started DESC LIMIT 1
The above will work in MySQL but not elsewhere. A more portable version uses a correlated subquery:
以上将在 MySQL 中有效，但在其他地方无效。更便携的版本使用相关子查询：
SELECT a.id,
a.name,
a.n,
a.r,
a.pot,
a.ticket_price,
a.starting_tickets,
a.started,
a.end,
b.tickets_bought
FROM current_lotteries a
/* More portable to join against a subquery which returns the count per group */
JOIN (
SELECT b.lid, COUNT(*) AS tickets_bought
FROM lottery_tickets
GROUP BY lid
) b ON a.id = b.lid
WHERE a.cid = 1
ORDER BY started DESC LIMIT 1
回答by Saharsh Shah
Try this:
尝试这个：
SELECT a.id, a.name, a.n, a.r, a.pot, a.ticket_price,
a.starting_tickets, a.started, a.end, b.tickets_bought
FROM current_lotteries a
RIGHT JOIN (SELECT b.lid, COUNT(*) AS tickets_bought
FROM lottery_tickets GROUP BY lid ) b ON a.id = b.lid
WHERE a.cid = 1
ORDER BY started DESC
LIMIT 1;
