Mysql

如何在 MySQL 中使用準備好的語句創建動態列？

  • August 24, 2020

我有 4 個相互關聯的表。

表位置：

CREATE TABLE `location` (
 `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
 `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
 PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `location` (`id`, `name`) VALUES
(1, 'Dallas'),
(2, 'Boston'),
(3, 'Houston');

表項：

CREATE TABLE `item` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`brand` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `item` (`id`, `brand`) VALUES
(1, 'Nissan Almera M/T 2009-2015'),
(2, 'Toyota Corolla A/T 2005-2012'),
(3, 'Nissan Terra A/T 2010-2017'),
(4, 'Suzuki Esteem M/T 1980-1990'),
(5, 'Toyota Fortuner A/T 2014-2020');

表 item_in：

CREATE TABLE `item_in` (
 `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
 `location_id` bigint(20) UNSIGNED NOT NULL,
 `item_id` bigint(20) UNSIGNED NOT NULL,
 `quantity` int(11) DEFAULT NULL,
 PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `item_in` (`id`, `location_id`, `item_id`, `quantity`) VALUES
(1, 1, 1, 1000),
(2, 1, 2, 500),
(3, 2, 2, 200),
(4, 2, 2, 300),
(5, 3, 3, 300),
(6, 1, 3, 800),
(7, 3, 5, 300),
(8, 3, 4, 400);

表 item_out：

CREATE TABLE `item_out` (
 `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
 `location_id` bigint(20) UNSIGNED NOT NULL,
 `item_id` bigint(20) UNSIGNED NOT NULL,
 `quantity` int(11) DEFAULT NULL,
 PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `item_out` (`id`, `location_id`, `item_id`, `quantity`) VALUES
(1, 1, 2, 20),
(2, 1, 1, 25),
(3, 2, 2, 25),
(4, 3, 3, 25),
(5, 3, 5, 10),
(6, 3, 4, 15),
(7, 1, 1, 200),
(8, 2, 2, 50);

使用動態 SQL，我能夠根據它們的位置和項目（item_in 數量減去 item_out 數量）獲取每個項目的單獨剩餘數量，並將位置名稱作為列。（見下面的程式碼）：

SET @sql = NULL, @sql1 = NULL, @sql2 = NULL;

SELECT GROUP_CONCAT( DISTINCT
         CONCAT('SUM(CASE WHEN `location_id` = ''',`location_id`, ''' THEN quantity END) AS ',`name`))
         INTO @sql1
         FROM item_in
         JOIN location on location.id = item_in.location_id;
       
SELECT GROUP_CONCAT( DISTINCT
         CONCAT('SUM(CASE WHEN `location_id` = ''',`location_id`, ''' THEN quantity END) AS ',`name`))
         INTO @sql2
         FROM item_out
         JOIN location on location.id = item_out.location_id;
         
SET @sql = CONCAT('SELECT item.brand AS Item, IFNULL(item_in.Dallas, 0) - IFNULL(item_out.Dallas, 0) AS Dallas, IFNULL(item_in.Boston, 0) - IFNULL(item_out.Boston, 0) AS Boston, IFNULL(item_in.Houston, 0) - IFNULL(item_out.Houston, 0) AS Houston FROM item LEFT JOIN (SELECT item_in.item_id, ', @sql1, ' FROM item_in
                   GROUP BY item_in.item_id) AS item_in ON item.id = item_in.item_id LEFT JOIN (SELECT item_out.item_id, ', @sql2, ' FROM item_out
                   GROUP BY item_out.item_id) AS item_out ON item.id = item_out.item_id');
                   
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

結果：

Item                         | Dallas | Boston | Houston
Nissan Almera M/T 2009-2015        775        0         0                           
Toyota Corolla A/T 2005-2012       480      425         0
Nissan Terra A/T 2010-2017         800        0       275
Suzuki Esteem M/T 1980-1990          0        0       385
Toyota Fortuner A/T 2014-2020        0        0       290

我的問題是，我該如何更改程式碼以便動態顯示位置名稱列，而不是在查詢中手動對其進行硬編碼，因為使用者可以隨時添加新位置？如果有人可以查看我的程式碼，我將非常感謝您的幫助。我遇到麻煩的唯一部分是如何不對這些行進行硬編碼並動態執行它們：

IFNULL(item_in.Dallas, 0) - IFNULL(item_out.Dallas, 0) AS Dallas, IFNULL(item_in.Boston, 0) - IFNULL(item_out.Boston, 0) AS Boston, IFNULL(item_in.Houston, 0) - IFNULL(item_out.Houston, 0) AS Houston

這是 PHP 中可能的解決方案：

 $link = mysqli_connect("p:192.168.3.2", "ghp", "azerty", "test");

 if (!$link) {
    error_log ( "Error: Unable to connect to MySQL. "
                 . mysqli_connect_errno() );
    header('HTTP/1.0 500 Internal Server Error');
    exit;
 }

 // store the items in an array, will be used as rows
 $itema = array();
 $stmt = mysqli_stmt_init($link);
 if (mysqli_stmt_prepare(
       $stmt,
       "select id, brand from item")) {
    $count = 0;
    $lid = $lname = "";
    if (mysqli_stmt_execute($stmt)) {
       mysqli_stmt_bind_result($stmt, $lid, $lname);
       while (mysqli_stmt_fetch($stmt)) {
          $count += 1;
          $itema[$lid] = $lname;
       }
    } else {
       error_log ( "Error: execute - "
                   . mysqli_errno($link) . " - " . mysqli_error($link));
       header('HTTP/1.0 500 Internal Server Error');
    }
    if ($count < 1) {
       header('HTTP/1.0 403 Forbidden');
    }
    mysqli_stmt_close($stmt);
 } else {
    error_log ( "Error: prepare - "
                 . mysqli_errno($link) . " - " . mysqli_error($link));
    header('HTTP/1.0 500 Internal Server Error');
 }

 // store the locations in an array, will be used as columns
 $loca = array();
 $stmt = mysqli_stmt_init($link);
 if (mysqli_stmt_prepare(
       $stmt,
       "select id, name from location")) {
    $count = 0;
    $lid = $lname = "";
    if (mysqli_stmt_execute($stmt)) {
       mysqli_stmt_bind_result($stmt, $lid, $lname);
       while (mysqli_stmt_fetch($stmt)) {
          $count += 1;
          $loca[$lid] = $lname;
       }
    } else {
       error_log ( "Error: execute - "
                   . mysqli_errno($link) . " - " . mysqli_error($link));
       header('HTTP/1.0 500 Internal Server Error');
    }
    if ($count < 1) {
       header('HTTP/1.0 403 Forbidden');
    }
    mysqli_stmt_close($stmt);
 } else {
    error_log ( "Error: prepare - "
                 . mysqli_errno($link) . " - " . mysqli_error($link));
    header('HTTP/1.0 500 Internal Server Error');
 }

 // store the remaining quantities in an array, for the table cells
 $data = array();
 $stmt = mysqli_stmt_init($link);
 if (mysqli_stmt_prepare(
       $stmt,
       "select
          location_id,
          item_id,
          sum(quantity)
          from (
            select location_id, item_id, quantity from item_in
            union all
            select location_id, item_id, -quantity from item_out
          ) s
          group by location_id, item_id")) {
    $count = 0;
    $lid = $iid = $q = 0;
    if (mysqli_stmt_execute($stmt)) {
       mysqli_stmt_bind_result($stmt, $lid, $iid, $q);
       while (mysqli_stmt_fetch($stmt)) {
          $count += 1;
          $data[$lid][$iid] = $q;
       }
    } else {
       error_log ( "Error: execute - "
                   . mysqli_errno($link) . " - " . mysqli_error($link));
       header('HTTP/1.0 500 Internal Server Error');
    }
    if ($count < 1) {
       header('HTTP/1.0 403 Forbidden');
    }
    mysqli_stmt_close($stmt);
 } else {
    error_log ( "Error: prepare - "
                 . mysqli_errno($link) . " - " . mysqli_error($link));
    header('HTTP/1.0 500 Internal Server Error');
 }

 // show the results in a table
 print("<html><head>\n");
 print("<style>
         table,th,td {
           border: 1px solid black;
           border-collapse: collapse;
         }
        </style></head><body>\n");
 print("<table>\n");
 print("<tr><th> ");
 foreach($loca as $i => $value) {
   print("<th align=right>" . $value);
 }
 foreach($itema as $i => $value) {
   print("<tr><td style='font-weight:bold;'>" . $value);
   foreach($loca as $j => $value) {
     print("<td align=right>" . $data[$j][$i]  );
   }
   print("\n");
 }
 print("</table></body></html>\n");

引用自：https://dba.stackexchange.com/questions/274181

相關問答

Mysql

觸發器內部的動態 SQL 的替代方案？

  • July 24, 2018
檢視問答
Mysql

使用可變數量的要綁定的參數執行準備好的語句

  • August 11, 2015
檢視問答
Mysql

在觸發器中更新查詢

  • January 22, 2022
檢視問答
Mysql

MySQL：有沒有辦法根據日期動態生成多個日期範圍

  • November 28, 2021
檢視問答
Mysql

準備好的語句看起來正確但拋出錯誤 1064

  • November 25, 2021
檢視問答
Mysql

我什麼時候需要在 MySQL 儲存過程中 DEALLOCATE 準備好的語句？

  • July 10, 2021
檢視問答