[MSSQL] 資料行塞入A,B,C的值，沒有正規化作業的SQL查詢

2011-07-21

6318
0
SQL Server

[MSSQL] 資料行塞入A,B,C此種沒有正規化作業的SQL查詢

自從出社會後，很少碰到這種一點正規化都沒有的資料庫規劃

正好MSDN論壇上的討論算完整

這邊當然也要紀錄一下，供未來自己有個前車之鑑

發問者有兩個Table

裡面資料是這樣(上方A Table，下方B Table)

A Table和B Table的關聯為，A的產品編號=B的組成

發問者想要撈成以下的資料集

B Table的”組成”欄位沒有經過正規化，所以要Join 根本不可能

以下是我的解法：

/*這邊都是定義物件和塞資料*/
Create Table A
(
產品編號 int,
名稱 varchar(50),
價錢 int NULL 
)
Go
Insert into A values (1,'西瓜',10),(2,'蘋果',20),(3,'鳳梨',30),(4,'木瓜',40)

Create Table B
(
果汁代號 varchar(50),
組成 varchar(50)
)
Go
Insert into B values ('A','1,2'),('B','2,3'),('C','1,2,3'),('D','3,4')

重點是這個處理未正規化資料的邏輯

/*邏輯Start*/
Declare @t Table
(
果汁代號 varchar(50),
價錢 int
)
Declare @果汁代號 varchar(50)
Declare @組成 varchar(50)
Declare myCursor Cursor
 for Select 果汁代號,組成 from B

Open myCursor
 Fetch Next From myCursor into @果汁代號,@組成

 While(@@FETCH_STATUS=0)
 Begin
	 Declare @價錢 int
	 Select @價錢=Sum(價錢) from A Where 產品編號 in (Select * from dbo.udf_Split(@組成,','))
                 /*把@組成字串分割成只有一個欄位Table*/ 
                 /*這邊用到的dbo.udf_Split函數請參考此：http://www.dotblogs.com.tw/shadow/archive/2011/07/02/30968.aspx*/
	 Insert into @t values (@果汁代號,@價錢)
 Fetch Next From myCursor into @果汁代號,@組成
 End/*結束While*/

close myCursor
deallocate myCursor
/*查詢結果集*/
Select * from @t

如果之後有相同查詢需求的話，大概也只能把這程式包成Stored Procedure來Reuse

接著來看經過正規化的資料

 
/*這邊都是定義物件和塞資料*/
Create Table A
(
產品編號 int,
名稱 varchar(50),
價錢 int NULL
)
Go
Insert into A values (1,'西瓜',10),(2,'蘋果',20),(3,'鳳梨',30),(4,'木瓜',40)

Create Table B
(
果汁代號 varchar(50),
組成 varchar(50)
)
Go
Insert into B values ('A','1'),('A','2'),('B','2'),('B','3')
          ,('C','1'),('C','2'),('C','3'),('D','3'),('D','4')/*正確的塞資料方式*/
          
Select * from A
Select * from B

查詢結果：

B Table經過正規化

再來看SQL 查詢語法如何下：

Select B.果汁代號,SUM(A.價錢) AS 價錢 from A Inner Join B
on A.產品編號=B.組成
Group by B.果汁代號
Order by B.果汁代號 ASC

這次就不必使用Cursor，效能變好，語法也變得比較乾淨

查詢結果集：

仍舊到達一開始的需求。

回首頁