首頁猿問如何透視MySQL實體 - 屬性...

如何透視MySQL實體 - 屬性 - 值模式

MySQL SQL Server

qq_笑_17 2019-08-06 17:34:37

如何透視MySQL實體 - 屬性 - 值模式我需要設計存儲文件所有元數據的表（即文件名，作者，標題，創建日期）和自定義元數據（用戶已添加到文件中，例如CustUseBy，CustSendBy）。無法預先設置自定義元數據字段的數量。實際上，確定在文件中添加了什么和多少自定義標記的唯一方法是檢查表中存在的內容。為了存儲它，我創建了一個基表（具有文件的所有公共元數據），一個Attributes表（包含可以在文件上設置的附加，可選屬性）和一個FileAttributes表（為文件的屬性賦值）。CREAT TABLE FileBase ( id VARCHAR(32) PRIMARY KEY, name VARCHAR(255) UNIQUE NOT NULL, title VARCHAR(255), author VARCHAR(255), created DATETIME NOT NULL,) Engine=InnoDB;CREATE TABLE Attributes ( id VARCHAR(32) PRIMARY KEY, name VARCHAR(255) NOT NULL, type VARCHAR(255) NOT NULL) Engine=InnoDB;CREATE TABLE FileAttributes ( sNo INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, fileId VARCHAR(32) NOT NULL, attributeId VARCHAR(32) NOT NULL, attributeValue VARCHAR(255) NOT NULL, FOREIGN KEY fileId REFERENCES FileBase (id), FOREIGN KEY attributeId REFERENCES Attributes (id) ) Engine=InnoDB;樣本數據：INSERT INTO FileBase(id, title, author, name, created) VALUES('F001', 'Dox', 'vinay', 'story.dox', '2009/01/02 15:04:05'),('F002', 'Excel', 'Ajay', 'data.xls', '2009/02/03 01:02:03');INSERT INTO Attributes(id, name, type) VALUES('A001', 'CustomeAttt1', 'Varchar(40)'),('A002', 'CustomUseDate', 'Datetime');INSERT INTO FileAttributes (fileId, attributeId, attributeValue) VALUES('F001', 'A001', 'Akash'),('F001', 'A002', '2009/03/02');現在問題是我想以這樣的方式顯示數據：FileId, Title, Author, CustomAttri1, CustomAttr2, ...F001 Dox vinay Akash 2009/03/02 ...F002 Excel Ajay 什么查詢會生成此結果？

查看完整描述

3 回答

紫衣仙女

TA貢獻1839條經驗獲得超15個贊

這個問題提到了MySQL，實際上這個DBMS對這類問題有一個特殊的功能：GROUP_CONCAT(expr)。請參閱有關逐個功能的MySQL參考手冊。該功能已在MySQL 4.1版中添加。您將GROUP BY FileID在查詢中使用。

我不確定你希望結果如何。如果你想為每個項目列出每個屬性（即使沒有設置），它將更難。但是，這是我對如何做的建議：

SELECT bt.FileID, Title, Author, 
 GROUP_CONCAT(
  CONCAT_WS(':', at.AttributeName, at.AttributeType, avt.AttributeValue) 
  ORDER BY at.AttributeName SEPARATOR ', ') FROM BaseTable bt JOIN AttributeValueTable avt ON avt.FileID=bt.FileID 
 JOIN AttributeTable at ON avt.AttributeId=at.AttributeId 
GROUP BY bt.FileID;

這為您提供了相同順序的所有屬性，這可能很有用。輸出將如下所示：

'F001', 'Dox', 'vinay', 'CustomAttr1:varchar(40):Akash, CustomUseDate:Datetime:2009/03/02'

這樣，您只需要一個單獨的數據庫查詢，并且輸出很容易解析。如果要將屬性存儲為數據庫中的實際日期時間等，則需要使用動態SQL，但我會保持清晰并將值存儲在varchars中。

反對回復 2019-08-06

嚕嚕噠

TA貢獻1784條經驗獲得超7個贊

這種查詢的一般形式是

SELECT file.*,
   attr1.value AS 'Attribute 1 Name', 
   attr2.value AS 'Attribute 2 Name', 
   ...FROM
   file 
   LEFT JOIN attr AS attr1 
      ON(file.FileId=attr1.FileId and attr1.AttributeId=1)
   LEFT JOIN attr AS attr2 
      ON(file.FileId=attr2.FileId and attr2.AttributeId=2)
   ...

因此，您需要根據所需的屬性動態構建查詢。在php-ish偽代碼中

