mysql交叉表查询列转行_SQL 转置计算

转置即旋转数据表的横纵方向，常用来改变数据布局，以便用新的角度观察。有些转置算法比较简单，比如行转列、列转行、双向转置；有些算法变化较多，比如动态转置、转置时跨行计算、关联转置等。这些转置算法对日常工作多有助益，值得我们学习讨论。

基础转置

行转列和列转行是最简单的转置算法，形式上互为逆运算，具体请看下面的问题及分析过程：

1.行转列：将销量分组表的quater字段里的值(行)Q1-Q4，转为新字段名(列)Q1-Q4，如下：yearquarteramount---->yearQ1Q2Q3Q4

year2018Q189year201889938899

year2018Q293year201992979088

year2018Q388

year2018Q499

year2019Q192

year2019Q297，

year2019Q390

year2019Q488

2.列转行：将销量交叉表的字段名Q1-Q4，转为新字段quarter里的值Q1-Q4，如下：YearQ1Q2Q3Q4---->yearquarteramount

year201889938899year2018Q189

year201992979088year2018Q293

year2018Q388

year2018Q499

year2019Q192

year2019Q297

year2019Q390

year2019Q488

早期SQL的解决方案

对于行转列，早期的SQL没有pivot之类的专用函数(MySQL、HSQLDB等数据库现在也没有)，这种情况下只能用多个基本函数的组合来实现行转列，同一个问题往往有多种实现方法。

方法1：case when子查询+分组汇总/*mysql*/

Select year, max(Q1) 'Q1',  max(Q2)   'Q2',  max (Q3) 'Q3', max (Q4) 'Q4'

from   (

select year,

case when   quarter = 'Q1' then amount end Q1,

case when   quarter = 'Q2' then amount end Q2,

case when   quarter = 'Q3' then amount end Q3,

case when   quarter = 'Q4' then amount end Q4

from zz11

) t  group by year;

方法2：sum if+分组汇总：/*mysql*/

SELECT year,

MAX(IF(quarter = 'Q1',   amount, null)) AS 'Q1',

MAX (IF(quarter = 'Q2',   amount, null)) AS 'Q2',

MAX (IF(quarter = 'Q3',   amount, null)) AS 'Q3',

MAX (IF(quarter = 'Q4', amount, null)) AS   'Q4'

FROM zz11 GROUP BY year;

其他方法还有 WITH ROLLUP+分组汇总或UNION+分组汇总等，这里不一一列举。这些方法表面各异，但本质其实都差不多，都是用分组的方法算出year的值，用枚举的方法依次生成新列Q1-Q4，同时用汇总的方式生成新列的值。

可以看到，即使最基础最简单的转置，早期SQL的代码也很长。原因在于，每个新列都要枚举出来，新列越多，代码就越长。如果新列是12个月、各州各省，可以想象SQL会更长。

只要新列是已知的，用笨办法总能枚举出来，所以新列多只会影响代码长度，并不会影响难度。如果新列是未知的，想枚举就困难多了。比如：大客户名单经常变动，需要将动态的大客名单由行转为列。遇到这种情况，SQL就很难解决了，通常要求助存储过程\JAVA等语言工具，代码难度和维护难度都会陡然提升。

上面SQL其实还有个毛病：汇总算法难以理解。原表每年每季度只有一条数据，所以原本是不必汇总的，但因为计算year列需要分组，而SQL规定分组的同时必须汇总，所以必须对每年每季度的一条数据进行难以理解的汇总。因为这里的汇总毫无意义，所以汇总算法可以随便选，并不影响计算结果，比如将max换成sum。

SQL之所以规定分组的同时必须汇总，是因为集合化不彻底的缘故。具体来说，SQL只能表达多条记录组成的小集合，而没有语法或符号表达多个小集合组成的大集合，一旦遇到后者，比如分组的情况，就必须立刻汇总，让每个小集合变成一条记录，从而转变成前者。

