|
本帖最后由 JLxiangwei 于 2013-10-7 20:48 编辑
论坛很久没有sql题目了,出一个小题目,大家试试看。
发现练习题大家参与度不够,积极性不高,提前公布答案吧。- 解法1:
- select format(datepart('ww',a.日期),"第0周") as 周数,
- dsum("用户数","[sheet1$A:B]","datepart('ww',日期)<="&datepart('ww',a.日期)) as 用户累计
- from [sheet1$A:B]a
- group by datepart('ww',a.日期)
- ====================================================================================================
- 解法2:
- select "第"&周数&"周" as 周数,first(累计) as 用户累计
- from (SELECT datepart("ww",日期) as 周数,
- (select sum(用户数)
- from [sheet1$]
- where datepart("ww",日期)<=datepart("ww",a.日期)) as 累计
- FROM [sheet1$] a)
- group by 周数
- ====================================================================================================
- 解法3:
- select "第"&b.周数&"周" as 周数,sum(a.用户数) as 用户累计
- from (select datepart("ww",日期) as 周数,sum(用户数) as 用户数
- from [sheet1$]
- group by datepart("ww",日期)) a
- left join (select datepart("ww",日期) as 周数
- from [sheet1$]
- group by datepart("ww",日期)) b
- on a.周数<=b.周数
- group by b.周数
- ====================================================================================================
- 解法4:
- select "第"&b.周数&"周" as 周数,sum(a.用户数) as 用户累计
- from (select 周数,sum(用户数) as 用户数
- from (select datepart("ww",日期) as 周数,用户数
- from [sheet1$])
- group by 周数) a,
- (select 周数,sum(用户数) as 用户数
- from (select datepart("ww",日期) as 周数,用户数
- from [sheet1$])
- group by 周数) b
- where a.周数<=b.周数
- group by b.周数
- ====================================================================================================
- 解法5:
- select "第" & a.日期 & "周" as 周数 ,
- (select sum(用户数)
- from (select datepart("ww",日期,1) as 日期, sum(用户数) as 用户数
- from [Sheet1$]
- group by datepart("ww",日期,1)) b
- where b.日期<=a.日期 ) as 用户累计
- from (select datepart("ww",日期,1) as 日期,sum(用户数) as 用户数
- from [Sheet1$]
- group by datepart("ww",日期,1)) a
-
-
复制代码 |
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有帐号?注册
x
评分
-
查看全部评分
|