MySQL 之開放架構

神諭丶發表於2014-08-10
MySql架構
此處用到了Pivoting技術
注：Pivoting技術是指一種可以把行轉換為列的技術，在Pivoting的執行過程中可能會用到聚合。
這裡討論的都是靜態Pivoting查詢，即使用者需要提前知道旋轉的屬性列的值，對於動態Pivoting，需要動態地構造查詢字串。


若想對這種架構設計的表進行新增、修改或者刪除表和列時，只需要通過insert、update、delete來操作便可完成邏輯架構的更改
但是通過這種方法，可能無法保證其他RDBMS的特性無法使用，比如完整性約束、SQL優化等


首先建立這樣的一個表
  1. create table open_schema(
  2. id int,
  3. attribute varchar(10),
  4. value varchar(20),
  5. primary key(id,attribute));







插入以下測試資料


  1. insert into open_schema select 1,'attr1','110930001';
  2. insert into open_schema select 2,'attr1','110930002';
  3. insert into open_schema select 3,'attr1','110930003';
  4. insert into open_schema select 1,'attr2','AAA';
  5. insert into open_schema select 2,'attr2','BBB';
  6. insert into open_schema select 3,'attr2','CCC';
  7. insert into open_schema select 1,'attr3','M';
  8. insert into open_schema select 2,'attr3','M';
  9. insert into open_schema select 3,'attr3','F';
  10. insert into open_schema select 1,'attr4','1990-01-01';
  11. insert into open_schema select 2,'attr4','1990-02-01';
  12. insert into open_schema select 3,'attr4','1990-03-01';
  13. insert into open_schema select 1,'attr5','18600000000';
  14. insert into open_schema select 2,'attr5','18611111111';
  15. insert into open_schema select 3,'attr5','18622222222';


開放架構表open_schema的內容為：（通過select *查詢的結果）





如果要查詢得出以下結果，需要用到Pivoting技術（需要和聚合一起用）
首先要確定結果的行數和表中行數的關係，對於此表，可以通過id來獲得分組：





解決方案：

  1. select id,  max(case when attribute='attr1' then value end) as sno,
  2.             max(case when attribute='attr2' then value end) as name,
  3.             max(case when attribute='attr3' then value end) as sex,
  4.             max(case when attribute='attr4' then value end) as birthdate,
  5.             max(case when attribute='attr5' then value end) as tel
  6. from open_schema
  7. group by id;



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29773961/viewspace-1248570/，如需轉載，請註明出處，否則將追究法律責任。

相關文章