列转行不涉及难以理解的汇总，早期SQL的思路相对简单，只需按列名依次取出Q1-Q4的记录，再用union拼起来就行，具体写法如下：select year, 'Q1' quarter , Q1 as amount from zz111

union

select year, 'Q2' quarter , Q2 as amount from zz111

union

select year, 'Q3' quarter , Q3 as amount from zz111

union

select year, 'Q4' quarter , Q4 as amount from zz111

列转行虽然思路简单，但因为要枚举组内新行，比如季度、月份、省份，所以代码依然会很长。值得庆幸的是，组内新行来自原表列名(字段名)，而原表列名通常固定，所以一般不存在动态计算的情况，算法也不会太复杂。

引入pivot/unpivot函数

早期SQL实现转置确实不够方便，所以数据库厂商近几年推出专用函数，试图让用户更方便地实现转置。

用pivot实现行转列：/*oracle*/

select * from zz111

pivot(

max(amount)   for quarter in(

'Q1'as Q1,'Q2' as Q2,'Q3' as   Q3,'Q4' as Q4

)

)

仔细观察就会发现，pivot的确让代码缩短了不少，但并没解决本质问题，早期SQL存在的那些问题，现在一个都不少。

首先，pivot不能解决动态语法问题，所以遇到动态新列，依然要依靠存储过程/JAVA，开发难度和维护难度依然很大。

其次，pivot不能解决SQL集合问题，依然要用汇总去解决和汇总毫无关系的问题。对新手来说，这是难以理解的知识点，恳请留意。

在某些特殊情况下，汇总也是有意义的，比如销量分组表另有一个字段customer，使每年每季度的数据有多条，在这种情况下需要行转列，并计算每年每季度amount最大的值。如下：customeryearquarteramountyearQ1Q2Q3Q4

companyAyear2018Q189---->year2018899388100

companyByear2018Q1100year201992979088

companyAyear2018Q293

companyByear2018Q388

companyCyear2018Q499

companyDyear2019Q192

companyEyear2019Q297

companyFyear2019Q390

companyGyear2019Q488

在这种特殊情况下，使用汇总才是真正合理的，合理到核心代码都不用改：/*oracle*/

select * from (select year,quarter,amount from zz111)

pivot(

max(amount)   for quarter in(

'Q1'as Q1,'Q2' as Q2,'Q3' as   Q3,'Q4' as Q4

)

)

可以看到，上述特殊情况实际上不是字面意义上的“行转列”，而是“分组汇总后再行转列”。也许有些初学者会有疑问：这明明是两种不同的算法，为何会使用相同的核心代码？但读过前文的人就会明白，这是SQL集合化不彻底的缘故。

相对而言，列转行函数unpivot就好理解多了：select * from zz111

unpivot(

amount for   quarter in(

Q1,Q2,Q3,Q4

)

)

可以看到，unpivot不仅可以解决代码冗长的问题，而且由于不涉及汇总，所以理解起来也很容易。另外列转行很少遇到动态取列名的需求，因此基础算法不会发生太复杂的变化。可以这样说，unpivot是个相对成功的函数。

双向转置

双向转置可以理解为行列互换或镜像，通常来说，交叉表的双向转置才有意义。

3．将年度-季度销售表转置为季度-年度销售表，即将year的值转为新列名year2018、year2019，同时将列名Q1-Q4转为新列quarter的值。

如下所示：YearQ1Q2Q3Q4---->quarteryear2018year2019

year201889938899Q18992

year201992979088Q29397

Q38890

Q49988

双向转置的实现思路就写在名字里，即先对Q1-Q4执行列转行，再对year2018、year2019执行行转列。如果用小型数据库实现，代码会是下面这样：/*mysql*/

select quarter,

max(IF(year = 'year2018',   amount, null)) AS 'year2018',

max(IF(year = 'year2019',   amount, null)) AS 'year2019'

from (

select year, 'Q1'   quarter , Q1 as amount from crosstb

union

select year, 'Q2'   quarter , Q2 as amount from crosstb

union

select year, 'Q3'   quarter , Q3 as amount from crosstb

union

select year, 'Q4'   quarter , Q4 as amount from crosstb

) t

