MySQL-选择3个具有正确左连接语法的表 [英] MySQL - select 3 tables with correct left join syntax
本文介绍了MySQL-选择3个具有正确左连接语法的表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
希望您可以帮助我正确使用SQL查询的语法(使用MySQL 5.5.25).
Hope you can help me with correct syntax of a SQL query (using MySQL 5.5.25).
我有3张桌子:
- 数据
- data_tmp
- 用户
data
表为空-具有自己的结构但没有行
data
table is empty - has it's own structure but no rows
数据:
id | name | who
----------------
data_tmp :
id | cars | who
---------------
1 | lambo| 2
用户
who | name |
------------
2 | john
我的查询是:
SELECT DISTINCT
users.name,
(SELECT count(id) FROM data WHERE who = 1) as number,
data_tmp.cars
FROM
users, data, data_tmp
WHERE
users.who = 2
AND data_tmp.who = 2
AND data.who = 2
这当然会返回一个空结果(因为data
为空,所以没有适合所有参数的行).
This of course returns an empty result (there is no row that suits to all parameters because data
is empty).
我想要实现的是:
users.name | number | data_tmp.cars |
-------------------------------------
john | 0 | lambo |
我确定我必须-以某种方式-使用LEFT JOIN
,但是找不到正确的语法.希望你能帮助我.
I am sure I have to - in some way - use LEFT JOIN
but can't find correct syntax. Hope you can help me.
Kalreg
推荐答案
尝试一下(不使用子查询)
SELECT a.name, b.cars, count(c.id) as number
FROM users a
INNER JOIN data_tmp b
on a.who = b.who
LEFT JOIN data c
on a.who = c.who AND
a.name = c.name
WHERE a.who = 2
GROUP BY a.name, b.cars
这适用于不同的服务器:
this works on different servers:
MSSQL服务器@ SQLFIDDLE
MYSQL @ SQLFIDDLE
MSSQL SERVER @ SQLFIDDLE
MYSQL @ SQLFIDDLE
这篇关于MySQL-选择3个具有正确左连接语法的表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文