$cols="file";$joins="";$rows=$db->GetAll("select * from Attributes");foreach($rows as $idx=>$row){
   $alias="attr{$idx}";
   $cols.=", {$alias}.value as '".mysql_escape_string($row['AttributeName'])."'";   
   $joins.="LEFT JOIN attr as {$alias} on ".
       "(file.FileId={$alias}.FileId and ".
       "{$alias}.AttributeId={$row['AttributeId']}) ";}

 $pivotsql="select $cols from file $joins";

反對回復 2019-08-06

萬千封印

TA貢獻1891條經驗獲得超3個贊

如果您正在尋找比group-concat結果更可用（和可加入）的東西，請嘗試以下解決方案。我已經創建了一些與你的例子非常相似的表格，以使其有意義。

這適用于：

你想要一個純SQL解決方案（沒有代碼，沒有循環）

您有一組可預測的屬性（例如，不是動態的）

您可以在需要添加新屬性類型時更新查詢

您希望結果可以是JOINed，UNIONed或嵌套為子選擇

表A（文件）

FileID, Title, Author, CreatedOn

表B（屬性）

AttrID, AttrName, AttrType [not sure how you use type...]

表C（Files_Attributes）

FileID, AttrID, AttrValue

傳統查詢會拉出許多冗余行：

SELECT * FROM

Files F

LEFT JOIN Files_Attributes FA USING (FileID)

LEFT JOIN Attributes A USING (AttributeID);

AttrID FileID標題作者CreatedOn AttrValue AttrName AttrType

50 1 TestFile Joe 2011-01-01 true ReadOnly bool

60 1 TestFile Joe 2011-01-01 xls FileFormat文本

70 1 TestFile Joe 2011-01-01 false私人布爾

80 1 TestFile Joe 2011-01-01 2011-10-03 LastModified日期

60 2 LongNovel Mary 2011-02-01 json FileFormat文本

80 2 LongNovel Mary 2011-02-01 2011-10-04 LastModified日期

70 2 LongNovel Mary 2011-02-01 true私人布爾

50 2 LongNovel Mary 2011-02-01 true ReadOnly bool

50 3 ShortStory Susan 2011-03-01 false ReadOnly bool

60 3 ShortStory Susan 2011-03-01 ascii FileFormat文本

70 3 ShortStory Susan 2011-03-01 false私人布爾

80 3 ShortStory Susan 2011-03-01 2011-10-01 LastModified日期

50 4 ProfitLoss Bill 2011-04-01 false ReadOnly bool

70 4 ProfitLoss Bill 2011-04-01 true Private bool

80 4 ProfitLoss Bill 2011-04-01 2011-10-02 LastModified date

60 4 ProfitLoss Bill 2011-04-01 text FileFormat text

50 5 MonthlyBudget George 2011-05-01 false ReadOnly bool

60 5 MonthlyBudget George 2011-05-01二進制文件格式文本

70 5 MonthlyBudget George 2011-05-01 false私人布爾

80 5 MonthlyBudget George 2011-05-01 2011-10-20 LastModified日期

這個合并查詢（使用MAX的方法）可以合并行：

SELECT

F.*,

MAX( IF(A.AttrName = 'ReadOnly', FA.AttrValue, NULL) ) as 'ReadOnly',

MAX( IF(A.AttrName = 'FileFormat', FA.AttrValue, NULL) ) as 'FileFormat',

MAX( IF(A.AttrName = 'Private', FA.AttrValue, NULL) ) as 'Private',

MAX( IF(A.AttrName = 'LastModified', FA.AttrValue, NULL) ) as 'LastModified'

FROM

Files F

LEFT JOIN Files_Attributes FA USING (FileID)

LEFT JOIN Attributes A USING (AttributeID)

GROUP BY

F.FileID;

FileID標題作者CreatedOn ReadOnly FileFormat Private LastModified

1 TestFile Joe 2011-01-01 true xls false 2011-10-03

2 LongNovel Mary 2011-02-01 true json true 2011-10-04

3 ShortStory Susan 2011-03-01 false ascii false 2011-10-01

4 ProfitLoss Bill 2011-04-01 false text true 2011-10-02

5 MonthlyBudget George 2011-05-01 false binary false 2011-10-20

反對回復 2019-08-06

3 回答
0 關注
778 瀏覽

關注

添加回答

舉報

0/150

提交

取消

亚洲在线久爱草,狠狠天天香蕉网,天天搞日日干久草,伊人亚洲日本欧美

熱搜

最近搜索清空

如何透視MySQL實體 - 屬性 - 值模式

如何透視MySQL實體 - 屬性 - 值模式

3 回答

添加回答