数据透视表中级班练习五
本帖最后由 JLxiangwei 于 2014-7-7 20:51 编辑大家顶住，题目又来了
注：号码段不是固定的，如果修改号码，号码段和个数会自动刷新 本帖最后由 云影 于 2014-5-23 15:04 编辑
SELECT 分类,min(号码) &"-"&max(号码) as 号段, COUNT(*) AS 号码个数 FROM group by 分类,号码-dcount("号码","","分类='" & 分类 &"' and 号码 < " & 号码 ) 本帖最后由 天空的雨 于 2014-5-20 14:31 编辑
重答：
本帖最后由 xdragon 于 2014-5-20 15:26 编辑
select 分类,min(号码)&'-'&max(号码) as 号段,count(号码) as 号码个数 from (select t.分类,t.号码,(select count(*) from where 号码<=t.号码) as cc from t order by t.分类,t.号码) group by 分类,号码-cc有点混乱，不过凑出来了。。。^:D SELECT 分类,MIN(号码)&"-"& MAX(号码) AS 号段,COUNT(号码) AS 号码个数
FROM
GROUP BY 分类,PARTITION(号码,1,2000,5) SELECT 分类,"1001-1005" as 号段,count(号码) as 号码个数 from where 号码 between 1001 and 1005 group by 分类 UNION ALL SELECT 分类,"1007-1009" as 号段,count(号码) as 号码个数 from where 号码 between 1007 and 1009 group by 分类 union all SELECT 分类,"1011-1013" as 号段,count(号码) as 号码个数 from where 号码 between 1011 and 1013 group by 分类 union all SELECT 分类,"1023-1024" as 号段,count(号码) as 号码个数 from where 号码 between 1023 and 1024 group by 分类 本帖最后由 箫风 于 2014-5-22 11:18 编辑
**** Hidden Message ***** 先上一个，有空再改select 分类,min(号码) & "-" & max(号码) as 号段,count(*) as 号码个数 from (select a.*,a.号码-a.排名 as 差值 from (select 分类,号码,dcount("号码","data$a1:b14","号码<="&号码) as 排名 from a order by 3 asc)a) group by 分类,差值 本帖最后由 zx_wl 于 2014-7-10 16:01 编辑
SELECT 分类,MIN(号码)&"-"&MAX(号码) AS 号段,COUNT(*) AS 号码个数 FROM(select 分类,号码,号码-(SELECT COUNT(*) FROM T2 WHERE T2.号码<T1.号码) AS 分组 FROM T1) GROUP BY 分类,分组 我的神啊，这语句也太啰里吧嗦咯，做出来不容易啊Select 分类,"1001-1005"as 号段, count(分类) as 号码个数 from where 号码 between 1001 and 1005 group by 分类 union (Select 分类,"1007-1009"as 号段, count(分类) as 号码个数 from where 号码 between 1007 and 1009 group by 分类) union (Select 分类,"1011-1013"as 号段, count(分类) as 号码个数 from where 号码 between 1011 and 1013 group by 分类) union (Select 分类,"1023-1024"as 号段, count(分类) as 号码个数 from where 号码 between 1023 and 1024 group by 分类)
页:
[1]
2