时间戳

首页 > 解决方案 > 将表值作为 MySQL 中的列

mysql - 将表值作为 MySQL 中的列

问题描述

我的数据库上有以下表格和表格数据：

BRANCH_TABLE

BRANCH_CODE IS_ACTIVE
BRANCH1     1
BRANCH2     1
BRANCH3     1
BRANCH4     1
BRANCH5     1

PRODUCT_TABLE

PRODUCT_ID  PROD_NAME   PRODUCT_CATEGORY
1           PROD1       PROD_CATEGORY1
2           PROD2       PROD_CATEGORY1
3           PROD3       PROD_CATEGORY2
4           PROD4       PROD_CATEGORY2
5           PROD5       PROD_CATEGORY3

SALES_TABLE

SALES_ID    BRANCH_CODE     PRODUCT_ID  AMOUNT
1           BRANCH1         1           100.00
2           BRANCH2         1           100.00
3           BRANCH3         2           100.00
4           BRANCH4         2           100.00
5           BRANCH5         3           100.00
6           BRANCH1         3           100.00
7           BRANCH2         4           100.00
8           BRANCH3         4           100.00
9           BRANCH4         5           100.00
10          BRANCH5         5           100.00

现在我想按产品类别获取每个分支的销售额。这应该是预期的输出：

EXPECTED OUTPUT
BRANCH      PROD_CATEGORY1  PROD_CATEGORY2  PROD_CATEGORY3  TOTAL_SALES
BRANCH 1        100.00          100.00          0.00            200.00
BRANCH 2        100.00          100.00          0.00            200.00
BRANCH 3        100.00          100.00          0.00            200.00
BRANCH 4        100.00          0.00            100.00          200.00
BRANCH 5        0.00            100.00          100.00          200.00

目前，我正在做的是调用分支；

SELECT * FROM BRANCH_TABLE WHERE IS_ACTIVE = '1';

然后我调用产品表中的产品类别

SELECT DISTINCT(PRODUCT_CATEGORY) FROM PRODUCT_TABLE

然后我遍历分支和产品类别结果并获取每个产品类别的销售额；

foreach($branches as $branch){
   foreach($product_categories as $category)
   {
      $sales = $sales_table->getSalesByCategory($branch, $category);
   }
}

// Sample Function on SalesTable Class
public function getSalesByCategory($branch, $category){
     $query = "
     SELECT
     SUM(A.AMOUNT) AS SALES
     FROM
     SALES_TABLE A, PRODUCT_TABLE B
     WHERE
     A.BRANCH_CODE = $branch
     AND
     B.PRODUCT_CATEGORY = $category
     AND
     A.PRODUCT_ID = B.PRODUCT_ID
     ";

     // Then bind the params and execute, I'll leave it here just to be short.
}

该算法有效。但是，正如您所看到的，我正在执行单独的查询，只是为了获取每个产品类别和每个分支的销售量，这非常慢。特别是当我获得 10 万以上的销售记录和 100 多家分支机构时。这就是为什么我只想提出一个查询或至少更少的查询来实现这个表结构。

标签： mysqlsqlpivot

解决方案

未经测试，但可以工作

SELECT      ST.BRANCH_CODE
    ,   SUM(CASE WHEN PT.PRODUCT_CATEGORY='PROD_CATEGORY1' THEN ST.AMOUNT ELSE 0 END) AS PROD_CATEGORY1
    ,   SUM(CASE WHEN PT.PRODUCT_CATEGORY='PROD_CATEGORY2' THEN ST.AMOUNT ELSE 0 END) AS PROD_CATEGORY2
    ,   SUM(CASE WHEN PT.PRODUCT_CATEGORY='PROD_CATEGORY3' THEN ST.AMOUNT ELSE 0 END) AS PROD_CATEGORY3
    ,   SUM(ST.AMOUNT) AS TOTAL
 FROM SALES_TABLE ST
 INNER JOIN PRODUCT_TABLE PT ON ST.PRODUCT_ID=PT.PRODUCT_ID
 GROUP BY ST.BRANCH_CODE
 ORDER BY ST.BRANCH_CODE

推荐阅读