我有3个表,值,设置和控件。每个SETTING都有一个CONTROL,但不一定都有VALUE。
VALUES SETTINGS CONTROLS
------ -------- --------
ID ID ID
SettingID SettingName SettingID
Value ProductFamilyID ControlName
OrderID
我已经尝试过加入和合并,但是无法正常工作。我想要的是查询结果如下所示的查询:
SettingName ControlName Value
----------- ----------- -----
Setting1 Control1 Value1
Setting2 Control58 Value22
Setting3 Control22 null
我忘了提一下,我想在何处VALUE.OrderID = '1'
以及何处过滤结果SETTINGS.ProductFamilyID='2'
我一直面临的问题是,有52个设置,但只有45个具有值。因此,在所有左侧联接的情况下,缺少7 SETTING,而我只能看到具有VALUE的45个。
使用UNION时,我有一些接近的地方,但结果分别放在不同的行中(对于CONTROL,是1行,对于VALUE或没有值,是另一行)
SELECT
SETTINGS.ID, CONTROLS.ControlName
FROM
CONTROLS
INNER JOIN
Settings ON CONTROLS.SettingID = SETTINGS.ID
WHERE
SETTINGS.ProductFamilyID = '2'
UNION
SELECT
SETTINGS.ID, VALUES.Value
FROM
VALUES
INNER JOIN
SETTINGS ON VALUES.SettingID = SETTINGS.ID
WHERE
VALUES.OrderID = '1'
结果是:
SettingID ControlName
--------- -----------
1 Control1
1 Value1
2 Control2
2 Value2
3 Control3 <--- Control3 has no value
4 Control4
4 Value4
我想要的是将值逐ControlName
列放在单独的列中。
终于想通了!
SELECT
FIRSTSET.ID, FIRSTSET.SETTINGNAME, FIRSTSET.CONTROLNAME, SECONDSET.VALUE
FROM
(SELECT
SETTINGS.ID AS ID, SETTINGS.SettingName
AS
SETTINGNAME, CONTROLS.ControlName AS CONTROLNAME
FROM
CONTROLS
INNER JOIN
SETTINGS ON CONTROLS.SettingID = Settings.ID
WHERE
SETTINGS.ProductFamilyID = '2')
AS FIRSTSET
LEFT JOIN
(SELECT
SETTINGS.ID
AS ID2, VALUES.Value AS VALUE FROM VALUES
INNER JOIN
SETTINGS ON VALUES.SettingID = SETTINGS.ID
WHERE
VALUES.SalesOrderLineID = '1')
AS SECONDSET
ON FIRSTSET.ID = SECONDSET.ID2
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句