a b c
aaa1 23.2 30
aba43 48.2 40
aaa1 66.2 67
ada1 99.2 100
aaaa1 123.2 130
aza1 163.2 170
agga1 113.2 120
assa1 144.2 150
aeea1 223.2 230
a22a1 213.2 230
adda1 333.2 350
agsa1 623.2 630
arra1 613.2 620
aqoo1 823.2 830
aarr 1123.2 1130
aa441 3323.2 3330
有一个表A有3列a,b,c统计当b<100时,把b加起来,c加起来,并统计有多少个小于100的,
同样当b>100小于200时,把b加起来,c加起来,并统计有多少个大于100小于200的,
……一直累计以100有分界
结果:
b<100 4 236.8 237
b>100 b<200 4 543.8 570
b>200 b<300 2 436.4 460
b>300 1 333.2 350
b>600b<700 2 1236.4 1250
b>800b<900 1 823.2 830
b>1000b<1100 1 1123.2 1130
b>3300b<3400 1 3323.2 3330
请问各位一个问题,谢谢
aaa1 23.2 30
aba43 48.2 40
aaa1 66.2 67
ada1 99.2 100
aaaa1 123.2 130
aza1 163.2 170
agga1 113.2 120
assa1 144.2 150
aeea1 223.2 230
a22a1 213.2 230
adda1 333.2 350
agsa1 623.2 630
arra1 613.2 620
aqoo1 823.2 830
aarr 1123.2 1130
aa441 3323.2 3330
有一个表A有3列a,b,c统计当b<100时,把b加起来,c加起来,并统计有多少个小于100的,
同样当b>100小于200时,把b加起来,c加起来,并统计有多少个大于100小于200的,
……一直累计以100有分界
结果:
b<100 4 236.8 237
b>100 b<200 4 543.8 570
b>200 b<300 2 436.4 460
b>300 1 333.2 350
b>600b<700 2 1236.4 1250
b>800b<900 1 823.2 830
b>1000b<1100 1 1123.2 1130
b>3300b<3400 1 3323.2 3330
请问各位一个问题,谢谢
一串between ....and
select count(1), sum(b), sum(c)
from a
where b<100;
/*
-- Author:SQL77--RICHIE
-- Version:V1.001 Date:2008-05-15--转Flystone*/-- Test Data: TB
If object_id('TB') is not null
Drop table TB
Go
Create table TB(a varchar(20),b numeric(18,2),c int)
Go
Insert into TB
select 'aaa1',23.2,30 union all
select 'aba43',48.2,40 union all
select 'aaa1',66.2,67 union all
select 'ada1',99.2,100 union all
select 'aaaa1',123.2,130 union all
select 'aza1',163.2,170 union all
select 'agga1',113.2,120 union all
select 'assa1',144.2,150 union all
select 'aeea1',223.2,230 union all
select 'a22a1',213.2,230 union all
select 'adda1',333.2,350 union all
select 'agsa1',623.2,630 union all
select 'arra1',613.2,620 union all
select 'aqoo1',823.2,830 union all
select 'aarr',1123.2,1130 union all
select 'aa441',3323.2,3330
Go
--Start
SELECT
'B>='+LTRIM(NUM)+' AND B<'+LTRIM(NUM+100)+'' AS '范围',
COUNT(*) AS 个数 ,
SUM(B)B,
SUM(C)AS C
FROM (SELECT (CONVERT(INT,B)/100)*100 AS NUM ,B,C FROM TB ) AS T GROUP BY NUM范围 个数 B C
---------------------------------- ----------- ---------------------------------------- -----------
B>=0 AND B<100 4 236.80 237
B>=100 AND B<200 4 543.80 570
B>=200 AND B<300 2 436.40 460
B>=300 AND B<400 1 333.20 350
B>=600 AND B<700 2 1236.40 1250
B>=800 AND B<900 1 823.20 830
B>=1100 AND B<1200 1 1123.20 1130
B>=3300 AND B<3400 1 3323.20 3330(所影响的行数为 8 行)