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 7CREATE 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 11CREATE 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个表缩成一个表那样更加
简单,其实每个表有很多列,我这里暂时省了一些,而且有时候我还需要单独
对每个表操作,所以就分开设计建表。
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 7CREATE 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 11CREATE 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个表缩成一个表那样更加
简单,其实每个表有很多列,我这里暂时省了一些,而且有时候我还需要单独
对每个表操作,所以就分开设计建表。
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>
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>
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