ExcelTip.Net留存知识帖 ---【注：附件之前被网盘供应商清空后，现已修复-现已修复-现已修复为本地下载！】
现在位置：首页 > 我的测验 > SQL in Excel > SQL测试——另类行列转置

SQL测试——另类行列转置

作者：绿色风 分类： 时间：2022-08-18 浏览：155
楼主
wise		 SQL测试——行列转置:

注：本题答题截止到2010年1月20日0：00。（提前20分钟公布答案）

题目背景：09年公司某职员从电信IT部那边得到了一批和Sheet1中那样形式（如图一）的excel数据，数量超过了2W条，但是前几次提取过来的数据都是和图二中那样形式的数据，导致该职员无法用正常的SQL语句对后面提取过来的数据进行关联，不知道社区中面哪个高手能帮这个小职员的忙呢？（本测试题只是截取了其中的一小部分数据，完整的数据有2W多行和几十列字段。）


题目要求：把Sheet1表中（如图一）的数据用SQL语句转置成图二中的形式


图一

 


图二

 




【要求】：
1、只用基本操作里面的SQL完成。
2、不能使用函数、VBA。
3、答题时请上传附件。
【评分】：
1、写出1个正确的SQL语句得2个技能分；
2、写出2个或者2个以上的正确的SQL语句得4个技能分；
3、写出具体的SQL语句说明，可得1个技能分；
4、精彩答案可以再获得1~4技能分。


SQL测试—行列另类转置.rar
2楼
pc520		 代码1：
SELECT T1.ID, T1.ARPU值 AS 1 月ARPU值, T1.达标程度 AS 1 月达标程度,
      T2.ARPU值 AS 2 月ARPU值, T2.达标程度 AS 2 月达标程度,
      T3.ARPU值 AS 3 月ARPU值, T3.达标程度 AS 3 月达标程度
FROM (SELECT DISTINCT ID, ARPU值, 达标程度
        FROM [Sheet1$]
        WHERE (月份 = '1月')) T1,
          (SELECT DISTINCT ID, ARPU值, 达标程度
         FROM [Sheet1$]
         WHERE (月份 = '2月')) T2,
          (SELECT DISTINCT ID, ARPU值, 达标程度
         FROM [Sheet1$]
         WHERE (月份 = '3月')) T3
WHERE T1.ID = T2.ID AND T2.ID = T3.ID


代码2：
TRANSFORM Last(T .ARPU值) SELECT T .ID
                                FROM (SELECT ID, 月份 & 'ARPU值' AS 月份, ARPU值
                                        FROM [Sheet1$]
                                        UNION
                                        SELECT ID, 月份 & '达标程度' AS 月份, 达标程度
                                        FROM [Sheet1$]) T
                                GROUP BY T .ID PIVOT T .月份

说明：
代码1：用三个子查询，分别查询出三个月的数值，然后用ID做关键字连接三个表，做多表查询。
代码2：用两个子查询，把月份分别加上'ARPU值'和'达标程度' ，用UNION连接起来，外面套一个交叉查询

代码1是拿到题目就想到的，代码2是想了好久的，不过通过这个题的练习，自己也多学到了一点，呵呵，谢谢老师的题。
3楼
MasterExcel		 请见附件。
SQL测试—行列另类转置.rar
4楼
棉花糖
  1. SELECT a.ID, a.ARPU值 AS 1月ARPU值, a.达标程度 AS 1月达标程度, b.ARPU值 AS 2月ARPU值, b.达标程度 AS 2月达标程度, c.ARPU值 AS 3月ARPU值, c.达标程度 AS 3月达标程度
  2. FROM [Sheet1$] a, [Sheet1$] b, [Sheet1$] c
  3. WHERE a.ID = b.ID AND a.ID = c.ID AND ((a.月份='1月') AND (b.月份='2月') AND (c.月份='3月'))


 

SQL测试—行列另类转置.rar
5楼
棉花糖
  1. SELECT a.ID, a.ARPU值 AS 1月ARPU值, a.达标程度 AS 1月达标程度, b.ARPU值 AS 2月ARPU值, b.达标程度 AS 2月达标程度, c.ARPU值 AS 3月ARPU值, c.达标程度 AS 3月达标程度
  2. FROM [Sheet1$] a, [Sheet1$] b, [Sheet1$] c
  3. WHERE a.ID = b.ID AND a.ID = c.ID AND ((a.月份='1月') AND (b.月份='2月') AND (c.月份='3月'))


 

SQL测试—行列另类转置.rar
6楼
皮皮1998		 不知是不是这意思，先交了再说！

