Access 2003 嵌套选择痒痒

发布于 2024-10-13 08:22:08 字数 736 浏览 6 评论 0原文

我有一个查询，看起来像：

SELECT *,
       (SELECT Attribute FROM TableOfAttributes WHERE KeyField = MyTable.KeyField AND Type = "A") AS Attribute1,
       (SELECT Attribute FROM TableOfAttributes WHERE KeyField = MyTable.KeyField AND Type = "B") AS Attribute2,
       (SELECT Attribute FROM TableOfAttributes WHERE KeyField = MyTable.KeyField AND Type = "C") AS Attribute3
FROM
       MyTable

确实如此！在 MyTable 中，信息是水平的，但在 TableOfAttributes 中，它是垂直的，我试图找出如何删除这些嵌套查询，因为目前执行时间太长（超过一个小时）。

简而言之：我有一个包含条目的表，每个条目在另一个表中都有属性，每个属性都存储在一个记录中，一个条目有 3 个属性。

我想出现：

[Entry ID] [Entry Something] [Attribute1] [Attribute2] [Attribute3]

你们会如何解决这个问题？

预先感谢

Miloud B.

原文

I've a query that looks like:

SELECT *,
       (SELECT Attribute FROM TableOfAttributes WHERE KeyField = MyTable.KeyField AND Type = "A") AS Attribute1,
       (SELECT Attribute FROM TableOfAttributes WHERE KeyField = MyTable.KeyField AND Type = "B") AS Attribute2,
       (SELECT Attribute FROM TableOfAttributes WHERE KeyField = MyTable.KeyField AND Type = "C") AS Attribute3
FROM
       MyTable

Indeed ! In MyTable information is horizontal, but in TableOfAttributes it's vertical, I'm trying to figure out how to rip off these nested queries because currently this is taking too long to execute (more than an hour).

To sum up in words: I've a table with entries, every entry has attributes in another table, every attribute is stored in one record and an entry has 3 attributes.

I want to show up:

[Entry ID] [Entry Something] [Attribute1] [Attribute2] [Attribute3]

How would you guys solve that ?

Thanks in advance

Miloud B.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

初懵 2024-10-20 08:22:08

您可以只使用每个表的别名来连接表。如果在某些情况下 Att 表之一中不存在匹配项，则您将需要外连接。

SELECT m.*, Att1.Attribute, Att2.Attribute, Att3.Attribute 
FROM MyTable m, TableOfAttributes Att1, TableOfAttributes Att2, TableOfAttributes Att3 
WHERE Att1.KeyField = m.KeyField AND Type = "A" 
and Att2.KeyField = m.KeyField AND Type = "B"
and Att3.KeyField = m.KeyField AND Type = "C"

You might just join the table using an alias for each table. If there are instances where there doesn't exist a match in one of the Att tables, you'll need an outer join.

SELECT m.*, Att1.Attribute, Att2.Attribute, Att3.Attribute 
FROM MyTable m, TableOfAttributes Att1, TableOfAttributes Att2, TableOfAttributes Att3 
WHERE Att1.KeyField = m.KeyField AND Type = "A" 
and Att2.KeyField = m.KeyField AND Type = "B"
and Att3.KeyField = m.KeyField AND Type = "C"

回复收藏 0 原文

怂人 2024-10-20 08:22:08

您还可以仅对 TableOfAttribute 表执行一次 JOIN，然后执行 GROUP BY。

SELECT  [Entry ID], [Entry Something], 
        MIN(CASE WHEN [Type] = 'A' THEN Attribute ELSE NULL END) AS Attribute1,
        MIN(CASE WHEN [Type] = 'B' THEN Attribute ELSE NULL END) AS Attribute2,
        MIN(CASE WHEN [Type] = 'C' THEN Attribute ELSE NULL END) AS Attribute3
FROM MyTable 
LEFT JOIN TableOfAttributes
ON MyTable.KeyField = TableOfAttributes.KeyField
GROUP BY [Entry ID], [Entry Something]

You can also do just one JOIN with your TableOfAttribute table, and perform a GROUP BY.

SELECT  [Entry ID], [Entry Something], 
        MIN(CASE WHEN [Type] = 'A' THEN Attribute ELSE NULL END) AS Attribute1,
        MIN(CASE WHEN [Type] = 'B' THEN Attribute ELSE NULL END) AS Attribute2,
        MIN(CASE WHEN [Type] = 'C' THEN Attribute ELSE NULL END) AS Attribute3
FROM MyTable 
LEFT JOIN TableOfAttributes
ON MyTable.KeyField = TableOfAttributes.KeyField
GROUP BY [Entry ID], [Entry Something]

回复收藏 0 原文

~没有更多了~