旋转概念

发布于 2024-07-16 13:35:57 字数 481 浏览 6 评论 0原文

Hiii，

我的数据库设计如下：

Table File (FileID, Name, Details)

Table Attributes (AttID, AttName, AttType)

Table AttValues (FileID, AttID, AttValue)

直到运行时，才知道文件中有多少个属性以及名称。

我想在前端插入后以如下方式显示：

FileID, FileName, Details, (Rows of Attribute Table as Column here).

即，任何人都可以给我提供一段 Java 或 MySQL 代码来实现这个透视结果。

非常感谢您抽出宝贵的时间。

或者有没有其他更好的方式来存储数据，以便我可以轻松获得想要的结果。

原文

Hiii,

I have a Database design as:

Table File (FileID, Name, Details)

Table Attributes (AttID, AttName, AttType)

Table AttValues (FileID, AttID, AttValue)

Till the runtime, it is not known how many Attributes are in a File and of what names.

And I want to display after insertion at Frontend in a manner like like:

FileID, FileName, Details, (Rows of Attribute Table as Column here).

i.e.,
Can anybody provide me a piece of code in Java or in MySQL to achieve this Pivoting Result.

Highly thanks full for your precious time.

Or is there any other better way to store data, So that I can get the desired result easily.

分享到QQ

分享到微博

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

发布评论

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

昔梦 2024-07-23 13:35:57

这需要两次查询。首先选择文件：

SELECT * FROM File WHERE (...)

然后，获取属性：

SELECT *
FROM AttValues
     JOIN Attributes ON (Attributes.AttId = AttValues.AttId)
WHERE FileId = $id

后一个查询将为每个属性提供一行，您可以通过编程方式对其进行透视显示在您的前端：

foreach(row in result) {
    table.AddColumn(Header = row['AttName'], Value = row['AttValue']);
}

根据需要适应您的本地编程环境。

当然，这仅适用于单个 File 或具有相同属性的 File。如果您想显示具有不同 Attributes 的多个文件，您可以预取所有 AttName：

SELECT Attributes.AttId, Attributes.AttName
FROM Attributes 
     JOIN AttValues ON (Attributes.AttId = AttValues.AttId)
WHERE FileId IN ( $list_of_ids )

然后像这样加载值：

SELECT *
    FROM AttValues
WHERE FileId IN ( $list_of_ids )

并使用本地关联数组从 进行映射>AttId 到列索引。

作为最终的优化，您可以将最后两个查询合并到一个 OUTER JOIN 中，以避免第三次往返。虽然这可能会增加传输的数据量，但如果您的类库支持命名列，它也会使填充表变得更容易。

This requires two queries. First select the File:

SELECT * FROM File WHERE (...)

Then, fetch the Attributes:

SELECT *
FROM AttValues
     JOIN Attributes ON (Attributes.AttId = AttValues.AttId)
WHERE FileId = $id

The latter query will provide you with one row per Attribute, which you can programmatically pivot for display on your frontend:

foreach(row in result) {
    table.AddColumn(Header = row['AttName'], Value = row['AttValue']);
}

Adapt to your local programming environment as needed.

Of course, this only works for a single File or Files with the same attributes. If you want to display multiple files with different Attributes you can instead prefetch all AttNames:

SELECT Attributes.AttId, Attributes.AttName
FROM Attributes 
     JOIN AttValues ON (Attributes.AttId = AttValues.AttId)
WHERE FileId IN ( $list_of_ids )

Then load the values like this:

SELECT *
    FROM AttValues
WHERE FileId IN ( $list_of_ids )

and use a local associative array to map from AttIds to column indexes.

As a final optimisation, you can combine the last two queries into an OUTER JOIN to avoid the third round trip. While this will probably increase the amount of data transferred, it also makes filling the table easier, if your class library supports named columns.

回复收藏 0 原文