加了一些类似的语句,小7的分好难得哦!


SQL测试—行列另类转置1.rar
7楼
amulee		 就写一句吧，写了些说明。

SQL测试—行列另类转置.rar
8楼
wise		 精彩答案（pc520），该答案对付12个月都没有问题，并且用得那个Last 和 TRANSFORM 用得比较巧妙。
  1. TRANSFORM Last(T.ARPU值) SELECT T.ID FROM (SELECT ID, 月份 & 'ARPU值' AS 月份, ARPU值 FROM [Sheet1$] UNION  SELECT ID, 月份 & '达标程度' AS 月份, 达标程度 FROM [Sheet1$]) T GROUP BY T.ID PIVOT T.月份

另外皮皮和阿木的答案都是用TRANSFORM 来进行答题的，本题考察的目的也是TRANSFORM，这两位答题写得解释比较详细，想看一下TRANSFORM 解法的人可以下载他们的附件来看看。
皮皮的答案：
  1. transform SUM(ARPU值)  select ID FROM
  2. (select ID,月份&'ARPU值' as ww,ARPU值  from [Sheet1$] union all select id,月份&'达标程度' as ww,iif(达标程度="达标",0,-1) as ARPU值  from [Sheet1$])
  3. group by id  pivot ww

阿木的答案：
  1. TRANSFORM iif(SUM(值)=-1,"达标",iif(sum(值)=-2,"不达标",sum(值))) select ID from
  2. (select ID,月份,ARPU值 as 值,"ARPU值" as 类型 from [sheet1$] union all select ID,月份,iif(达标程度="达标",-1,-2) as 值,"达标程度" as 类型 from [sheet1$] order by ID,月份,类型) group by ID PIVOT 月份&类型

对于M兄和冻大师、pc520的第一种解法，这几个都是用了嵌套查询，先分别找出每月的相应的值，然后对ID进行关联，对于多数据量，不停地查询数据再连接，会导致运行速度比较慢，主要是语句相对较多：
pc520

  2. SELECT T1.ID, T1.ARPU值 AS 1 月ARPU值, T1.达标程度 AS 1 月达标程度,
  3. T2.ARPU值 AS 2 月ARPU值, T2.达标程度 AS 2 月达标程度,
  4. T3.ARPU值 AS 3 月ARPU值, T3.达标程度 AS 3 月达标程度
  5. FROM (SELECT DISTINCT ID, ARPU值, 达标程度
  6. FROM [Sheet1$]
  7. WHERE (月份 = '1月')) T1,
  8. (SELECT DISTINCT ID, ARPU值, 达标程度
  9. FROM [Sheet1$]
  10. WHERE (月份 = '2月')) T2,
  11. (SELECT DISTINCT ID, ARPU值, 达标程度
  12. FROM [Sheet1$]
  13. WHERE (月份 = '3月')) T3
  14. WHERE T1.ID = T2.ID AND T2.ID = T3.ID

M兄
  1. select
  2. a.*,
  3. [2月ARPU值],
  4. [2月达标程度],
  5. [3月ARPU值],
  6. [3月达标程度]
  7. from
  8. (
  9. select
  10. ID,
  11. ARPU值 as [1月ARPU值],
  12. 达标程度 as [1月达标程度]
  13. from
  14. [Sheet1$a1:d10]
  15. where
  16. 月份='1月'
  17. )a,
  18. (
  19. select
  20. ID,
  21. ARPU值 as [2月ARPU值],
  22. 达标程度 as [2月达标程度]
  23. from
  24. [Sheet1$a1:d10]
  25. where
  26. 月份='2月'
  27. )b,
  28. (
  29. select
  30. ID,ARPU值
  31. as [3月ARPU值],
  32. 达标程度 as [3月达标程度]
  33. from
  34. [Sheet1$a1:d10]
  35. where
  36. 月份='3月'
  37. )c
  38. where
  39. a.ID=b.ID
  40. and
  41. a.ID=c.ID
  1. select
  2. c.*,
  3. [3月ARPU值],
  4. [3月达标程度]
  5. from
  6. (
  7. select
  8. a.*,
  9. [2月ARPU值],
  10. [2月达标程度]
  11. from
  12. (
  13. select
  14. ID,
  15. ARPU值 as [1月ARPU值],
  16. 达标程度 as [1月达标程度]
  17. from
  18. [Sheet1$a1:d10]
  19. where
  20. 月份='1月'
  21. )a
  22. left join
  23. (
  24. select
  25. ID,
  26. ARPU值 as [2月ARPU值],
  27. 达标程度 as [2月达标程度]
  28. from
  29. [Sheet1$a1:d10]
  30. where
  31. 月份='2月'
  32. )b
  33. on
  34. a.ID=b.ID
  35. )c
  36. left join
  37. (
  38. select
  39. ID,
  40. ARPU值 as [3月ARPU值],
  41. 达标程度 as [3月达标程度]
  42. from
  43. [Sheet1$a1:d10]
  44. where
  45. 月份='3月'
  46.   )d
  47. on
  48. c.ID=d.ID

