MySQL选择查询以生成动态列结果 [英] MySQL Select Query to generate dynamic column Result

问题描述

我需要编写一个动态返回列的查询.例如，我有一个带有列的表tblTest:

I need to write a query that returns a Column Dynamically. For example I have a table tblTest with columns:

Id, Name,   Type,  Amount
1, Receipt, Cash   100
2, Receipt, Card   200
3, Receipt, Cheque 250
4, Receipt, Card   150
5, Receipt, Cash   100
6, Payment, Cash   300
7, Payment, Cheque 400

SQL查询:

SELECT
    Name,
    SUM(CASE WHEN Type = 'Cash'   THEN Amount ELSE 0 END) Cash,
    SUM(CASE WHEN Type = 'Card'   THEN Amount ELSE 0 END) Card,
    SUM(CASE WHEN Type = 'Cheque' THEN Amount ELSE 0 END) Cheque
FROM tblTest
GROUP BY
    Name;

它返回了我

以上结果是根据我的要求，但在我的情况下，上述数据中的Cash，Card，Cheque等类型未预定义，可能会动态产生，然后我将如何管理它，请帮助我为此建立动态列SQL

above result is as per my requirement but in my case Type Cash,Card,Cheque,etc in above data are not predefined, that may come dynamically then how could i manage it,Please help me to build dynamic columns SQL for that.

先谢谢了.

推荐答案

您需要使用动态数据透视

动态创建您的 pivot 查询.

主要步骤如下

1. 声明一个变量@sql以携带您的SUM函数和CASW WHEN表达式
2. 使用CONCAT组合SUM函数和CASW WHEN表达式字符串以及主select字符串.
3. 使用EXECUTE函数动态执行SQL.

1. Declare a variable @sql to carry your SUM function and CASW WHEN Expression
2. use CONCAT to combine your SUM function and CASW WHEN Expression string and main select string.
3. use EXECUTE function execute SQL Dynamically.

看起来像这样.

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN Type =''',
      Type,
      ''' THEN Amount END) AS ',
      Type
    )
  ) INTO @sql
FROM tblTest;

SET @sql = CONCAT('SELECT
                    Name,', @sql, ' 
                   FROM tblTest
                   GROUP BY
                    Name;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

结果

    Name    Cash    Card    Cheque
1   Payment 300     NULL    400
2   Receipt 200      350    250

SQLfiddle

这篇关于MySQL选择查询以生成动态列结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！