group by quarter

上述代码包含了行转列和列转行两种算法，所以也兼具了两者的缺点，比如代码冗长、不支持动态行、汇总算法难理解。这里需要注意的是，JAVA\C++等过程性语言擅长多步骤计算，代码的复杂度和代码长度可视为线性关系，SQL则不同，很难分步骤分模块或断点调试，这就导致SQL的复杂度随代码长度呈指数增长。总之，你会发现双向转置要比它表面看起来更难实现。

上面的算法是先列转行再行转列，理论上似乎也可以反过来，即先行转列再列转行，但实际上并非如此。如果先行转列，就会导致子查询的数量从1个增加到4个(由union导致)，不仅代码更长，而且性能更差。当然，如果用支持with 语句的Oracle等数据库，反过来就没问题。

事实上，如果不是小型数据库，而是Oracle或MSSQL，那直接pivot、unpivot联用就可以了，用不到with语句。代码如下：/*Oracle*/

select * from(

select * from crosstb unpivot(

amount for   quarter in(

Q1,Q2,Q3,Q4

)

)

) pivot(

max(amount) for year   in(

'year2018' as   year2018,'year2019' as year2019

)

) order by quarter

上述代码思路更清晰，但由于子查询难以调试难以按独立步骤运行，所以理解起来并不会太轻松。另外列转行的顺序是不可控的，为了让quarter列按Q1-Q4的固定顺序排列，最后必须用order by排序。可以想象，如果需要自定义顺序(比如0、a、1)，则需要造假表并关联该假表，难度会大幅提升。

Pivot/unpivot其实还有个共同的问题，也请初学者注意：这类函数并非ANSI规范，所以各厂商语法区别较大，迁移时比较困难。

动态转置

前面简单提到过动态转置，这里再具体解释一下：由于待转置的值不是固定的，而是会动态增减的，所以转换后的行或列也不是固定的，而是要动态计算，这种算法就是动态转置。

4.动态行转列：部门-地区平均工资表中的地区会随着业务拓展而增加，请将地区字段的值(行)转为新字段名(列)。

如下图：DeptAreaAvgSalary---->DeptBeijingShanghai...

SalesBeijing3100Sales31002700

MarketingBeijing3300Marketing33002400

HRBeijing3200HR32002900

SalesShanghai2700

MarketingShanghai2400

HRShanghai2900

…

乍一看，这个问题应该可以用pivot解决，只须在in里用子查询动态取得地区的唯一值，比如：/*Oracle 11*/

select * from temp pivot (

max(AvgSalary) for Area in(

select distinct Area   from temp

)

)

上述语句看上去很合理，但实际上，pivot里的in函数和一般的in函数不同，一般的in函数里的确可以用子查询，但pivot的in函数不能直接支持子查询。

要想直接支持子查询，必须使用古怪的xml关键字，即：/*Oracle 11*/

select * from temp pivot xml(

max(AvgSalary) for Area in(

select distinct Area   from temp

)

)

这样就会产生一个古怪的中间结果集，该结果集含有2个字段，其中一个字段的类型是XML，如下所示。DeptArea_XML

HRBeijing3200Shanghai3200

MarketingBeijing3300Shanghai2400

SalesBeijing3100Shanghai2700

对于上述中间结果集，还需要动态解析XML，获得AREA的节点，并动态生成表结构，再动态填入数据，才能算出我们的目标。只用SQL已经无法实现此类动态算法，后续代码必须用JAVA或存储过程嵌入SQL才行，最终代码很长，放在文中影响阅读，这里就不贴了。

5.组内记录行转列：收入来源表中，逻辑上Name是分组字段， Source和Income是组内字段，每个Name对应多条组内记录，数量不固定，现在要将组内记录由行转列。

如下所示：NameSourceIncome---->CategorySource1Income1Source2Income2

DavidSalary8000DavidSalary8000Bonus15000

DavidBonus15000DanielSalary9000

