在线求sql语句 急
员工表:WS_Employee
ID NAME
1 a君
2 b君
3 c君
4 d君
5 e君
6 f君
计件工资:WS_jjgz
ID 员工ID price
1 1 125
2 2 122
3 3 111
4 1 212
5 1 111
6 2 121
计时工资:WS_jsgz
ID 员工ID price
1 1 125
2 2 122
3 3 111
4 1 212
5 1 111
6 2 121
我想显示效果还是那个
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
应该怎么写sql语句?
[解决办法]
使用SQL语句的联合查询应该可以解决问题
[解决办法]
建议采用存储过程解决这个问题。
[解决办法]
select * from
(select count(price) as price1,员工ID from WS_jjgz group by 员工ID) a,
(select count(price) as price2,员工ID from WS_jsgz group by 员工ID) b,WS_Employee
where a.员工ID=b.员工ID and a.员工ID =WS_Employee.id
[解决办法]
--建立測試環境
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
(
IDInt,
员工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
--結果
/*
IDNAME计件总工资计时总工资总工资
1a君448448896
2b君243243486
3c君111111222
4d君000
5e君000
6f君000
*/
[解决办法]
楼上正解,JF
[解决办法]
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 ry_id, SUM(je) As SUMprice From WS_jjgz Group By ry_id]. AS B ON A.ID = B.ry_id) LEFT JOIN [Select ry_id, SUM(je) As SUMprice From WS_jsgz Group By ry_id]. AS C ON A.ID = C.ry_id;
錯誤在這裡，
[Select ry_id, SUM(je) As SUMprice From WS_jjgz Group By ry_id]
後面要加上一個.
[Select ry_id, SUM(je) As SUMprice From WS_jjgz Group By ry_id].
另外，我開始寫的語句有點錯誤，現在修改了下，你再測試下。
