求SQL语句？(50分)

D

dxwwgr

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-02-22

• #1

求SQL语句？
表一：
代号 姓名
001 张三
002 李四
003 王五
表二：
代号 工资 月份
001 1000 1
002 2000 1
003 3000 1
001 1000 2
002 2000 2
003 3000 2
001 1000 3
002 2000 3
003 3000 3
表三：
代号 奖金 月份
001 100 1
002 200 1
003 300 1
001 100 2
002 200 2
003 300 2
001 100 3
002 100 3
003 300 3
由上面三个表通过SQL语句查询得到如下表
表四：
姓名 工资总数 奖金总数 收入总数
张三 3000 300 3300
李四 6000 600 6600
王五 9000 900 9900

 

无

无头骑士

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-02-22

• #2

select 姓名,sum(工资),sum(奖金)，sum(工资)+sum(奖金) as 收入总数
from 表一，表二，表三
where 表一.代号=表二.代号 and 表二.代号=表三.代号 group by 姓名

 

J

jennykiller

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-02-22

• #3

select b1.姓名,sum(b2.工资) as 工资总数,sum(b3.奖金) as 奖金总数,
(sum(b2.工资) + sum(b3.奖金)) as 收入总数
from b1,b2,b3
where b1.代号=b2.代号
and b1.代号=b3.代号
group by b1.姓名

 

姚

姚哥

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-02-22

• #4

select 姓名,
工资总数=(select sum(工资) from 表二 where 代号=a.代号),
奖金总数=(select sum(工资) from 表三 where 代号=a.代号),
收入总数=(select sum(工资) from 表二 where 代号=a.代号)+(select sum(工资) from 表三 where 代号=a.代号)
from 表一 a
order by 代号

 

D

dxwwgr

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-02-22

• #5

jennykiller和无头骑士的方法我试过不行．

 

B

babibean

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-02-22

• #6

SELECT 姓名,
(SELECT SUM(工资) FROM 表二 WHERE 表二.代号=表一.代号 GROUP BY 表二.代号 ) AS 工资总数,
(SELECT SUM(工资) FROM 表三 WHERE 表三.代号=表一.代号 GROUP BY 表三.代号) AS 奖金总数,
(SELECT SUM(工资) FROM 表二 WHERE 表二.代号=表一.代号 GROUP BY 表二.代号 ) +
(SELECT SUM(工资) FROM 表三 WHERE 表三.代号=表一.代号 GROUP BY 表三.代号) AS 收入总数
FROM 表一

 

B

babibean

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-02-22

• #7

刚才没看到，姚哥的写法跟我的一样啊.

 

L

L.Ming

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-02-22

• #8

SELECT 姓名,工资,奖金,工资+奖金 AS 收放总数 FROM 表一 A,
(SELECT 代号,SUM(工资) AS 工资 FROM 表二 GROUP BY 代号) B,
(SELECT 代号,SUM(奖金) AS 奖金 FROM 表三 GROUP BY 代号) C
WHERE A.代号 = B.代号 AND A.代号 = C.代号

 

F

fsse4000

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-02-22

• #9

简单，他们已经做出来了

 

V

vic152

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-02-22

• #10

看楼上的....

 

D

dxwwgr

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-02-22

• #11

上面有的方法算的数据不对，
姚哥的方法也不行，高手指点？

 

H

hwh6666

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-02-23

• #12

select A.姓名,B.工资,C.奖金,(B.工资+C.奖金) AS 总数 from 表一 A LEFT JOIN
(SELECT 代号,SUM(工资) AS 工资 FROM 表二 GROUP BY 代号) B ON A.代号 = B.代号
LEFT JOIN
(SELECT 代号,SUM(奖金) AS 奖金 FROM 表三 GROUP BY 代号) C ON A.代号 = C.代号

 

B

babibean

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-02-23

• #13

SELECT 姓名,
(SELECT SUM(工资) FROM 表二 WHERE 表二.代号=表一.代号 GROUP BY 表二.代号 ) AS 工资总数,
(SELECT SUM(奖金) FROM 表三 WHERE 表三.代号=表一.代号 GROUP BY 表三.代号) AS 奖金总数,
(SELECT SUM(工资) FROM 表二 WHERE 表二.代号=表一.代号 GROUP BY 表二.代号 ) +
(SELECT SUM(奖金) FROM 表三 WHERE 表三.代号=表一.代号 GROUP BY 表三.代号) AS 收入总数
FROM 表一
-----------
我把这个修改了一下,上面写的哪个有点小问题.

 

