关于PIVOT(用于行转列)
公司有份业务表格,需要弄一个会员资料清单,但是查询了数据表,其他诸如生日/性别等信息,都是在一张表上记录,这个简单,直接用member_id进行就好,唯独手机号(mobile) 微信会员号(weixin_card_no)和一个叫openid(weixin_open_id)的数据 这部分数据是存在在另一张表格里面,且这三项数据都是在一个字段值下显示,并用ident_type字段进行区分
(其中,红框内容可以发现,同一个会员号,一行也只记录其中一个注册类型的信息)
为了达到报表的效果,所想的思路就是,如果这个数据可以以会员id为基础,进行一行显示,那问题就解决了
提到这个,第一反应就是行列转换的函数PIVOT,下面的函数解释来自微软MSDN的解释
SELECT <非透视的列>,[第一个透视的列] AS <列名称>,[第二个透视的列] AS <列名称>, ... ,[最后一个透视的列] AS <列名称> FROM (<生成数据的 SELECT 查询>) AS <源查询的别名> PIVOT ( <聚合函数>(<要聚合的列>) FOR [<包含要成为列标题的值的列>] IN ( [第一个透视的列], [第二个透视的列], ... ,[最后一个透视的列] ) ) AS <透视表的别名> <可选的 ORDER BY 子句>;
根据上述的实例,进行撸的脚本
select tenant_id,member_id,card_no,cellphone,open_id --第四步,行列进行转换了,查询语句的基本框架也有了,那我们要展示那些行列转换后的数据呢,这个就可以在这里进行,其中可以发现tenant_id,member_id这两列的本身就是存在于mb_tenant_member中,这列是没有疑问的,card_no,cellphone,open_id这三个是行列转换之后的新名称,因为我已经在第一步的时候进行了别名定义,因此这一步我就不需要再进行表别名定义,免得进行重复工作,另外,要记住,当第一步没有选择别名的时候,这里的card_no应该为'weixin_card_no',其他依次类推 from mb_tenant_member --第三步,我们在转换之后,总需要一个查询语句把他查询出来吧,这里其实就是写一个查询语句进行查询的 pivot ( max(ident_code) --第二步,在原始数据中,我们假设一个情况,就是在同一个会员ID之下,他每一个ident_code类型下的数据,都只有一条记录,也就是A会员的手机号码,有且只有一条(13800138000),我们默认他是不变的,如此一来就是简单的行转列,那按道理来说也就没有聚合函数什么事了,但是实际上,大部分时候都是需要一个聚合过程的,在这个案例中,类似手机号码这个,他是会随着用户的变更而增加的,因此,这里就有了一个聚合函数的出现 --聚合函数,是要告诉计算机,你在进行行列转换的时候,这些数据要怎样进行处理,是根据转换后的weixin_card_id值进行sum,count,max还是其他的呢 for ident_type in ('weixin_card_no' card_no,'mobile' cellphone,'weixin_open_id' open_id) --第一步,这是行转列中关键的一步,翻译一下这句话的意思,将ident_type的值作为列值,其中值为weixin_card_no的命名为card_no列,值为mobile的命名为cellphone列,值为weixin_open_id的命名为open_id列,其中,关于别名这个,你可以在这一步进行别名定义,也可以在第四步的时候进行别名定义,要是你觉得不需要,那也可以不进行别名定义,但是你要留意的是,当你在这一步不进行定义的时候,那么你第四步查询的时候,你的列名称是需要引号的 )
--注:pivot是跟在from表格的后面的 如果需要使用到where,having等语句,应该是接在 pivot()后面,也就是现在你看到这个位置的
得到的结果
这一步已经接近想要的结果了
我们把上面的语句变一下
select tenant_id,member_id,max(card_no) cardnum,max(cellphone) cellphone,max(open_id) openid from mb_tenant_member pivot( max(ident_code) for ident_type in ('weixin_card_no' card_no,'mobile' cellphone,'weixin_open_id' open_id)) group by tenant_id,member_id;
大功告成
