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

问题描述

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

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

del_id，del_date，del_ProductID

del_id, del_date, del_ProductID

我的普通查询产生

    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

等

我希望查询以以下格式输出:

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!

非常感谢任何帮助

谢谢 克里斯

推荐答案

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

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):

所以，可能是这样的:

-- 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;

请在SQL小提琴中查看此示例.

说明

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

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?

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

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.

对于 case ... end 聚合函数中的某个部分，您需要数据透视表的每一列都与del_productID的值匹配，因此，例如，case when del_ProductID = 1 then del_id end仅在del_ProductID为1时才返回del_id的值.在其他任何情况下都返回null，例如，如果要返回零，则可以添加else 0.

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).

select ... into 将存储结果将表达式的内容转换为名为@sql的变量.

建立列列表后，需要编写其余的select语句...这是通过concat()函数完成的.

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

至于其余部分，这很简单:@sql是一个字符串，因此，如果要执行它，则需要使用其值(这是一个select语句)创建一个准备好的语句，然后执行它.

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屋！