这样的SQL该怎么写？(在线等!)

[duangexin521]

CREATE TABLE tillball (
XChipNumber varchar(50),
XClassNumber varchar(50),
XTillBallResult int(50),
PRIMARY KEY (XChipNumber)
)
值
XChipNumber XClassNumber XTillBallResult
01 1111 1
01 1111 3
02 2222 5
02 2222 7

CREATE TABLE head (
ZChipNumber varchar(50)
ZClassNumber varchar(50)
ZHeadResult int(50)
PRIMARY KEY (ZCheckNumber)
)
值
ZChipNumber ZClassNumber ZHeadResult
01 1111 8
01 1111 9
02 2222 10
02 2222 11

CREATE TABLE lead (
YChipNumber varchar(50),
YClassNumber varchar(50),
YLeadResult int(50),
PRIMARY KEY (YCheckNumber)
)
值
YChipNumber YClassNumber YLeadResult
01 1111 20
01 1111 21
02 2222 22
02 2222 23

总表
CREATE TABLE testresult (
ChipNumber varchar(50),
ClassNumber varchar(50),
CheckHead int(50),
CheckTillBall int(50),
CheckLead int(50),
PRIMARY KEY (CheckNumber)
)
总表所得值
ChipNumber ClassNumber CheckHead CheckTillBall CheckLead
01 1111 17 4 41
02 2222 21 12 45

请问最后总表汇总的值sql语句怎么写？
举例一下：
比如查询前面3个表主键值= 01的相关列求和，并将和值插入总表对应列。
比如tillball表，XChipNumber=01，对XTillBallResult求和=1 +3 =4
head表中，ZChipNumber=01，对ZHeadResult求和=8 + 9 =17
Lead表中，YChipNumber=01，对YLeadResult 求和=20 + 21=41
将结果插入到总表中对应列，而ChipNumber ClassNumber这2个值可以随便
使用前面3个分表对应的2列值。有人可能会问将3个表缩成一个表那样更加
简单，其实每个表有很多列，我这里暂时省了一些，而且有时候我还需要单独
对每个表操作，所以就分开设计建表。

[ACMAIN_CHM]

　你提供的表结构对吗？！

PRIMARY KEY (XChipNumber)　既然是主键，怎么还有重复的？

建议给出例子的时候能够准确一些，否则别人根本无法测试！反而是浪费双方的时间。

mysql> CREATE TABLE tillball (
    -> XChipNumber varchar(50),
    -> XClassNumber varchar(50),
    -> XTillBallResult int(50),
    -> PRIMARY KEY (XChipNumber)
    -> );
Query OK, 0 rows affected (0.16 sec)

mysql> insert into tillball values
    -> ('01','1111',1),
    -> ('01','1111',3),
    -> ('02','2222',5),
    -> ('02','2222',7);
ERROR 1062 (23000): Duplicate entry '01' for key 'PRIMARY'
mysql>

[ACMAIN_CHM]

估计楼主是做半导体行业的ＳＰＣ的。

mysql> select ChipNumber,ClassNumber,sum(CheckHead),sum(CheckTillBall),sum(Check
Lead)
    -> from (
    ->  select XChipNumber as ChipNumber,
    ->          XClassNumber as ClassNumber,
    ->          0 as CheckHead,
    ->          XTillBallResult as CheckTillBall,
    ->          0 as CheckLead
    ->  from tillball
    ->  union all
    ->  select ZChipNumber as ChipNumber,
    ->          ZClassNumber as ClassNumber,
    ->          ZHeadResult as CheckHead,
    ->          0 as CheckTillBall,
    ->          0 as CheckLead
    ->  from head
    ->  union all
    ->  select YChipNumber as ChipNumber,
    ->          YClassNumber as ClassNumber,
    ->          0 as CheckHead,
    ->          0 as CheckTillBall,
    ->          YLeadResult as CheckLead
    ->  from lead
    -> ) t
    -> group by ChipNumber,ClassNumber;
+------------+-------------+----------------+--------------------+----------------+
| ChipNumber | ClassNumber | sum(CheckHead) | sum(CheckTillBall) | sum(CheckLead) |
+------------+-------------+----------------+--------------------+----------------+
| 01         | 1111        |             17 |                  4 |             41 |
| 02         | 2222        |             21 |                 12 |             45 |
+------------+-------------+----------------+--------------------+----------------+
2 rows in set (0.00 sec)

mysql>

[vipper23]

insert into testresult
select a.XChipNumber,a.XClassNumber,sum(a.XTillBallResult),sum(b.ZHeadResult),sum(c.YLeadResult) from tillball a , head b,lead c where a.XChipNumber=b.ZChipNumber and
b.ZChipNumber=c.YChipNumber and a.XClassNumber=b.ZClassNumber and b.ZClassNumber= c.
YClassNumber group by a.XChipNumber,a.XClassNumber

[duangexin521]

引用 2 楼 acmain_chm 的回复:

估计楼主是做半导体行业的ＳＰＣ的。
SQL codemysql>select ChipNumber,ClassNumber,sum(CheckHead),sum(CheckTillBall),sum(Check
Lead)->from (->select XChipNumberas ChipNumber,-> XClassNumberas ClassNumber,->0as CheckHead,-> XTillBallResultas CheckTillBall,->0as CheckLead->from tillball->unionall->select ZChipNumberas ChipNumber,-> ZClassNumberas ClassNumber,-> ZHeadResultas CheckHead,->0as CheckTillBall,->0as CheckLead->from head->unionall->select YChipNumberas ChipNumber,-> YClassNumberas ClassNumber,->0as CheckHead,->0as CheckTillBall,-> YLeadResultas CheckLead->from lead-> ) t->groupby ChipNumber,ClassNumber;+------------+-------------+----------------+--------------------+----------------+| ChipNumber| ClassNumber|sum(CheckHead)|sum(CheckTillBall)|sum(CheckLead)|+------------+-------------+----------------+--------------------+----------------+|01|1111|17|4|41||02|2222|21|12|45|+------------+-------------+----------------+--------------------+----------------+2 rowsinset (0.00 sec)

mysql>

谢谢老师，公司是微电子公司，主要做IC这块的。

[duangexin521]

引用 3 楼 vipper23 的回复:

SQL codeinsertinto testresultselect a.XChipNumber,a.XClassNumber,sum(a.XTillBallResult),sum(b.ZHeadResult),sum(c.YLeadResult)from tillball a , head b,lead cwhere a.XChipNumber=b.ZChipNumberand
b.ZChip?-

谢谢朋友指点。

[duangexin521]

3楼的方法怎么算出来的结果是4倍？？？
01 1111 16 68 164
02 2222 48 84 180
好像4个表就计算了4次？？请问怎么回事？？