查询mysql数据的横向布局 [英] query for horizontal layout of mysql data

问题描述

我有一个包含以下字段的表 (delvery_dates):

del_id、del_date、del_ProductID

我的正常查询产生

 2014-08-23 |252014-08-23 |322014-08-23 |142014-08-23 |152014-08-23 |562014-08-23 |342014-08-27 |322014-08-27 |112014-08-27 |192014-08-27 |35

等

我想要一个按以下格式输出的查询:

 del_date, del_ProductID-1, del_ProductID-2, del_ProductID-3, del_ProductID-4 .. 最多 62014-08-23 25 32 14 152014-08-27 32 11 19 35

我在某处看到过有关数据透视表的信息，但我不明白！

非常感谢任何帮助

谢谢克里斯

解决方案

您需要的是一个透视查询.由于 MySQL 没有对此的声明，因此您需要手动"编写它(更准确地说，创建一个动态 SQL 表达式):

所以，它可能是这样的:

-- 首先你需要建立列列表.-- "CASE ... END" 表达式将过滤每一列的数据-- 我以max()"为例；使用您需要的任何聚合函数.选择group_concat(不同的连接('max(当 del_ProductID = ', del_productID, ' 然后 del_id end) ','as `del_productID-', del_productID, '` '))进入@sql例如；-- 现在构建完整的 SELECT 语句set @sql = concat('SELECT del_date, ', @sql, ' from example group by del_date');-- 可选:检查您刚刚构建的 SELECT 语句选择@sql；-- 使用上面构建的 SELECT 语句准备语句从@sql 准备stmt；执行stmt；-- 完成后，一定要释放准备好的语句解除分配准备stmt；

请在 SQL fiddle 中查看此示例.

<小时>

解释

你可能会说伙计，这看起来很复杂！"...但它一点也不复杂(只是很费力).那么，上述解决方案是如何工作的呢?

第一步是构建列列表和一个表达式来填充它.group_concat() 函数将获取行值(或表达式)并将它们连接起来，用逗号分隔它们.您需要一个聚合函数来显示数据透视表结果中的值.我选择了 max() 作为示例，但您可以使用 sum()、average() 或任何其他聚合函数.

至于 case ...end 块里面的聚合函数，你需要数据透视表的每一列都匹配del_productID的值，所以比如case when del_ProductID= 1 then del_id end 仅当 del_ProductID 为 1 时才会返回 del_id 的值(在任何其他情况下将返回 null，例如，如果要返回零，可以添加 else 0).

选择...进入 会将表达式的结果存储到名为 @sql 的变量中.

在你建立了列列表之后，你需要编写 select 语句的其余部分......这是用 concat() 函数完成的.p>

至于其余的，它非常简单:@sql 是一个字符串，所以如果你想执行它，你需要使用它的值创建一个准备好的语句(这是一个 select 语句)，然后执行.

I have a table (delvery_dates) with the following fields:

del_id, del_date, del_ProductID

My normal query produces

    2014-08-23 | 25
    2014-08-23 | 32
    2014-08-23 | 14
    2014-08-23 | 15
    2014-08-23 | 56
    2014-08-23 | 34
    2014-08-27 | 32
    2014-08-27 | 11
    2014-08-27 | 19
    2014-08-27 | 35

etc

I would like a query that outputs in the following format:

    del_date, del_ProductID-1, del_ProductID-2, del_ProductID-3, del_ProductID-4 .. up to 6
    2014-08-23    25                 32                14             15
    2014-08-27    32                 11                19             35

I've seen somewhere about pivot tables, but I don't understand!

Any help much appreciated

thanks Chris

解决方案

What you need is a Pivot query. Since MySQL does not have a statement for that, you'll need to write it "by hand" (more exactly, create a dynamic SQL expression):

So, it may be something like this:

-- First you need to build the column list.
-- The "CASE ... END" expression will filter the data for each column
-- I use "max()" as an example; use whatever aggregate function you need.
select
  group_concat(distinct
    concat(
      'max(case when del_ProductID = ', del_productID, ' then del_id end) ',
      'as `del_productID-', del_productID, '` '
    )
  )
into @sql
from example;

-- Now build the full SELECT statement
set @sql = concat('SELECT del_date, ', @sql, ' from example group by del_date');


-- OPTIONAL: Check the SELECT statement you've just built
select @sql;

-- Prepare a statement using the SELECT statement built above
prepare stmt from @sql;
execute stmt;

-- When you are done, be sure to dealocate the prepared statement
deallocate prepare stmt;

Please see this example in SQL fiddle.

---

The explanation

You may say "dude, this looks quite complex!"... but it's not complex at all (it's just laborious). So, how does the above solution works?

The first step is to build the column list and an expression to fill it. The group_concat() function will take row values (or expressions) and concatenate them, separating them by commas. You need an aggregate function to show the values in the result of the pivot table. I chose max() as an example, but you can use sum(), average() or any other aggregate function.

As for the case ... end piece inside the aggregate function, you need that each column of the pivot table matches the value of del_productID, so, for example, case when del_ProductID = 1 then del_id end will return the value of del_id only if del_ProductID is 1 (will return null in any other case, you can add else 0 if you want to return zero, for example).

The select ... into will store the result of the expression into a variable called @sql.

After you've built the column list, you need to write the rest of the select statement... that's done with the concat() function.

As for the rest, it's pretty straight forward: @sql is a string, so if you want to execute it, you need to create a prepared statement using its value (which is a select statement), and execute it.

这篇关于查询mysql数据的横向布局的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！