帮忙谢谢这条sql语句 急用
0
悬赏园豆：50
[已解决问题]
解决于 2017-08-01 14:02
oracle数据库
表如下
table acc(
name balance subyearmonth
A 12 2016
S 13 2017
S 32 2017
A 11 2016
R 17 2017
G 34 2016
A 21 2017
s 36 2017
)
查询结果显示
name num1 num2
注:name显示结果为 把相同的name名字合并 num1和num2显示为相同name的num数值和 其中 num1的数值和为时间是2016年的，num2的数值和为时间为2017年的
最佳答案
0
SELECT a.name, num1,num2 FROM (SELECT NAME,SUM(num) AS num1 FROM acc WHERE YEAR ='2016' GROUP BY NAME) a LEFT JOIN (SELECT NAME, SUM(num) AS num2 FROM acc WHERE YEAR ='2017' GROUP BY NAME) b ON a.name = b.name
收获园豆：20
其他回答(3)
0
select a.name ,
(select sum(num) from 表名 where year = 2016 and name = a.name)as 'nums1',
(select sum(num) from 表名 where year = 2017 and name = a.name)as 'nums2'
from 表名 a GROUP BY name
year 是系统关键字 自己加个s （示例：years）
收获园豆：10
报错
@长跑: 你把题目 改啦
现在的问题如下就可以了：
select a.name ,
(select sum(balance) from 表名 where subyearmonth= 2016 and name = a.name)as 'nums1',
(select sum(balance) from 表名 where subyearmonth= 2017 and name = a.name)as 'nums2'
from 表名 a GROUP BY name
你刚刚字段是num，现在是balance，换一下就可以了，还有subyearmonth你刚刚是year，字段不同 所以报错
@没什么比你更优秀: 改了还是报错 它说找不到from关键字
@长跑: 你把语句执行和报错结果截图下来看看
0
SELECT t.personname, sum(IF(t. YEAR = 2016, s, 0)) AS 2016y, sum(IF(t. YEAR = 2017, s, 0)) AS 2017y FROM ( SELECT personName, YEAR, sum(num) s FROM acc GROUP BY personName, YEAR ) t GROUP BY personname
msyql为例：
DROP TABLE IF EXISTS `acc`; CREATE TABLE `acc` ( `personName` varchar(50) DEFAULT NULL, `num` int(11) DEFAULT NULL, `year` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of acc -- ---------------------------- INSERT INTO `acc` VALUES ('joey', '1', '2016'); INSERT INTO `acc` VALUES ('joey', '1', '2017'); INSERT INTO `acc` VALUES ('ross', '1', '2016'); INSERT INTO `acc` VALUES ('ross', '1', '2017'); INSERT INTO `acc` VALUES ('joey', '1', '2016'); INSERT INTO `acc` VALUES ('ross', '1', '2016');
你这是什么？
@长跑: 没什么，来骗分的
0
select a.name , (select sum(balance) from acc where subyearmonth= 2016 and name = a.name)as 'num1', (select sum(balance) from acc where subyearmonth= 2017 and name = a.name)as 'num2' from acc as a GROUP BY name
收获园豆：10
