关于按分数段统计的动态sql问题！！！！！
首先说明是 sql2k 不是sql2005
现有学生成绩库0-100分
每10分为一段
或者每5分为一段，进行统计
分数段 人数 累计人数
[0-10] 20 20
[10-20] 30 50
[20-30] 40 90
[30-40] 50 140
...
0-10的意思是 0=<分数<10
10-20 的意思是 10<=分数<20
我知道可以写成这样
select sum(case when 分数>=0 and 分数<10 then 1 else 0 end) '0-10',
....
但是现在想能够自动分分数段写成动态的语句怎么写。也就是给定一个参数比如10，就按10分一段，如果是5，就按5分一段，如果是20，就按20分一段。
oracle很容易实现，在sql2k里怎么实现，谢谢。
[解决办法]
declare @sql varchar(8000)set @sql=''declare @i int;set @i=5select @sql=@sql+'sum(case when 分数>='+ltrim(number) +' and 分数<'+ltrim(number+@i)+ ' then 1 else 0 end) as ''['+ltrim(number)+'-'+ltrim(number+@i)+']'', ' from master..spt_valueswhere type='p' and number%@i=0and number between 0 and 95select 'select '+left(@sql,len(@sql)-1)+' from tablename'
[解决办法]
declare @sql varchar(8000)declare @i intset @sql=''set @i=10select @sql=@sql+'sum(case when 分数>='+ltrim(@i*number) +' and 分数<'+ltrim(@i*number+@i)+ ' then 1 else 0 end) as ''['+ltrim(@i*number)+'-'+ltrim(@i*number+@i)+']'', ' from master..spt_valueswhere type='p' and @i*number<100select @sql='select '+stuff(@sql,len(@sql),1,'')+' from tb'print(@sql)
[解决办法]
create table tb(score int)insert into tbselect 6 union allselect 11 union allselect 56 union allselect 37 union allselect 34 union allselect 87 union allselect 14 union allselect 66 union allselect 92 union allselect 41 union allselect 25 union allselect 10godeclare @sql varchar(8000)declare @i intset @i=5select @sql=isnull(@sql+',','')+'sum(case when score>='+ltrim(number) +' and score<'+ltrim(number+@i)+ ' then 1 else 0 end) as ['+ltrim(number)+'-'+ltrim(number+@i)+']'from master..spt_valueswhere type='p' and number%@i=0and number between 0 and 95select @sql = 'select '+@sql+' from tb'exec(@sql)drop table tb/***********0-5 5-10 10-15 15-20 20-25 25-30 30-35 35-40 40-45 45-50 50-55 55-60 60-65 65-70 70-75 75-80 80-85 85-90 90-95 95-100----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------0 1 3 0 0 1 1 1 1 0 0 1 0 1 0 0 0 1 1 0(1 行受影响)
[解决办法]
create table tb(score int)insert into tbselect 6 union allselect 11 union allselect 56 union allselect 37 union allselect 34 union allselect 87 union allselect 14 union allselect 66 union allselect 92 union allselect 41 union allselect 25 union allselect 950godeclare @str1 varchar(8000)declare @str2 varchar(8000)declare @i intset @i=5select @str1=isnull(@str1+',','')+'sum(case when score>='+ltrim(number) +' and score<'+ltrim(number+@i)+ ' then 1 else 0 end) as ['+ltrim(number)+'-'+ltrim(number+@i)+']'from master..spt_valueswhere type='p' and number%@i=0and number between 0 and 550select @str2=isnull(@str2+',','')+'sum(case when score>='+ltrim(number) +' and score<'+ltrim(number+@i)+ ' then 1 else 0 end) as ['+ltrim(number)+'-'+ltrim(number+@i)+']'from master..spt_valueswhere type='p' and number%@i=0and number between 550+@i and 950print len(@str1) --看字符串长度print len(@str1)/(550/@i) --看每个分段用的字符串长度print 950/@i*70 --看按950分算@i长度分段用的总字符串长度exec('select '+@str1+','+@str2+' from tb')drop table tb/********************7796 --@str1长度70 --每段长度13300 --总长度0-5 5-10 10-15 15-20 20-25 25-30 30-35 35-40 40-45 45-50 50-55 55-60 60-65 65-70 70-75 75-80 80-85 85-90 90-95 95-100 100-105 105-110 110-115 115-120 120-125 125-130 130-135 135-140 140-145 145-150 150-155 155-160 160-165 165-170 170-175 175-180 180-185 185-190 190-195 195-200 200-205 205-210 210-215 215-220 220-225 225-230 230-235 235-240 240-245 245-250 250-255 255-260 260-265 265-270 270-275 275-280 280-285 285-290 290-295 295-300 300-305 305-310 310-315 315-320 320-325 325-330 330-335 335-340 340-345 345-350 350-355 355-360 360-365 365-370 370-375 375-380 380-385 385-390 390-395 395-400 400-405 405-410 410-415 415-420 420-425 425-430 430-435 435-440 440-445 445-450 450-455 455-460 460-465 465-470 470-475 475-480 480-485 485-490 490-495 495-500 500-505 505-510 510-515 515-520 520-525 525-530 530-535 535-540 540-545 545-550 550-555 555-560 560-565 565-570 570-575 575-580 580-585 585-590 590-595 595-600 600-605 605-610 610-615 615-620 620-625 625-630 630-635 635-640 640-645 645-650 650-655 655-660 660-665 665-670 670-675 675-680 680-685 685-690 690-695 695-700 700-705 705-710 710-715 715-720 720-725 725-730 730-735 735-740 740-745 745-750 750-755 755-760 760-765 765-770 770-775 775-780 780-785 785-790 790-795 795-800 800-805 805-810 810-815 815-820 820-825 825-830 830-835 835-840 840-845 845-850 850-855 855-860 860-865 865-870 870-875 875-880 880-885 885-890 890-895 895-900 900-905 905-910 910-915 915-920 920-925 925-930 930-935 935-940 940-945 945-950 950-955----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------0 1 2 0 0 1 1 1 1 0 0 1 0 1 0 0 0 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1(1 行受影响)