在线求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语句?
[解决办法]
select id,name,sum(WS_jjgz.price) as 计件总工资,sum(WS_jsgz.price) as 计时总工资,sum(sum(WS_jjgz.price)+sum(WS_jsgz.price)) as 总工资
from 数据表
where WS_Employee.id=WS_jjgz.员工id and WS_Employee.id=WS_jsgz.员工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
(
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((SELECT SUM(price) FROM WS_jjgz WHERE 员工ID=A.ID),0) 计件总工资,
ISNULL((SELECT SUM(price) FROM WS_jsgz WHERE 员工ID=A.ID),0) 计时总工资,
ISNULL((SELECT SUM(price) FROM WS_jjgz WHERE 员工ID=A.ID),0)+ISNULL((SELECT SUM(price) FROM WS_jsgz WHERE 员工ID=A.ID),0) 总工资
FROM WS_Employee A
--结果
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
(6 行受影响)
[解决办法]
select *,
计件总工资=isnull((select sum(price) from WS_jjgz where 员工ID=e.id),0),
计时总工资=isnull((select sum(price) from WS_jsgz where 员工ID=e.id),0),
总工资=isnull((select sum(price) from WS_jjgz where 员工ID=e.id)+(select sum(price) from WS_jsgz where 员工ID=e.id),0)
from WS_Employee e
[解决办法]
create table WS_Employee
(
ID varchar(10),
NAME varchar(10)
)
insert into WS_Employee(ID,NAME) values( '1 ', 'a君 ')
insert into WS_Employee(ID,NAME) values( '2 ', 'b君 ')
insert into WS_Employee(ID,NAME) values( '3 ', 'c君 ')
insert into WS_Employee(ID,NAME) values( '4 ' , 'd君 ')
insert into WS_Employee(ID,NAME) values( '5 ', 'e君 ')
insert into WS_Employee(ID,NAME) values( '6 ', 'f君 ')
go
create table WS_jjgz
(
ID varchar(10),
员工ID varchar(10),
price int
)
insert into WS_jjgz(ID,员工ID,price) values( '1 ', '1 ', 125)
insert into WS_jjgz(ID,员工ID,price) values( '2 ', '2 ', 122)
insert into WS_jjgz(ID,员工ID,price) values( '3 ', '3 ', 111)
insert into WS_jjgz(ID,员工ID,price) values( '4 ', '1 ', 212)
insert into WS_jjgz(ID,员工ID,price) values( '5 ', '1 ', 111)
insert into WS_jjgz(ID,员工ID,price) values( '6 ', '2 ', 121)
go
create table WS_jsgz
(
ID varchar(10),
员工ID varchar(10),
price int
)
insert into WS_jsgz(ID,员工ID,price) values( '1 ', '1 ', 125)
insert into WS_jsgz(ID,员工ID,price) values( '2 ', '2 ', 122)
insert into WS_jsgz(ID,员工ID,price) values( '3 ', '3 ', 111)
insert into WS_jsgz(ID,员工ID,price) values( '4 ', '1 ', 212)
insert into WS_jsgz(ID,员工ID,price) values( '5 ', '1 ', 111)
insert into WS_jsgz(ID,员工ID,price) values( '6 ', '2 ', 121)
go
select a.* , isnull(b.计件总工资,0) as 计件总工资 , isnull(c.计时总工资 , 0) as 计时总工资 , isnull(b.计件总工资,0) + isnull(c.计时总工资 , 0) as 总工资
from WS_Employee a
left join (select 员工ID,sum(price) as 计件总工资 from WS_jjgz group by 员工ID) b on a.id = b.员工ID
left join (select 员工ID,sum(price) as 计时总工资 from WS_jsgz group by 员工ID) c on a.id = c.员工ID
drop table WS_Employee,WS_jjgz,WS_jsgz
/*result
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
（所影响的行数为 6 行）
*/
[解决办法]
select A.id,
A.name,
isnull(B.price,0) 计件总工资,
isnull(c.price,0) 计时总工资,
isnull(b.price + c.price,0)总工资
from ws_employee A left join (select 员工Id ,sum(price) price from ws_jjgz group by 员工Id) B
on A.id = B.员工Id
left join (select 员工Id ,sum(price) price from ws_jsgz group by 员工Id) C
on A.id = c.员工Id
[解决办法]
你開始沒說是ACCESS，都以為是SQL的。
ACCESS的是這麼寫
SELECT
ID,
NAME,
IIF(IsNull(B.SUMprice), 0, B.SUMprice) AS 计件总工资,
IIF(IsNull(B.SUMprice), 0, C.SUMprice) AS 计时总工资,
IIF(IsNull(B.SUMprice), 0, B.SUMprice) + IIF(IsNull(B.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;
我的异常网推荐解决方案：软件开发者薪资,http://www.myexception.cn/other/1391128.html