DanielSalary9000AndrewShares26000Sales23000

AndrewShares26000RobertBonus13000

AndrewSales23000

RobertBonus13000

本算法的整体思路很清晰：先生成结果表的表结构，再向结果表插入数据，最后输出结果表。

思路虽然清晰，但实际代码非常繁琐，这是因为代码中大量涉及动态语法，包括嵌套循环中的动态语法，而SQL本身不支持动态语法。为了弥补SQL的缺陷，只能用其他语言配合SQL，比如JAVA或存储过程。这些语言不擅长结构化计算，却非要实现结构化算法，代码必然冗长。有兴趣的可按如下步骤实现 ：

1.       计算出结果表应该有几组组内字段(colN)，即对源表按Name分组，求各组记录数，进而求最大的记录数。上表中David和Andrew的记录数最多，有2条，所以colN=2。通过colN，很容易计算出动态列的列名colNames。

2.       动态生成建结果表的SQL字符串(cStr)。难点在于循环colN次，每次都要生成一组组内字段，所有字段包括1个固定列+2*colN个动态列(如图)。

3.       动态执行上述字符串，生成临时表。代码形如：execute immediate cStr;

4.       计算结果表应该插入的关键字列表(rowKeys)，即对源表按Name去重。上表中rowKeys=["David","Daniel","Andrew","Robert"]

5.       循环rowKeys，每次动态生成向结果表插入记录的SQL字符串iStr，并动态执行。生成iStr时，先根据当前Name查询源表，以获得对应的记录列表，这里要动态生成SQL并动态执行。接下来循环该记录列表，拼凑出iStr并执行，从而完成一次循环。

6.       查询结果表，返回数据。

可以想象，如果SQL支持动态语法，或者JAVA/存储过程内置结构化函数库(脱离SQL)，那实际的代码就会精简很多 。

还应该注意到，上面第4步的算法是对Name去重，去重相当于分组后求分组字段的值，而第1步的算法是分组后求各组记录数。这两步同时有分组的动作，理论上是可以复用的，但由于SQL的集合化不彻底，分组的同时必须强制汇总，所以无法复用分组的结果。如果数据量小，能否复用并不重要，最多就是代码丑不丑的问题，而一旦遇到数据量较大或多处复用的算法，能否复用就决定性能高低了。

6. 复杂静态行列转置:每人每天在考勤表有7条固定记录，需要将其转置为2条，其中第1条的In、Out、Break、Return字段值分别对应原表的第1、7、2、3条的Time字段值，第2条对应原表的1、7、5、6的Time字段值。

如下所示：

原表Per_Codein_outDateTimeType

111026312013-10-1109:17:14In

111026362013-10-1111:37:00Break

111026352013-10-1111:38:21Return

111026302013-10-1111:43:21NULL

111026362013-10-1113:21:30Break

111026352013-10-1114:25:58Return

111026322013-10-1118:28:55Out

转置后目标表Per_CodeDateInOutBreakReturn

11102632013-10-1109:17:1418:28:5511:37:0011:38:21

11102632013-10-1109:17:1418:28:5513:21:3014:25:58

由于转置后列数固定，无须动态算法，因此可用SQL实现本算法，具体如下：With r as(

select Per_code,Date,Time,row_number()  over(partition by Per_Code,Date order by Time) rn from temp)

select Per_code,Date,

max(case when rn=1   then Time end) In,

max(case when rn=7   then Time end) Out,

max(case when rn=2   then Time end) Break,

max(case when rn=3   then Time end) Return

from r group by   Per_code,Date

union

select Per_code,Date,

max(case when rn=1   then Time end) In,

max(case when rn=7   then Time end) Out,

max(case when rn=5   then Time end) Break,

max(case when rn=6   then Time end) Return

from r group by   Per_code,Date

SQL集合无序，所以不能用序号引用记录，而本算法又需要序号，所以我们不得不人为制造一个序号，即上述代码中的with子句。有了序号之后，取数据就方便多了。至于明明没有汇总算法，却硬要max，这是SQL集合化不彻底的缘故，前面已经解释过这种现象。

