主页 / dba / 问题 / 274181
Accepted
Andy Wong
Asked: 2020-08-24 00:46:03 +0800 CST

如何在 MySQL 中使用准备好的语句创建动态列？

  • 772

我有 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
mysql dynamic-sql

1 个回答

  1. Best Answer

    这是 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>&nbsp;");
  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");
    • 1

相关问题