2 qq 39586869 qq_39586869 于 2017.09.09 16:24 提问

还是一道sql语句的题好难。。求解
sql

图片说明

求路过高手,大神给看看,务必于今晚之前拿下!!不然会被炒鱿鱼的!!

9个回答

lkhog
lkhog   2017.09.09 23:37
已采纳

考试题?
SELECT '1' 列名,(SELECT SUM(数值列) FROM 表 WHERE NAME = 'aa') aa, (SELECT SUM(数值列) FROM 表 WHERE NAME = 'bb') bb,
(SELECT SUM(数值列) FROM 表 WHERE NAME = 'cc') cc,(SELECT SUM(数值列) FROM 表 WHERE NAME = 'dd') dd FROM dual;

weixin_40170270
weixin_40170270   2017.09.09 17:48

SELECT SUM(nums) FROM demo GROUP BY name

表名:Sum_Test

Id Name Count Type

1 A 12 1
2 B 12 1
3 C 12 1
4 D 12 1
5 B 12 2
6 B 12 1
7 A 12 2
8 A 12 1
9 C 12 1
10 B 12 1

结果: Type 1 : 84
Type 2 : 36

求type 为1和2的Count的总和,如果Name即有Type为1也有Type为2的,则该Count算到Type为2的里面,如: A

Type即有1也有2则A的求和时将A的Count值全部算作为Type=2里面

方法一:

现将Type为2的Name的Type值都改为2

UPDATE Sum_Test SET Type=2 WHERE Name IN (SELECT Name FROM Sum_Test SHERE Type=2)

然后再求和

SELECT SUM(Count) FROM Sum_Test GROUP BY Type

方法二:

select sum([count]) from
(
select [count],[Type]='2' from sum_test where [name] in (select [name] from sum_test where type=2)
UNION ALL
select [count],[Type]='1' from sum_test where [name] not in (select [name] from sum_test where type=2)
) t
group by [type]

这两种方法虽然可以得到想要的结果,但是有很大的弊端

方法三:

select (select sum([count]) from sum_test) - (select sum(t.counts) from
(select []name, sum([count]) counts from sum_test
where [name] in(select [name] from sum_test where [type] = 2)
group by [name]) t) type1, (select sum(t.counts) from
(select [name], sum([count]) counts from sum_test
where [name] in(select [name] from sum_test where [type] = 2)
group by [name]) t) type2

qq_24827217
qq_24827217   2017.09.09 23:18

select * from (select name, nums from demo) pivot (sum(nums) for name in (select namefrom demo gruop by name));

你试试需要这种效果

gjf214
gjf214   2017.09.09 16:38

content不太对

qq_33727653
qq_33727653   2017.09.09 16:40

这不是 select name ,sum(nums) from demo group by name?

bao_java
bao_java   2017.09.09 16:59

select sum(nums) as name from demo
GROUP BY name

u010105757
u010105757   2017.09.09 17:07

2楼正解,不过楼主是DBA吗?基础知识不太牢固

qq_23501635
qq_23501635   2017.09.11 09:55

select name,totalnum from (select name as name,sum(nums) as totalnum from demo group by name) pivot (totalnum for name in ('aa','bb','cc','dd'))

qq_40090700
qq_40090700   2017.09.11 13:31

你这个就是行变列
使用case 就好了

select
sum(case when 'aa' then 值 else 0 end)aa,
...
from 表名

这样也可以得

Csdn user default icon
上传中...
上传图片
插入图片