7复杂动态行列转置：用户表和记录表通过用户ID关联，表示用户在2018年某日存在一条活动记录。现在需要计算出2018年的每周，各用户是否存在活动记录，用户名需转置为列。

如下所示：

源表结构UserRecord

ID(pk)1:N---->ID(pk)

NameDate(pk)

转置后目标表的数据WeekUser1User2User3

1YesNoYes

2YesYesNo

3YesNoYes

4NoYesYes

由于列是动态的，所以只能用存储过程/JAVA+动态SQL的方法实现，代码很长，这里照例不贴，下面只讲思路。

要实现上述算法，需要先进行准备工作：将用户表和记录表关联起来；新加计算列，算出Date字段值相对于2018-01-01的周数，最大不应超过53；对周数求最大值，可获得目标表的关键字列表rowKeys；对关联表去重，计算出目标表新增的列名colNames。

接下来是动态转置算法：用colNames动态生成建目标表的SQL，再动态执行SQL；循环rowKeys，每次循环时先从关联表取数据，再动态生成Insert SQL，再动态执行SQL。

上述动态转置算法前面也见到过，事实上，凡此类涉及动态列的转置，都有个动态生成目标表结构，再动态插入数据的过程。这个算法难度较大，这既是SQL缺乏动态语言能力的表现，也是我们不得不求助于JAVA/存储过程的根本原因。后面遇到类似的情况，我会用“动态转置”直接带过。

转置同时列间计算

前面都是单纯的转置，作为习题比较合适，在实际工作中，转置的同时通常会附带其他计算，比如列之间的计算。

8表Temp存储2014年每个客户每个月的应付款情况，现在要将其转置，客户名为主键(关键字)列，1-12月为转置列，对应的值为当月应付款金额，如果当月无数据，则用上月的应付款金额。

如下所示：

源表IDNameamount_payabledue_date

112101CA128002014-02-21

112102CA35002014-06-15

112104LA250002014-01-12

112105LA200002014-11-15

112106LA80002014-12-06

转置后目标表name123456789101112

CA128001280012800128003500350035003500350035003500

LA25000250002500025000250002500025000250002500025000200008000

因为转置后的列是固定的，所以可以用SQL解决，大致思路是：造一个包含单字段month，值为1-12的临时表t1；通过源表的日期算出月份，字段名也是month；用这两个表进行左关联，造出连续的应付款记录，注意这里有很多数据无效；使用pivot实现行转列，用min汇总去除无效数据。具体SQL如下：With t2 as(select   name,amount_payable,EXTRACT(MONTH from dule_date) month from temp

)

,t1 as(SELECT rownum   month FROM dual CONNECT BY LEVEL <= 12

)

,t3 as(select   t2.name,t2.amount_payable,t1.month from t1 left join t2 on   t1.month>=t2.month

)

select * from t3  pivot(min(amount_payable) for month in(1 as   "1",2 as "2",3 as "3",4 as "4",5 as   "5",6 as "6",7 as "7",8 as "8",9 as   "9",10 as "10",11 as "11",12 as   "12"))

上述SQL不长，但是很难理解，尤其是造无效数据这一古怪算法。之所以出现这种情况，是因为SQL集合本身没有序号，也不擅长有序计算，尤其是行间计算，只能采取一些古怪的手段去间接实现。

表间关联列转行

9子表动态插入主表：订单表和订单明细是主子关系，一条订单对应至少一条明细，现在要将明细动态插入订单，如下所示：

源表关系Order---->OrderDetail

ID(pk)OrderID(PK)

CustomerNumber(pk)

DateProduct

Amount

转置后目标表IDCustomerDateProduct1Amount1Product2Amount2Product3Amount3

132019-01-01Apple5Milk3Salt1

252019-01-02Beef2Pork4

322019-01-02Pizza3

