MySQL 从三个表中获取信息

Question

我是 SQL 的新手，所以请多多包涵。
我正在写一个登录脚本，想从三个表中获取信息。问题是这个查询只从表 3 返回一个值。
请有人给我一些指示。

SELECT
    table1.id,
    table1.username,
    table1.password,
    table1.email, 
    table2.image,   
    table3.intValue,
    table3.textValue,
    table3.dateValue
FROM
    table1
LEFT JOIN
    table2

ON
    table1.id = table2.userId

LEFT JOIN
    table3
ON
    table1.id = table3.userId
        AND columnName='sex' OR columnName='birthdate' OR columnName='realname'

WHERE 
    table1.email = $username
OR 
    table1.username = $username 

columnName='sex' 是一个整数(intValue)
columnName='birthdate' 是一个日期 (dateValue)
columnName='realname' 是一个字符串 (textValue)

谢谢。

Answer 1

这是您的查询(经过格式化以便我可以更好地阅读):

SELECT t1.id, t1.username, t1.password, t1.email, 
       t2.image, t3.intValue, t3.textValue, t3.dateValue
FROM table1 t1 LEFT JOIN
     table2 t2
     ON t1.id = t1.userId LEFT JOIN
     table3 t3
     ON t1.id = t3.userId AND
        columnName='sex' OR columnName='birthdate' OR columnName='realname'
WHERE t1.email = $username OR t1.username = $username ;

一个问题是 table3 上的 OR 条件。这被评估为:

    ON (t1.id = t3.userId AND columnName='sex') OR columnName='birthdate' OR columnName='realname';

SQL 不读心术。它调用优先规则。条件最好表述为:

     ON t1.id = t3.userId AND
        columnName in ('sex', 'birthdate', 'realname');

但是，我认为这不会导致一行的问题。如果有的话，那会使行数成倍增加。

您似乎希望在一行中获取所有值，而您的查询将为 table3 中的每一行返回单独的一行。如果是这样，您应该使用 group by，并进行适当的聚合。最终查询将是:

SELECT t1.id, t1.username, t1.password, t1.email, 
       t2.image,
       max(case when columnName = 'sex' then t3.intValue end) as sex,
       max(case when columnName = 'realname' then t3.textValue end) as realname,
       max(case when columnName = 'birthdate' then t3.dateValue end) as birthdate
FROM table1 t1 LEFT JOIN
     table2 t2
     ON t1.id = t1.userId LEFT JOIN
     table3 t3
     ON t1.id = t3.userId AND
        columnName in ('sex', 'birthdate', 'realname')
WHERE t1.email = $username OR t1.username = $username
GROUP BY t1.id;