在线求sql语句 急

[lzt7]

员工表: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语句?

[onlyguhong]

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

没测试，不知道行不行

[hrb2008]

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

[paoluo]

你開始沒說是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;

[lzt7]

数据库引擎找不到输入表或查询'Select ry_id,sum(je) AS SumPrice from WS_jjgz Group By ry_id'。确定它是否存在，以及它的名称是否正确。

但我检查过，是都正确的啊

[lzt7]

我这样写的
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 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;

[paoluo]

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;

[paoluo]

錯誤在這裡，
[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].

另外，我開始寫的語句有點錯誤，現在修改了下，你再測試下。

[lzt7]

可以了，就是不解， 那个点有什么用的？？本人愚昧，恳请回答

[paoluo]

我對ACCESS也不是很熟悉，開始我的語句不是這麼寫的，它會自動轉成那樣的。

我對照你的語句和我的語句，找了許久，才發現是那個.的問題。

[lzt7]

恩恩，以后我会注意的了，感激paoluo(一天到晚游泳的鱼) ，感激csdn所有热心的朋友

[smalllifei]

不错

[lzt7]

好像不行呢

[dawugui]

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 行）
*/

[msjqd]

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

[lzt7]

操作符丢失呢

[lzt7]

select a.*, isnull(b.计件总工资,0) as 计件总工资 , isnull(c.计时总工资 , 0) as 计时总工资 , isnull(b.计件总工资,0) + isnull(c.计时总工资 , 0) as 总工资
from WS_Employee a
left join (select ry_id,sum(je) as 计件总工资 from WS_jjgz group by ry_id) b on a.ID = b.ry_id
left join (select ry_id,sum(je) as 计时总工资 from WS_jsgz group by ry_id) c on a.ID = c.ry_id

是否access都能使用的啊？

[lzt7]

还有，问个菜菜问题，dawugui(潇洒老乌龟) 那些测试是怎么做出来的？用什么软件做这个测试的啊？