SQL外部连接?
我喜欢这些表:SQL外部连接?
Fields (ID int, Name varchar(20))
ID Name
===============
1 FieldName
2 FeildDesc
Container (ID int, Name varchar(100))
ID Name
====================================
1 C1
2 C2
3 C3
ContainerField (ContainerId int, FieldId int, FieldValue varchar(100))
ContainerId FieldId FieldValue
====================================
1 1 Container1
1 2 Container1 Desc
2 1 Container2
2 2 Container3 Desc
3 1 Container3
我希望有我的结果是这样的:
ContainerId Name Desc
===================================
1 Container1 Container1 Desc
2 Container2 Container2 Desc
3 Container3 NULL
我试图做LEFT OUTER ContainerField和领域,而且没”之间的连接似乎没有工作。如果有人能帮忙,我会很感激。
加入表格两次,一次为名称,一次为描述。
select
c.Id as ContainerId,
cfn.FieldValue as Name,
cfd.FieldValue as Desc
from
Container c
left join ContainerField cfn
on cfn.ContainerId = c.ID and cfn.FieldId = 1
left join ContainerField cfd
on cfd.ContainerId = c.ID and cfd.FieldId = 2
正如您所看到的,不是组或聚合。另外,查询中并不需要Fields tabel本身,不过在数据库中使用Fields tabel本身是很好的,因为它会强制引用完整性(如果您拥有正确的密钥)并提供一种文档。但你也可以使用枚举。
或者,如果您不只有两个字段,但是它们的数量未知,您可能需要考虑pivot tables
。我必须承认,对于这些人我并不是很擅长,特别是在TSQL中,所以我会为您提供一个链接供您探索:http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx或者希望别人能给你一个具体的例子。
你做的似乎只是需要一个left join
:
select c.id, c.name, cf.FieldValue
from Container c join
ContainerField cf
on cf.ContainerId = c.id and cf.FieldId = 2;
当然,在你的问题中的数据是有点误导。我假设name
列意在来自Container
表。
不需要外连接,内连接就可以做到。您也没有使用Container
中的任何内容(如果您想显示容器名称而不是ID,则可以使用它进行内部连接以获取该内容)。
SELECT c.id AS ContainerID,
MAX(CASE WHEN f.name = 'FieldName' THEN c.FieldValue END) AS Name,
MAX(CASE WHEN f.name = 'FieldDesc' THEN c.FieldValue END) AS `Desc`
FROM ContainerField AS c
JOIN Fields AS f ON c.FieldID = f.id
GROUP BY ContainerID
请注意,您在Fields
表有一个拼写错误:FeildsDesc
应该是FieldsDesc
。
这将工作。我不认为像这样“滥用”团队是最干净的方式,但是再次,我已经在SO回答和生产代码中使用了这个,所以我们不要成为伪君子。+1 :-) – GolezTrol 2014-08-27 20:29:40
我觉得这是你所追求的:
SELECT t1.ContainerId, t1.FieldValue, t2.FieldValue
FROM ContainerField t1
LEFT JOIN ContainerField t2 ON t1.ContainerId = t2.ContainerId
AND t1.FieldId = 1 and t2.FieldId = 2
EDIT(SQL Fiddle):
SELECT DISTINCT t1.ContainerId,
(
SELECT t2.FieldValue FROM ContainerField t2
WHERE t2.FieldId = 1 AND t1.ContainerId = t2.ContainerId
) AS [Name],
(
SELECT t3.FieldValue FROM ContainerField t3
WHERE t3.FieldId = 2 AND t1.ContainerId = t3.ContainerId
) AS [Desc]
FROM ContainerField t1
可能会在我的两分钱,以及抛出。这会让你得到你所需要的。这是在Oracle中完成的,但应该也一样。
编辑:SQL Fiddle Demo作为一个工作的例子。
Select * From (
WITH
/* Example Tables.*/
Container As
(
Select 1 as ID, 'C1' as Name From Dual Union All
Select 2 as ID, 'C2' as Name From Dual Union All
Select 3 as ID, 'C3' as Name From Dual
)
,ContainerField as
(
Select 1 as ContainerId, 1 as FieldId, 'Container1 ' as FieldValue From Dual UNION ALL
Select 1 as ContainerId, 2 as FieldId, 'Container1 Desc' as FieldValue From Dual UNION ALL
Select 2 as ContainerId, 1 as FieldId, 'Container2 ' as FieldValue From Dual UNION ALL
Select 2 as ContainerId, 2 as FieldId, 'Container2 Desc' as FieldValue From Dual UNION ALL
Select 3 as ContainerId, 1 as FieldId, 'Container3 ' as FieldValue From Dual
)
/*Code*/
Select
C.ID,
Max(Case When CF.FieldId = 1 Then FieldValue End) as Name,
Max(Case When CF.FieldId = 2 Then FieldValue End) as Descrption
From
Container C
Left Join ContainerField CF on C.ID = CF.ContainerID
Group By
C.ID,
C.Name
Order By
C.ID
)
输出:
CONTAINERID NAME DESCRPTION
1 Container1 Container1 Desc
2 Container2 Container2 Desc
3 Container3
您可能会发现http://sqlfiddle.com/有趣。然后您可以链接到查询。我只是说因为我在这里有很多像你这样的答案。我向你保证这样更简单,看起来更“专业” – SQLMason 2014-08-27 20:18:35
通过使用'CF.ContainerID'你有一点作弊,如果没有匹配的ContainerField作为字段类型'Name'的容器,将会失败。你应该'选择','按组'和'按''C.ID'来代替。或者,如果您假设总是有一个名称字段,那么将第一个联接更改为“内部联接”以使该假设变得清晰(并使查询更加优化)。一旦你解决了这个问题,你的答案基本上和@ Barmar的答案一样。 :) – GolezTrol 2014-08-27 20:25:53
@GolezTrol好抓!我通常以这种方式进行分组,但是我认为我放了'ContainerID',这当然是不可靠的。 – Phillip 2014-08-27 20:42:20
“*未seemt工作*” 是不可接受的错误描述 – 2014-08-27 19:57:13
怎么没工作?你试过的查询是什么? – Barmar 2014-08-27 19:57:14