MySQL DML操作--------实现pivot行转列功能最佳实战

1. 背景

　 * 由于MySQL 不支持类型Oracle与SQL Server的pivot功能进行行列转换。

<a href="https://s3.51cto.com/wyfs02/M00/9C/88/wKiom1lxsSSxDTNgAAEQZy1NYpE561.jpg" target="_blank"></a>

2. 表与数据

<code>mysql&gt; </code><code>select</code> <code>* </code><code>from</code> <code>t_temp;</code>

<code>+</code><code>---------+-----------+------------+</code>

<code>| </code><code>year</code>    <code>| season    | orderCount |</code>

<code>| 2010年  | 一季度    |        100 |</code>

<code>| 2010年  | 二季度    |        200 |</code>

<code>| 2010年  | 三季度    |        300 |</code>

<code>| 2010年  | 四季度    |        400 |</code>

<code>| 2011年  | 一季度    |        150 |</code>

<code>| 2011年  | 二季度    |        300 |</code>

<code>| 2011年  | 三季度    |        450 |</code>

<code>| 2011年  | 四季度    |        600 |</code>

<code>8 </code><code>rows</code> <code>in</code> <code>set</code> <code>(0.00 sec)</code>

3. 通过子查询与case when判断实现

<code>mysql&gt; </code><code>select</code> <code>year</code><code>, </code><code>sum</code><code>(orderCount1) </code><code>'第一季度'</code><code>, </code>

<code>    </code><code>-&gt;              </code><code>sum</code><code>(orderCount2) </code><code>'第二季度'</code><code>, </code>

<code>    </code><code>-&gt;              </code><code>sum</code><code>(orderCount3) </code><code>'第三季度'</code><code>, </code>

<code>    </code><code>-&gt;              </code><code>sum</code><code>(orderCount4) </code><code>'第四季度'</code> 

<code>    </code><code>-&gt; </code><code>from</code>  

<code>    </code><code>-&gt;     (</code>

<code>    </code><code>-&gt;         </code><code>select</code> <code>year</code><code>, </code>

<code>    </code><code>-&gt;             </code><code>case</code> <code>when</code> <code>season = </code><code>'一季度'</code> <code>then</code> 

<code>    </code><code>-&gt;                 orderCount </code>

<code>    </code><code>-&gt;             </code><code>end</code> <code>orderCount1, </code>

<code>    </code><code>-&gt;             </code><code>case</code> <code>when</code> <code>season = </code><code>'二季度'</code> <code>then</code> 

<code>    </code><code>-&gt;             </code><code>end</code> <code>orderCount2, </code>

<code>    </code><code>-&gt;             </code><code>case</code> <code>when</code> <code>season = </code><code>'三季度'</code> <code>then</code> 

<code>    </code><code>-&gt;             </code><code>end</code> <code>orderCount3, </code>

<code>    </code><code>-&gt;             </code><code>case</code> <code>when</code> <code>season = </code><code>'四季度'</code> <code>then</code> 

<code>    </code><code>-&gt;             </code><code>end</code> <code>orderCount4 </code>

<code>    </code><code>-&gt;         </code><code>from</code> <code>t_temp</code>

<code>    </code><code>-&gt;     ) t </code>

<code>    </code><code>-&gt; </code><code>group</code> <code>by</code> <code>year</code><code>;</code>

<code>+</code><code>---------+--------------+--------------+--------------+--------------+</code>

<code>| </code><code>year</code>    <code>| 第一季度     | 第二季度     | 第三季度     | 第四季度     |</code>

<code>| 2010年  |          100 |          200 |          300 |          400 |</code>

<code>| 2011年  |          150 |          300 |          450 |          600 |</code>

<code>2 </code><code>rows</code> <code>in</code> <code>set</code> <code>(0.00 sec)</code>

4. 通过IF聚合函数实现

<code>mysql&gt; </code><code>SELECT</code> <code>year</code><code>, </code>

<code>    </code><code>-&gt;        </code><code>SUM</code><code>(IF(season = </code><code>'一季度'</code><code>, orderCount, </code><code>null</code><code>)) </code><code>AS</code> <code>'第一季度'</code><code>,</code>

<code>    </code><code>-&gt;        </code><code>SUM</code><code>(IF(season = </code><code>'二季度'</code><code>, orderCount, </code><code>null</code><code>)) </code><code>AS</code> <code>'第二季度'</code><code>,</code>

<code>    </code><code>-&gt;        </code><code>SUM</code><code>(IF(season = </code><code>'三季度'</code><code>, orderCount, </code><code>null</code><code>)) </code><code>AS</code> <code>'第三季度'</code><code>,</code>

<code>    </code><code>-&gt;        </code><code>SUM</code><code>(IF(season = </code><code>'四季度'</code><code>, orderCount, </code><code>null</code><code>)) </code><code>AS</code> <code>'第四季度'</code>

<code>    </code><code>-&gt; </code><code>FROM</code> <code>t_temp</code>

<code>    </code><code>-&gt; </code><code>GROUP</code> <code>BY</code> <code>year</code><code>;</code>

5. 总结

以需求驱动技术，技术本身没有优略之分，只有业务之分。

      本文转自asd1123509133 51CTO博客，原文链接：http://blog.51cto.com/lisea/1949764，如需转载请自行联系原作者