如果你确保 tillball 中含有所有的 ChipNumber 则可以用下面的SQLmysql> select XChipNumber as ChipNumber, -> XClassNumber as ClassNumber, -> (select sum(ZHeadResult) from head where ZChipNumber=XChipNumber) as CheckHead, -> sum(XTillBallResult) as CheckTillBall, -> (select sum(YLeadResult) from lead where YChipNumber=XChipNumber) as CheckLead -> from tillball -> group by XChipNumber,XClassNumber; +------------+-------------+-----------+---------------+-----------+ | ChipNumber | ClassNumber | CheckHead | CheckTillBall | CheckLead | +------------+-------------+-----------+---------------+-----------+ | 01 | 1111 | 17 | 4 | 41 | | 02 | 2222 | 21 | 12 | 45 | +------------+-------------+-----------+---------------+-----------+ 2 rows in set (0.06 sec)mysql>
select a.xchipnumber,a.xclassnumber,a.XTillBallResult,b.ZHeadResult,c.YLeadResult from ( select XChipNumber ,XClassNumber,sum(XTillBallResult) XTillBallResult from tillball group by XChipNumber ,XClassNumber) a, (select ZChipNumber ,ZClassNumber ,sum(ZHeadResult) ZHeadResult from head group by ZChipNumber ,ZClassNumber) b , (select YChipNumber ,YClassNumber ,sum(YLeadResult) YLeadResult from lead group by YChipNumber ,YClassNumber)c where a.XChipNumber=b.ZChipNumber and b.ZChipNumber=c.YChipNumber and a.XClassNumber=b.ZClassNumber and b.ZClassNumber= c.YClassNumber; +-------------+--------------+-----------------+-------------+-------------+ | xchipnumber | xclassnumber | XTillBallResult | ZHeadResult | YLeadResult | +-------------+--------------+-----------------+-------------+-------------+ | 01 | 1111 | 4 | 17 | 41 | | 02 | 2222 | 12 | 21 | 45 | +-------------+--------------+-----------------+-------------+-------------+
老师,我4个表都不设置主键,怎么写这个操作的mysql???我现在把4个表的主键都撤销了。
-> XClassNumber as ClassNumber,
-> (select sum(ZHeadResult) from head where ZChipNumber=XChipNumber) as CheckHead,
-> sum(XTillBallResult) as CheckTillBall,
-> (select sum(YLeadResult) from lead where YChipNumber=XChipNumber) as CheckLead
-> from tillball
-> group by XChipNumber,XClassNumber;
+------------+-------------+-----------+---------------+-----------+
| ChipNumber | ClassNumber | CheckHead | CheckTillBall | CheckLead |
+------------+-------------+-----------+---------------+-----------+
| 01 | 1111 | 17 | 4 | 41 |
| 02 | 2222 | 21 | 12 | 45 |
+------------+-------------+-----------+---------------+-----------+
2 rows in set (0.06 sec)mysql>
select XChipNumber ,XClassNumber,sum(XTillBallResult) XTillBallResult from tillball group by XChipNumber ,XClassNumber) a,
(select ZChipNumber ,ZClassNumber ,sum(ZHeadResult) ZHeadResult from head group by ZChipNumber ,ZClassNumber) b ,
(select YChipNumber ,YClassNumber ,sum(YLeadResult) YLeadResult from lead group by YChipNumber ,YClassNumber)c
where a.XChipNumber=b.ZChipNumber and
b.ZChipNumber=c.YChipNumber and a.XClassNumber=b.ZClassNumber and b.ZClassNumber= c.YClassNumber;
+-------------+--------------+-----------------+-------------+-------------+
| xchipnumber | xclassnumber | XTillBallResult | ZHeadResult | YLeadResult |
+-------------+--------------+-----------------+-------------+-------------+
| 01 | 1111 | 4 | 17 | 41 |
| 02 | 2222 | 12 | 21 | 45 |
+-------------+--------------+-----------------+-------------+-------------+
老师,我前面3个表都含有所有的 ChipNumber 因为我是时时插入的,而且是同时一起插入同一个ChipNumber。你说的第一次的方法我觉得有点繁琐。因为我前面的3个表中有很多列,按照你原来的
方法的话那样sql语句是不是会显得很长?很不方便修改?