我需要从设置表中检索所有默认设置,但如果存在 x 字符,还需要获取字符设置。
但是此查询仅检索字符 = 1 的那些设置,而不是用户尚未设置任何人的默认设置。
SELECT `settings`.*, `character_settings`.`value`
FROM (`settings`)
LEFT JOIN `character_settings`
ON `character_settings`.`setting_id` = `settings`.`id`
WHERE `character_settings`.`character_id` = '1'
所以我应该需要这样的东西:
array(
'0' => array('somekey' => 'keyname', 'value' => 'thevalue'),
'1' => array('somekey2' => 'keyname2'),
'2' => array('somekey3' => 'keyname3')
)
其中 key 1 和 2 是 key 0 包含默认值和字符值时的默认值。
where
子句过滤掉 left join
不成功的行。将其移至联接:
SELECT `settings`.*, `character_settings`.`value`
FROM `settings`
LEFT JOIN
`character_settings`
ON `character_settings`.`setting_id` = `settings`.`id`
AND `character_settings`.`character_id` = '1'
在进行 OUTER JOIN(ANSI-89 或 ANSI-92)时,过滤位置很重要,因为 ON
子句中指定的条件在在进行 JOIN 之前应用。 在进行 JOIN 之后应用 WHERE
子句中提供的针对 OUTER JOIN 表的条件。这会产生非常不同的结果集。相比之下,如果在 ON
或 WHERE
子句中提供条件,则 INNER JOIN 无关紧要 - 结果将是相同的。
SELECT s.*,
cs.`value`
FROM SETTINGS s
LEFT JOIN CHARACTER_SETTINGS cs ON cs.setting_id = s.id
AND cs.character_id = 1
如果我正确理解您的问题,如果它们没有与 character_settings 表的连接,或者该连接记录的 character_id = 1,则您需要来自设置数据库的记录。
因此,您应该这样做
SELECT `settings`.*, `character_settings`.`value`
FROM (`settings`)
LEFT OUTER JOIN `character_settings`
ON `character_settings`.`setting_id` = `settings`.`id`
WHERE `character_settings`.`character_id` = '1' OR
`character_settings`.character_id is NULL
您可能会发现使用简单的子查询更容易理解
SELECT `settings`.*, (
SELECT `value` FROM `character_settings`
WHERE `character_settings`.`setting_id` = `settings`.`id`
AND `character_settings`.`character_id` = '1') AS cv_value
FROM `settings`
子查询允许返回 null,因此您不必担心主查询中的 JOIN/WHERE。
有时,这在 MySQL 中运行得更快,但将其与 LEFT JOIN 形式进行比较,看看哪种方式最适合您。
SELECT s.*, c.value
FROM settings s
LEFT JOIN character_settings c ON c.setting_id = s.id AND c.character_id = '1'
对于这个问题,对于许多其他涉及非平凡左连接(例如内连接表上的左连接)的问题,我发现使用 with
子句拆分查询更方便且更具可读性。在你的例子中,
with settings_for_char as (
select setting_id, value from character_settings where character_id = 1
)
select
settings.*,
settings_for_char.value
from
settings
left join settings_for_char on settings_for_char.setting_id = settings.id;
我最终理解最佳答案的方式是意识到(在 Order Of Execution of the SQL query 之后)将 WHERE 子句应用于 joined 表,从而从加入的(或输出)表。但是,将 WHERE 条件移动到 ON 子句会将其应用于在加入之前的各个表。这使左连接能够保留左表中的行,即使这些行的某些列条目(来自右表的条目)不满足 WHERE 条件。
根据 SQL 语句,结果是正确的。左连接返回右表中的所有值,并且只返回左表中的匹配值。
ID 和 NAME 列来自右侧表,因此返回。
得分来自左表,返回 30,因为该值与名称“流”相关。其他名称为 NULL,因为它们与名称“流”无关。
以下将返回您期望的结果:
SELECT a.*, b.Score
FROM @Table1 a
LEFT JOIN @Table2 b
ON a.ID = b.T1_ID
WHERE 1=1
AND a.Name = 'Flow'
SQL 对右侧表应用过滤器。
不定期副业成功案例分享
AND
充当WHERE
子句,然后它仅适用于character_settings
表。我们可以在想要过滤表之前加入它们的情况下使用这样的设置。此外,正如您上面提到的,如果我们使用显式WHERE
子句,过滤器会在表连接后发生。