数据透视表中级班练习五

JLxiangwei · 发表于 2014-5-19 13:58

本帖最后由 JLxiangwei 于 2014-7-7 20:51 编辑

大家顶住，题目又来了

SQL练习之按照号码段统计个数.rar (9.72 KB, 下载次数: 109)

云影 · 发表于 2014-5-19 14:24

本帖最后由云影于 2014-5-23 15:04 编辑

SELECT 分类,min(号码) &"-"&max(号码) as 号段, COUNT(*) AS 号码个数 FROM [Data$] group by 分类,号码-dcount("号码","[Data$]","分类='" & 分类 &"' and 号码 < " & 号码 )

复制代码

天空的雨 · 发表于 2014-5-19 15:41

本帖最后由天空的雨于 2014-5-20 14:31 编辑

SQL练习之号码段-天空的雨答.rar (13.89 KB, 下载次数: 7)

xdragon · 发表于 2014-5-19 15:48

本帖最后由 xdragon 于 2014-5-20 15:26 编辑

select 分类,min(号码)&'-'&max(号码) as 号段,count(号码) as 号码个数 from (select t.分类,t.号码,(select count(*) from [Data$] where 号码<=t.号码) as cc from [Data$] t order by t.分类,t.号码) group by 分类,号码-cc

复制代码

有点混乱，不过凑出来了。。。

车仁静 · 发表于 2014-5-21 17:23

SELECT 分类,MIN(号码)&"-"& MAX(号码) AS 号段,COUNT(号码) AS 号码个数
FROM [Data$]
GROUP BY 分类,PARTITION(号码,1,2000,5)

复制代码

xiaoni · 发表于 2014-5-21 20:23

SELECT 分类,"1001-1005" as 号段,count(号码) as 号码个数 from [data$a:b] where 号码 between 1001 and 1005 group by 分类 UNION ALL SELECT 分类,"1007-1009" as 号段,count(号码) as 号码个数 from [data$a:b] where 号码 between 1007 and 1009 group by 分类 union all SELECT 分类,"1011-1013" as 号段,count(号码) as 号码个数 from [data$a:b] where 号码 between 1011 and 1013 group by 分类 union all SELECT 分类,"1023-1024" as 号段,count(号码) as 号码个数 from [data$a:b] where 号码 between 1023 and 1024 group by 分类

箫风 · 发表于 2014-5-22 07:35

本帖最后由箫风于 2014-5-22 11:18 编辑

游客，如果您要查看本帖隐藏内容请回复

lasharks · 发表于 2014-5-22 21:06

先上一个，有空再改

select 分类,min(号码) & "-" & max(号码) as 号段,count(*) as 号码个数 from (select a.*,a.号码-a.排名 as 差值 from (select 分类,号码,dcount("号码","data$a1:b14","号码<="&号码) as 排名 from [data$a1:b14]a order by 3 asc)a) group by 分类,差值

复制代码

zx_wl · 发表于 2014-5-27 11:09

本帖最后由 zx_wl 于 2014-7-10 16:01 编辑

SELECT 分类,MIN(号码)&"-"&MAX(号码) AS 号段,COUNT(*) AS 号码个数 FROM(select 分类,号码,号码-(SELECT COUNT(*) FROM [DATA$] T2 WHERE T2.号码<T1.号码) AS 分组 FROM [DATA$] T1) GROUP BY 分类,分组

复制代码

jio1ye · 发表于 2014-7-6 12:25

我的神啊，这语句也太啰里吧嗦咯，做出来不容易啊

Select 分类,"1001-1005"as 号段, count(分类) as 号码个数 from [Data$] where 号码 between 1001 and 1005 group by 分类 union (Select 分类,"1007-1009"as 号段, count(分类) as 号码个数 from [Data$] where 号码 between 1007 and 1009 group by 分类) union (Select 分类,"1011-1013"as 号段, count(分类) as 号码个数 from [Data$] where 号码 between 1011 and 1013 group by 分类) union (Select 分类,"1023-1024"as 号段, count(分类) as 号码个数 from [Data$] where 号码 between 1023 and 1024 group by 分类)

复制代码

		自动登录	找回密码
密码			注册