多表关联汇总在MS SQL和ACCESS中的写法

--多表关联汇总在MS SQL和ACCESS中的写法

--MS SQL
--建立測試環境
Create Table WS_Employee
(
ID Int,
NAME Varchar(10)
)
Insert Into WS_Employee
Select 1,'a' Union All
Select 2,'b' Union All
Select 3,'c' Union All
Select 4,'d' Union All
Select 5,'e' Union All
Select 6,'f'
Create Table WS_jjgz
(
ID Int,
员工ID Int,
price Int
)
Insert Into WS_jjgz
Select 1,1,125 Union All
Select 2,2,122 Union All
Select 3,3,111 Union All
Select 4,1,212 Union All
Select 5,1,111 Union All
Select 6,2,121
Create Table WS_jsgz
(
ID Int,
员工ID Int,
price Int
)
Insert Into WS_jsgz
Select 1,1,125 Union All
Select 2,2,122 Union All
Select 3,3,111 Union All
Select 4,1,212 Union All
Select 5,1,111 Union All
Select 6,2,121
GO
--測試
Select
 ID,
 NAME,
 IsNull(B.price,0) As 计件总工资,
 IsNull(C.price,0) As 计时总工资,
 IsNull(B.price,0) + IsNull(C.price,0) As 总工资
From
 WS_Employee A
Left Join
 (Select 员工ID, SUM(price) As price From WS_jjgz Group By 员工ID) B
On A.ID = B.员工ID
Left Join
 (Select 员工ID, SUM(price) As price From WS_jsgz Group By 员工ID) C
On A.ID = C.员工ID
--刪除測試環境
Drop table WS_Employee, WS_jjgz, WS_jsgz
--結果
/*
ID NAME 计件总工资 计时总工资 总工资
1 a  448   448   896
2 b  243   243   486
3 c  111   111   222
4 d  0   0   0
5 e  0   0   0
6 f  0   0   0
*/

--ACCESS
/*
SELECT
 ID,
 NAME,
 IIF(IsNull(B.SUMprice), 0, B.SUMprice) AS 计件总工资,
 IIF(IsNull(C.SUMprice), 0, C.SUMprice) AS 计时总工资,
 IIF(IsNull(B.SUMprice), 0, B.SUMprice) + IIF(IsNull(C.SUMprice), 0, C.SUMprice) AS 总工资
FROM
 (WS_Employee AS A
 LEFT JOIN [Select 员工ID, SUM(price) As SUMprice From WS_jjgz Group By 员工ID]. AS B ON A.ID = B.员工ID)
 LEFT JOIN [Select 员工ID, SUM(price) As SUMprice From WS_jsgz Group By 员工ID]. AS C ON A.ID = C.员工ID;
*/

/*
注意的地方
1. MS SQL中的IsNull在ACCESS中用IIF代替，ACCESS中的IsNull只是用來判斷是否為Null
2. ACCESS中多個JOIN，前一個JOIN的子句要用"()"括起來
3. ACCESS會自動給子查詢前後加上"[]",這時候"]"後面的"."不能刪除，否則會報錯
*/ 

Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1524037