mysql - 将表值作为 MySQL 中的列

Question

我的数据库中有下表和表数据:

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

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

Answer 1

未测试但可以使用

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