由于列是动态的，所以只能用存储过程/JAVA+动态SQL的方法实现，大致思路是：先把两表关联起来；对关联表(或子表)按ID分组，求各组记录数，求最大值，从而算出目标表的动态列列表colNames；对关联表(或主表)的ID去重，算出目标表的主键列表rowKeys；根据colNames和rowKeys实现动态转置算法。

10多表关联列转行： 考试成绩表Exam和补考成绩Retest表都是Students的子表，现在需要将两个子表转置到主表的列，且增加一个总分，注意考试的科目不定，且并非每个人都会补考，但考试的科目一定包含了补考科目。

源表数据及关系Exam tableRetest table

stu_idsubjectscorestu_idstu_nameclass_idstu_idsubjectscore

1Chinese801Ashley3012Chinese78

1Math772Rachel3013Math82

2Chinese583Emily301

2Math67

3Chinese85

3Math56

转置后目标表stu_idstu_nameChinese_scoreMath_scoretotal_scoreChinese_retestMath_retest

1Ashley8077156

2Rachel586712578

3Emily855614182

如果科目固定，就可以用SQL解决，先将Students和Exam左关联并piovt，然后Retest和Exam左关联并pivot，最后再左关联一次。

但每次考试的科目不固定，因此目标表的列是动态的，只能用存储过程/JAVA+动态SQL的方法实现，大致思路是：先将2表左关联至Students；对关联表按stu_id分组，求各组记录数，再求最大记录数，从而计算出目标表的动态列列表colNames；对关联表按stu_id去重，计算出目标表的主键列表rowKeys；根据colNames和rowKeys实现动态转置算法。

分栏

11源表记录各大洲的部分城市人口，现在要分别找出欧洲和非洲的城市和人口，分两栏横向转置，注意目标列是固定的，但源表行数是动态的。如下所示：ContinentCityPopulation---->EuropeCityEuropePopulationAfricaCityEuropePopulation

AfricaCairo6789479Moscow8389200Cairo6789479

AfricaKinshasa5064000London7285000Kinshasa5064000

AfricaAlexandria3328196Alexandria3328196

EuropeMoscow8389200

EuropeLondon7285000

目标表的结构是固定的，可以用SQL解决，思路是：过滤出包含欧洲城市的记录，用rownum算出行号，作为计算列；类似地，过滤出包含非洲城市的记录；将两者进行full join，并取出所需字段。

具体SQL如下：With t1 as(select city   Europecity,population Europepopulation,rownum rn from temp where   continent='Europe')

,t2 as(select city   Africacity,population Africapopulation,rownum rn from temp where   continent='Africa')

select   t1.Europecity,t1.Europepopulation,t2.Africacity,t2.Africapopulation from t1   full join t2 on t1.rn=t2.rn

总结

通过上面的讨论可以发现，只有最简单的三种转置可以直接用piovt/unpivot实现，且仅限大型数据库，还需注意xml解析、结果集乱序，以及难以移植的问题。

对于有一定难度的转置算法来说，如果列是固定的，通常就能用SQL解决，但代码通常很难写，需要熟知SQL的缺陷，并掌握各类古怪的技巧来弥补这些缺陷。前面遇到的缺陷包括：集合化不彻底、集合无序号、不擅长有序计算、难以分步计算、难以调试代码等。

如果列是动态的，复杂程度将大幅上升，只能用JAVA/存储过程，代码将非常繁琐。事实上，不支持动态结构，也是SQL的重大缺陷。

SQL的上述缺陷是个独特的历史现象，在其它计算机语言中并不存在，比如VB\C++\JAVA，甚至包括存储过程。当然，这些语言的集合计算能力比较弱，缺乏结构化计算类库，需要编写大量代码才能实现上述算法(指不嵌入SQL的情况)。

采用esProc 的 SPL能更好地适应这些问题。esProc 是专业的数据计算引擎，基于有序集合设计，像SQL一样提供了完善的结构化函数，又和Java等语言类似天然支持分步计算，相当于 Java 和 SQL 优势的结合。使用SPL (替代Java)来配合SQL可以轻松解决上面的问题：