冻大师的
  1. SELECT a.ID, a.ARPU值 AS 1月ARPU值, a.达标程度 AS 1月达标程度, b.ARPU值 AS 2月ARPU值, b.达标程度 AS 2月达标程度, c.ARPU值 AS 3月ARPU值, c.达标程度 AS 3月达标程度

  3. FROM [Sheet1$] a, [Sheet1$] b, [Sheet1$] c

  5. WHERE a.ID = b.ID AND a.ID = c.ID AND ((a.月份='1月') AND (b.月份='2月') AND (c.月份='3月'))


另外，我增加多一种解法，我这种解法也是很长，不要见笑：
  1. select ID,
  2. max(iif(月份='1月',ARPU值,null)) as 1月ARPU值,
  3. max(iif(月份='1月',达标程度,null)) as 1月达标程度,
  4. max(iif(月份='2月',ARPU值,null)) as 2月ARPU值,
  5. max(iif(月份='2月',达标程度,null)) as 2月达标程度,
  6. max(iif(月份='3月',ARPU值,null)) as 3月ARPU值,
  7. max(iif(月份='3月',达标程度,null)) as 3月达标程度
  8. from [Sheet1$]
  9. group by ID
9楼
toby08		 好题!赞一个呵。再来鸡蛋里挑骨头一下。

pc520第二个SQL语句存在一点不足，就是：经过这一轮查询后，原来数值类型的ARPU值变成文本类型了。（不过报表本来就是耐看的，最后的结果是领导想要的就OK啦）
不过对于这题，如果要保持数据的类型不变的话，要用到多个连接，也挺麻烦的。
小小意见，啰嗦了几句哈~~
10楼
wise
  1. SELECT ID, 月份 & 'ARPU值' AS 月份, ARPU值 FROM [Sheet1$] UNION  SELECT ID, 月份 & '达标程度' AS 月份, 达标程度 FROM [Sheet1$]
导致类型出现改变的原因主要是在这段代码，这里是连接之后，因为数值和文本类型都混在一列，导致了这个问题，toby08兄提醒了，这个也是最佳答案的一点不足。
如果考虑类型，看来只能这个答案比较好写一点了：
  1. select ID,
  2. max(iif(月份='1月',ARPU值,null)) as 1月ARPU值,
  3. max(iif(月份='1月',达标程度,null)) as 1月达标程度,
  4. max(iif(月份='2月',ARPU值,null)) as 2月ARPU值,
  5. max(iif(月份='2月',达标程度,null)) as 2月达标程度,
  6. max(iif(月份='3月',ARPU值,null)) as 3月ARPU值,
  7. max(iif(月份='3月',达标程度,null)) as 3月达标程度
  8. from [Sheet1$]
  9. group by ID
11楼
MasterExcel		 pc520的代码经典。学习！transform还是前些天才知道的，刚用过了。

学习！
12楼
eqzh		 请求斑竹，能否详细解释一下该句的作用：
max(iif(月份='1月',ARPU值,null)) as 1月ARPU值,
13楼
xinger7		 真是山外青山人外楼呐！一山更比一山高
14楼
wxin		 标个记号，认真学习下
15楼
bluexuemei		 这样也不完美，无法实现动态的行列转置。
16楼
qazwer168		 路过!!!!!!

免责声明

有感于原ExcelTip.Net留存知识的价值及部分知识具有的时间限定性因素， 经与ExcelTip.Net站长Apolloh商议并征得其同意， 现将原属ExcelTip.Net的知识帖采集资料于本站点进行展示， 供有需要的人士查询使用，也慰缅曾经的论坛时代。 所示各个帖子的原作者如对版权有异议， 可与本人沟通提出，或于本站点留言，我们会尽快处理。 在此，感谢ExcelTip.Net站长Apolloh的支持，感谢本站点所有人**绿色风（QQ:79664738)**的支持与奉献，特此鸣谢！
------本人网名**KevinChengCW（QQ:1210618015)**原ExcelTip.Net总版主之一

猜您喜欢
评论列表
搜索

鄂ICP备2021011689号-2

powered by emlog pro Theme By 蓝叶

sitemap