用select sum(A) as A,sum(B) as B,c from Table1 Group by C不行吗?
to wujinbao(飞扬草):我的方法就可以的,你可以一试,你是不是没有学过SQL
不行呀系统提示如下错误信息: Column 'Table1.C' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
老兄,你用什么数据库,怎么可能: select c, sum(a) as a,sum(b) as b from table1 group by c
to jcq(疯子弟) 看清我的问题,照你说的那我C字段的几种取值情况没有考虑到。 我要根据C的取值来对sum(A) ,sum(B)归类。 摆脱 老兄说话能不能客气点? 什么叫没有学过SQL?没有你牛就是没学过呀?
to jcq(疯子弟) 我是照nanman(南蛮)所说的出现的错误提示!
哦对不起是我说错了问题因该是: 现有一表Table1结构大体如下Table1(A,B,C,D)。 C有四种不同取值1,2,3,4。现希望用一个sql语句统计出对应C 的四种取值A,B的合计值。 用select sum(A) as A,sum(B) as B from Table1 Where C = 1 Group by D,只能求出C=1一种情况下 A,B合计值。 非常抱歉!!!
这样吧: select sum(A) as A,sum(B) as B,C,D from Table1 Group by C,D
我都跟你说了(我给你一个例子,你看是不是要这种结果) 表的内容: a b c ----------- ----------- ----------- 1 1 1 2 2 1 3 3 1 4 4 1 1 1 2 2 2 2 3 3 2 1 1 3 1 3 4 2 2 4 执行语句:select c,sum(a) as a,sum(b) as b from table1 group by c的结果: c a b ----------- ----------- ----------- 1 10 10 2 6 6 3 1 1 4 3 5 我是在SQL7中试的,不知,你是不是要这样的结果。
二位非常感谢!你们所说的都是对的。 to nanman(南蛮):按照你说的可以达到我的要求。 结果如下: D A B C -------- ----------- ----------- BBB 2 8 3 BBB 5 6 4 不过我还有点要求就是可不可以达到如下显示效果? --------------- C =3 C=4 D A B A B -------- ----------- ----------- ----------- BBB 2 8 5 6 也不知道行不行,为表诚意再加20分,已做酬谢!!
nanman(南蛮)和jcq(疯子弟)都没错啊,是你没问到点子上。 原来你要做透视表啊! 看我的 select sum((case when C=1 then 1 else 0 end)*A) as sumA1, sum((case when C=1 then 1 else 0 end)*B) as sumB1, sum((case when C=2 then 1 else 0 end)*A) as sumA2, sum((case when C=2 then 1 else 0 end)*B) as sumB2, sum((case when C=3 then 1 else 0 end)*A) as sumA3, sum((case when C=3 then 1 else 0 end)*B) as sumB3, sum((case when C=4 then 1 else 0 end)*A) as sumA4, sum((case when C=4 then 1 else 0 end)*B) as sumB4 from Table1 group by C如果C的取值不确定怎么办?只好定义一个字符串变量,动态的生成上面的语句,然后用execsql来执行。
看我的 select d,sum(choose case c when 3 then a else 0) ca3 ,sum(choose case c when 3 then b else 0) cb3, sum(choose case c when 4 then a else 0) ca4 ,sum(choose case c when 4 then b else 0) cb4 from (select sum(A) as A,sum(B) as B,C,D from Table1 Group by C,D ) a group by d
我写错了?好像应该是Group by D?select sum((case when C=1 then A else 0 end) as sumA1, sum(case when C=1 then B else 0 end) as sumB1, sum(case when C=2 then A else 0 end) as sumA2, sum(case when C=2 then B else 0 end) as sumB2, sum(case when C=3 then A else 0 end) as sumA3, sum(case when C=3 then B else 0 end) as sumB3, sum(case when C=4 then A else 0 end) as sumA4, sum(case when C=4 then B else 0 end)*B) as sumB4 from Table1 group by D
不可能的,如果C值确定的话可以做,但是很麻烦。我有写过,不过不太一样,你自己改造吧: DECLARE @dev varchar(2), @ValueSQL varchar(220), @name varchar(2), @newname varchar(200) DECLARE cur_test CURSOR FOR SELECT dev FROM test GROUP BY dev OPEN cur_test FETCH next FROM cur_test INTO @dev SET @valuesql = 'select dev,name from test where 1=0' WHILE @@fetch_status = 0 BEGIN DECLARE cur_value CURSOR FOR SELECT dev, name FROM test WHERE dev = @dev OPEN cur_value FETCH next FROM cur_value INTO @dev,@name SET @newname = '' WHILE @@fetch_status = 0 BEGIN SET @newname = @newname + ' ' + @name FETCH next FROM cur_value INTO @dev,@name END SET @valuesql = @valuesql + ' union select ''' + @dev + ''' as dev,''' + @newname + ''' as name' CLOSE cur_value DEALLOCATE cur_value fetch next from cur_test into @dev END close cur_test deallocate cur_test set @valuesql = @valuesql + ' order by dev' exec(@valuesql)
Column 'Table1.C' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
select c, sum(a) as a,sum(b) as b from table1 group by c
我要根据C的取值来对sum(A) ,sum(B)归类。
摆脱 老兄说话能不能客气点? 什么叫没有学过SQL?没有你牛就是没学过呀?
现有一表Table1结构大体如下Table1(A,B,C,D)。
C有四种不同取值1,2,3,4。现希望用一个sql语句统计出对应C 的四种取值A,B的合计值。
用select sum(A) as A,sum(B) as B from Table1 Where C = 1 Group by D,只能求出C=1一种情况下
A,B合计值。
非常抱歉!!!
select sum(A) as A,sum(B) as B,C,D from Table1 Group by C,D
表的内容:
a b c
----------- ----------- -----------
1 1 1
2 2 1
3 3 1
4 4 1
1 1 2
2 2 2
3 3 2
1 1 3
1 3 4
2 2 4
执行语句:select c,sum(a) as a,sum(b) as b from table1 group by c的结果:
c a b
----------- ----------- -----------
1 10 10
2 6 6
3 1 1
4 3 5
我是在SQL7中试的,不知,你是不是要这样的结果。
to nanman(南蛮):按照你说的可以达到我的要求。
结果如下:
D A B C
-------- ----------- -----------
BBB 2 8 3
BBB 5 6 4
不过我还有点要求就是可不可以达到如下显示效果?
---------------
C =3 C=4
D A B A B
-------- ----------- ----------- -----------
BBB 2 8 5 6
也不知道行不行,为表诚意再加20分,已做酬谢!!
马上散分了!
原来你要做透视表啊!
看我的
select sum((case when C=1 then 1 else 0 end)*A) as sumA1,
sum((case when C=1 then 1 else 0 end)*B) as sumB1,
sum((case when C=2 then 1 else 0 end)*A) as sumA2,
sum((case when C=2 then 1 else 0 end)*B) as sumB2,
sum((case when C=3 then 1 else 0 end)*A) as sumA3,
sum((case when C=3 then 1 else 0 end)*B) as sumB3,
sum((case when C=4 then 1 else 0 end)*A) as sumA4,
sum((case when C=4 then 1 else 0 end)*B) as sumB4
from Table1
group by C如果C的取值不确定怎么办?只好定义一个字符串变量,动态的生成上面的语句,然后用execsql来执行。
select d,sum(choose case c when 3 then a else 0) ca3 ,sum(choose case c when 3 then b else 0) cb3,
sum(choose case c when 4 then a else 0) ca4 ,sum(choose case c when 4 then b else 0) cb4
from (select sum(A) as A,sum(B) as B,C,D from Table1 Group by C,D ) a
group by d
wujinbao(飞扬草),能不能讲讲你的D字段实干什么用的?
sumA1 sumB1 sumA2 sumB2
----------- ----------- ----------- -------
2 3 0 0
0 0 3 23
0 0 0 0
0 0 0 0
能不能将统计结果为0的去掉呢?得到?
sumA1 sumB1 sumA2 sumB2
----------- ----------- ----------- -------
2 3 3 23
sum(case when C=1 then B else 0 end) as sumB1,
sum(case when C=2 then A else 0 end) as sumA2,
sum(case when C=2 then B else 0 end) as sumB2,
sum(case when C=3 then A else 0 end) as sumA3,
sum(case when C=3 then B else 0 end) as sumB3,
sum(case when C=4 then A else 0 end) as sumA4,
sum(case when C=4 then B else 0 end)*B) as sumB4
from Table1
group by D
DECLARE @dev varchar(2), @ValueSQL varchar(220), @name varchar(2),
@newname varchar(200) DECLARE cur_test CURSOR FOR SELECT dev
FROM test
GROUP BY dev OPEN cur_test FETCH
next
FROM cur_test
INTO @dev
SET @valuesql = 'select dev,name from test where 1=0' WHILE @@fetch_status = 0 BEGIN DECLARE
cur_value CURSOR FOR
SELECT dev, name
FROM test
WHERE dev = @dev OPEN cur_value FETCH next
FROM cur_value
INTO @dev,@name
SET @newname = '' WHILE @@fetch_status = 0 BEGIN
SET @newname = @newname + ' ' + @name FETCH next
FROM cur_value
INTO @dev,@name END
SET @valuesql = @valuesql + ' union select ''' + @dev + ''' as dev,''' + @newname + ''' as name' CLOSE cur_value DEALLOCATE
cur_value
fetch next from cur_test into @dev
END
close cur_test
deallocate cur_test
set @valuesql = @valuesql + ' order by dev'
exec(@valuesql)
就好比D字段为单位代码,A字段表示数量,B字段表示金额,C字段表示售出、退回、报废..
大概就是这样了!
打个比方D类似于单位代码或名称,A 就是数量 ,B就是金额,C可取值有出库,退回、丢失等等了,大概就是这个意思了。
改进意见:定义游标的select语句是不是应该用distinct?