D

dxwwgr

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-02-23

• #14

这是我的语句，但是不语法通不过
SELECT Table_JH.Name AS 机号,
(select sum(Table_GD_X1.BZ_CL) from Table_GD_X1 where Table_JH.ID = Table_GD_X1.JH_ID GROUP BY Table_GD_X1.JH_ID) as 包装产量合计,
(select sum(Table_PROTOS.JY_CL) from Table_PROTOS where Table_JH.ID = Table_PROTOS.JH_ID GROUP BY Table_PROTOS.JH_ID) as 卷烟产量合计,
(select sum(Table_GD_X1.BZ_CL) from Table_GD_X1 where Table_JH.ID = Table_GD_X1.JH_ID GROUP BY Table_GD_X1.JH_ID)-(select sum(Table_PROTOS.JY_CL) from Table_PROTOS where Table_JH.ID = Table_PROTOS.JH_ID GROUP BY Table_PROTOS.JH_ID) as 卷烟包装差异
FROM Table_JH
WHERE (Table_PROTOS.Class_Total = 1) AND (Table_GD_X1.Class_Total = 1) AND
(Table_PROTOS.Date_Time > '2006 - 2 - 21') AND
(Table_PROTOS.Date_Time < '2006 - 2 - 22') AND
(Table_GD_X1.Date_Time > '2006 - 2 - 21') AND
(Table_GD_X1.Date_Time < '2006 - 2 - 22')
GROUP BY Table_JH.Name

 

D

dxwwgr

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-02-23

• #15

这个语句算的结果不对，有什么错？
SELECT Table_JH.Name AS 机号, SUM(Table_GD_X1.BZ_CL) AS 包装产量合计,
SUM(Table_PROTOS.JY_CL) AS 卷烟产量合计, SUM(Table_PROTOS.JY_CL)
- SUM(Table_GD_X1.BZ_CL) AS 卷烟包装差异
FROM Table_JH INNER JOIN
Table_PROTOS ON Table_JH.ID = Table_PROTOS.JH_ID INNER JOIN
Table_GD_X1 ON Table_JH.ID = Table_GD_X1.JH_ID
WHERE (Table_PROTOS.Class_Total = 1) AND (Table_GD_X1.Class_Total = 1) AND
(Table_PROTOS.Date_Time > '2006 - 2 - 21') AND
(Table_PROTOS.Date_Time < '2006 - 2 - 22') AND
(Table_GD_X1.Date_Time > '2006 - 2 - 21') AND
(Table_GD_X1.Date_Time < '2006 - 2 - 22')
GROUP BY Table_JH.Name

 

飘

飘雪

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-02-23

• #16

select gh.代号, gh.姓名, sum(gh.工资) as 工资总数, sum(表三.奖金) as 奖金总数,sum(gh.工资+表三.奖金) as 收入总数 from
(SELECT 表一.代号 as 代号,表一.姓名 as 姓名,表二.工资 as 工资 ,表二.月份 as 月份
FROM 表一 INNER JOIN 表二 ON 表一.代号 = 表二.代号
ORDER BY 表一.代号, 表二.月份) as gh INNER JOIN 表三 on gh.月份 = 表三.月份
where gh.代号 = 表三.代号
group by gh.代号,gh.姓名
以上在Access下测试通过

 

K

kaolajue_1003

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-02-23

• #17

select 表一.姓名,sum(工资)as 工资,sum(奖金)as 奖金,sum(工资)+sum(奖金) as 收入总数
from 表一,表二,表三 where 表一.代号=表二.代号 and 表二.代号=表三.代号 and 表二.月份=表三.月份
group by 表一.姓名

 

D

dxwwgr

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-02-25

• #18

还是不行

 

L

LL_11

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-02-27

• #19

select 姓名,
工资总数=(select sum(工资) from 表2 where 代号=a.代号),
奖金总数=(select sum(奖金) from 表3 where 代号=a.代号),
收入总数=(select sum(工资) from 表2 where 代号=a.代号)+(select sum(奖金) from 表3 where 代号=a.代号)
from 表1 a
order by 代号

 

H

hinco

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-02-27

• #20

如果仅仅是lz题目的要求，姚哥的SQL就可以了，lz之所以出错在于你多了GROUP BY Table_JH.Name，有了这一句Table_JH.ID 就无法取到了
lz最好把具体的情况说清楚，很显然你给的模拟数据例子和你真正想要的效果并不相吻合