--多表关联汇总在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會自動給子查詢前後加上"[]",這時候"]"後面的"."不能刪除,否則會報錯
*/