双11狂欢大促

盲盒赢 定制羽绒服

开启惊喜盲盒，抽取你的专属好礼

— 惊喜奖品抢先看 —

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

在线求sql语句急

2007-03-06 06:09:36 524 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

lzt7 墨衍会员 · AI 创作全网分发关注 + 加入社区

Linux系统常用命令及程序初步必须确定挂载点已经存在，也就是在“/mnt”下的相应子目录已经存在，一般建议在“/mnt”下新建几个如“/mnt/windows”，“/mnt/usb”的子目录，现在有些新版本的 Linux 都可自动挂载文件系统。由于 vi 在不同的模式下有不同的操作功能，因此，读者一定要时刻注意屏幕最下方的提示，分清所在的模式。用户在用 vi 编辑文件时，最初进入的为一般模式。通常，在 Linux 下“/mnt”目录是专门用于挂载不同的文件系统的，它可以在该目录下新建不同的子目录来挂载不同的设备文件系统。阅读详情

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

...全文

VCSA 上的 vmware-vpxd 服务失败，Web端提示503 服务不可用 (503 Service Unavailable) 昨晚VCSA碰到一个很诡异的问题：使用 vSphere Web Client 连接到 vCenter Server Appliance时失败，提示： 503 服务不可用 (503 Service Unavailable) 无法连接端点 (Failed to connect to endpoint)：[N7Vmacore4Http20NamedPipeServiceSpecE:0x00007f975808ee30] _serverNamespace = / action = Allow _pipeNam 阅读详情

lzt7

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

lzt7 2007-03-07

打赏
举报

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

lzt7 2007-03-07

打赏
举报

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 2007-03-07

打赏
举报

操作符丢失呢

msjqd 2007-03-07

打赏
举报

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

dawugui 2007-03-07

打赏
举报

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

lzt7 2007-03-07

打赏
举报

好像不行呢

smalllifei 2007-03-07

打赏
举报

不错

lzt7 2007-03-07

打赏
举报

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

paoluo 2007-03-07

打赏
举报

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

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

lzt7 2007-03-07

打赏
举报

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

paoluo 2007-03-07

打赏
举报

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

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

paoluo 2007-03-07

打赏
举报

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;

lzt7 2007-03-07

打赏
举报

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

lzt7 2007-03-07

打赏
举报

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

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

paoluo 2007-03-07

打赏
举报

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

一者仁心 2007-03-06

打赏
举报

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

hrb2008 2007-03-06

打赏
举报

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 行受影响)

onlyguhong 2007-03-06

打赏
举报

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

没测试，不知道行不行

✦双11狂欢大促✦

在线求sql语句 急

相关推荐

双11狂欢大促

在线求sql语句急