1行转列，有类似的pivot函数A

1=connect("orcl").query@x("select * from T")

2=A1.pivot(year; quarter, amount)

2列转行，有相当于unpivot的函数A

1=connect("orcl").query@x("select year,Q1,Q2,Q3,Q4 from T")

2=A1.pivot@r(year; quarter, amount)

3双向转置，结合使用pivot及其逆A

1=connect("orcl").query@x("select year,Q1,Q2,Q3,Q4 from T")

2=A1.pivot@r(year;quarter,amount).pivot(quarter;year,amount)

4动态行转列，SPL的pivot可以支持动态数据结构A

1=connect("orcl").query@x("select Dept,Area,AvgSalary   from T")

2=A1.pivot@r(year;quarter,amount).pivot(Dept; Area, AvgSalary)

5组内记录行转列，分步计算并支持动态数据结构AB

1=orcl.query("select   Name,Source,Income from T")

2=gData=A1.group(Name)

3=colN=gData.max(~.len())

4=create(Name, ${colN.("Source"+string(~)+",   Income"+string(~)).concat@c()})

5for gData=A5. Name | A5.conj([Source,   Income])

6>A4.record(B5)

6复杂静态行列转置，天然支持序号AB

1=connect("orcl").query@x("select * from DailyTime   order by Per_Code,Date,Time")=A1.group((#-1)\7)

2=create(Per_Code,Date,In,Out,Break,Return)=B1.(~([1,7,2,3,1,7,5,6]))

3=B2.conj([~.Per_Code,~.Date]|~.(Time).m([1,2,3,4])|[~.Per_Code,~.Date]|~.(Time).m([5,6,7,8]))>A2.record(A3)

7复杂动态行列转置AB

1=connect("db").query("select   t1.ID as ID, t1.Name as Name, t2.Date as Date from User t1, Record t2 where   t1.ID=t2.ID")

2=A1.derive(interval@w("2018-01-01",Date)+1:Week)=A2.max(Week)

3=A2.group(ID)=B2.new(~:Week,${A3.("\"No\":"+Name).concat@c()})

4=A3.run(~.run(B3(Week).field(A3.#+1,"Yes")))

8转置同时列间计算AB

1=orcl.query@x("select name,amount_payable from T")

2=create(name,${12.string@d()})=A1.group(customID)

3for B2=12.(null)

4>A3.run(B3(month(due_date))=   amount_payable)

5>B3.run(~=ifn(~,~[-1]))

6=A2.record(B2.name|B3)

9子表动态插入主表AB

1=orcl.query@x("select * from OrderDetail left join Order on   Order.ID=OrderDetail.OrderID")

2=A1.group(ID)=A2.max(~.count()).("Product"+string(~)+","+"Amount"+string(~)).concat@c()

3=create(ID,Customer,Date,${B2})>A2.run(A3.record([ID,Customer,Date]|~.([Product,Amount]).conj()))

10多表关联列转行AB

1=orcl.query@x("select t1.stu_id stu_id,t1.stu_name stu_name,t2.subject   subject,t2.score score1,t3.score

score2 from Students   t1 left join Exam t2 on  t1.stu_id=t2.stu_id   left join

Retest t3 on t1.stu_id=t3.stu_id and   t2.subject=t3.subject order by t1.stu_id,t2.subject

2=A1.group(stu_id)=A1.group(subject)

3=create(stu_id,stu_name,${(B2.(~.subject+"_score")|"total_score"|B2.(~.subject+"_retest  ")).string()})

4>A2.run(A3.record([stu_id,stu_name]|B2.(~(A2.#).score1)|A2.sum(score1)|B2.(~(A2.#).score2)))

11分栏AB

1=orcl.query@x("select * from World where Continent   in('Europe','Africa')")

2=A1.select(Continent:"Europe")=A1.select(Continent:"Africa")

3=create('Europe City',Population,'Africa City', Population)=A3.paste(A2.(City),A2.(Population),B2.(City),B2.(Population))