SELECT a.* ,b.`description`,sum(`qty`)
FROM `Stock_Moves` a LEFT JOIN `Product` b ON a.`item_code`=b.`item_code`
WHERE `loc_code`='SZW' GROUP BY `item_code`
FROM `Stock_Moves` a LEFT JOIN `Product` b ON a.`item_code`=b.`item_code`
WHERE `loc_code`='SZW' GROUP BY `item_code`
参考一下这个贴子的提问方式http://bbs.csdn.net/topics/320211382
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
你好! 因为原表数据很多,我建了个简单的测试表。CREATE TABLE IF NOT EXISTS `order` (
`trans_no` int(11) NOT NULL,
`item_code` varchar(50) NOT NULL,
`loc_code` varchar(50) NOT NULL,
`qty` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;--
-- Dumping data for table `order`
--INSERT INTO `order` (`trans_no`, `item_code`, `loc_code`, `qty`) VALUES
(123, '001-00', 'SZW', 10),
(1234, '001-00', 'SZW', 20),
(12345, '001-00', 'SZW', 10),
(123456, '001-00', 'HKW', 20);这个是我目前用的语句:
SELECT *,sum(`qty`) FROM `order` WHERE `trans_no`=123 and `loc_code`='SZW' 我实际想要的结果是
trans_no----item_code---loc_code----sum(`qty`)
123------001-00-------SZW-----40
+----------+-----------+----------+-----+
| trans_no | item_code | loc_code | qty |
+----------+-----------+----------+-----+
| 123 | 001-00 | SZW | 10 |
| 1234 | 001-00 | SZW | 20 |
| 12345 | 001-00 | SZW | 10 |
| 123456 | 001-00 | HKW | 20 |
+----------+-----------+----------+-----+
4 rows in set (0.00 sec)mysql> SELECT *,sum(`qty`) FROM `order` WHERE `trans_no`=123 and `loc_code`='SZW' ;
+----------+-----------+----------+-----+------------+
| trans_no | item_code | loc_code | qty | sum(`qty`) |
+----------+-----------+----------+-----+------------+
| 123 | 001-00 | SZW | 10 | 10 |
+----------+-----------+----------+-----+------------+
1 row in set (0.00 sec)mysql> SELECT trans_no,item_code,loc_code,
-> (select sum(`qty`) FROM `order` WHERE `loc_code`=o.`loc_code`) as sumqty
-> FROM `order` o
-> WHERE `trans_no`=123 and `loc_code`='SZW' ;
+----------+-----------+----------+--------+
| trans_no | item_code | loc_code | sumqty |
+----------+-----------+----------+--------+
| 123 | 001-00 | SZW | 40 |
+----------+-----------+----------+--------+
1 row in set (0.00 sec)mysql>