SQL实践与解析

字数 2991阅读 2118评论 0赞 0

SQL实践
1         表转置
前些阵子，碰到朋友问起表转置的事情。鉴于表转置比较常用而且非常重要，因此将它编撰成文供参考。

<TABLE class=MsoTableGrid style="BORDER-RIGHT: medium none; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; BORDER-COLLAPSE: collapse; mso-border-alt: solid windowtext .5pt; mso-yfti-tbllook: 480; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-border-insideh: .5pt solid windowtext; mso-border-insidev: .5pt solid windowtext" cellSpacing=0 cellPadding=0 border=1>
<TBODY>
<TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-yfti-lastrow: yes">
<TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 426.1pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .5pt" vAlign=top width=568>
纵表表结构：
CREATE TABLE "WANGYAX "."TRADE"  (
                  "PERSON" VARCHAR(15) ,
                  "REGION" VARCHAR(15) ,
                  "TRADE" INTEGER );
纵表数据：
insert into  "WANGYAX "."TRADE" values
 ('GOUNOT','Manitoba',15),
 ('GOUNOT','Ontario-North',1),
 ('GOUNOT','Ontario-South',10),
 ('GOUNOT','Quebec',24),
 ('LEE','Manitoba',23),
 ('LEE','Ontario-North',8),
 ('LEE','Ontario-South',34),
 ('LEE','Quebec',26),
 ('LUCCHESSI','Manitoba',3),
 ('LUCCHESSI','Ontario-South',8),
 ('LUCCHESSI','Quebec',3);
横表表结构：
CREATE TABLE "WANGYAX "."REGION_TRADE"  (
                  "PERSON" VARCHAR(15) ,
                  "TRADE1" INTEGER ,
                  "TRADE2" INTEGER , 
                  "TRADE3" INTEGER ,
                  "TRADE4" INTEGER );
横表数据：
insert into region_trade values
 ('GOUNOT',15,1,10,24),
 ('LEE',23,8,34,26),
 ('LUCCHESSI',3,cast(null as int),8,3);</TD></TR></TBODY></TABLE>
 
1.1    纵表转横表

<TABLE class=MsoTableGrid style="BORDER-RIGHT: medium none; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; BORDER-COLLAPSE: collapse; mso-border-alt: solid windowtext .5pt; mso-yfti-tbllook: 480; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-border-insideh: .5pt solid windowtext; mso-border-insidev: .5pt solid windowtext" cellSpacing=0 cellPadding=0 border=1>
<TBODY>
<TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-yfti-lastrow: yes">
<TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 426.1pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .5pt" vAlign=top width=568>
select
 person,
 sum(case when region = 'Manitoba' then trade end),
 sum(case when region = 'Ontario-North' then trade end),
 sum(case when region = 'Ontario-South' then trade end),
 sum(case when region = 'Quebec' then trade end)
 from trade
 group by person
 order by person
 
结果：
 
<IMG id=_uchome_localimg_0 src=""></TD></TR></TBODY></TABLE>
1.2    横表转纵表

<TABLE class=MsoTableGrid style="BORDER-RIGHT: medium none; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; BORDER-COLLAPSE: collapse; mso-border-alt: solid windowtext .5pt; mso-yfti-tbllook: 480; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-border-insideh: .5pt solid windowtext; mso-border-insidev: .5pt solid windowtext" cellSpacing=0 cellPadding=0 border=1>
<TBODY>
<TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-yfti-lastrow: yes">
<TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 426.1pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .5pt" vAlign=top width=568>
select
  r.person,
  t.region,
  t.trade
 from region_trade as r,
      lateral(values('Manitoba',r.trade1),
                    ('Ontario-North',r.trade2),
                    ('Ontario-South',r.trade3),
                    ('Quebec',r.trade4))
              as t(region,trade)
  order by r.person,t.region
 
结果：
<IMG id=_uchome_localimg_1 src="">
</TD></TR></TBODY></TABLE>
 
 
 <div class="uchome-message-pic"><a class="example-image-link" href="/home/attachment/201211/2/81395_1351871822nnZ8.png" data-lightbox="example-set"><img class="example-image" src="/home/attachment/201211/2/81395_1351871822nnZ8.png" ></a>结果1</div><div class="uchome-message-pic"><a class="example-image-link" href="/home/attachment/201211/2/81395_1351871823SYGB.png" data-lightbox="example-set"><img class="example-image" src="/home/attachment/201211/2/81395_1351871823SYGB.png" ></a>结果2</div>

sql实践

著作权归作者所有

如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！

添加新评论0 条评论

Ctrl+Enter 发表

匿名评论

SQL实践与解析

添加新评论0 条评论

作者其他文章