再问分段数值范围查询,请帮忙!!!
我用这个查询可以查出结果
SELECT iif(时长秒<=3,"0:3",iif((时长秒>3 and 时长秒<=6),"3:6",iif((时长秒>6 and 时长秒<=15),"6:15",iif((时长秒>15 and 时长秒<=30),"15:30",iif((时长秒>30),">30"))))) AS range,count(1) as count
FROM biao1 group by iif(时长秒<=3,"0:3",iif((时长秒>3 and 时长秒<=6),"3:6",iif((时长秒>6 and 时长秒<=15),"6:15",iif((时长秒>15 and 时长秒<=30),"15:30",iif((时长秒>30),">30")))))
我的表是这样的:
日期 时长秒
1 2
1 3
1 5
2 8
2 1
3 9
3 6
需要的结果是,每天,时长在"0:3","3:6","6:15","15:30",">30"的个数.
我按以上查询,可以查出不同范围的个数,但问题有两个:
1,查出来,我想要的结果是行,我想要字段,列的
如,现在查出来
range count
"0:3" 3
" 3:6" 2
......
需要的结果
"0:3" "3:6"
3 2 .......
2,我怎么将日期分离出来?现在这样的结果是所有日期的和
需要的结果
日期 "0:3" "3:6"
1 2 1
2 1 0..........