MySQL SELECT基于不同列中的值的增量索引

debugcn 发表于 Dev

博丹

我做以下选择

SELECT products.supplier_catalog_number, prices.price, 0 AS price_index
FROM products
  INNER JOIN prices ON prices.product_id = products.id
LIMIT 10

结果看起来像

+-------------------------+----------+-------------+
| supplier_catalog_number | price    | price_index |
+-------------------------+----------+-------------+
| 00000001600L            | 287.0000 |           0 |
| 00000001600L            | 243.9500 |           0 |
| 0023-200                | 710.0000 |           0 |
| 0023-200                | 603.5000 |           0 |
| 0023-300                | 232.0000 |           0 |
| 0023-300                | 185.6000 |           0 |
| 0023-301                | 289.0000 |           0 |
| 0023-301                | 231.2000 |           0 |
| 0023-301                | 109.0000 |           0 |
| 0023-301                |  92.6500 |           0 |
+-------------------------+----------+-------------+

我需要结果是

+-------------------------+----------+-------------+
| supplier_catalog_number | price    | price_index |
+-------------------------+----------+-------------+
| 00000001600L            | 287.0000 |           0 |
| 00000001600L            | 243.9500 |           1 |
| 0023-200                | 710.0000 |           0 |
| 0023-200                | 603.5000 |           1 |
| 0023-300                | 232.0000 |           0 |
| 0023-300                | 185.6000 |           1 |
| 0023-301                | 289.0000 |           0 |
| 0023-301                | 231.2000 |           1 |
| 0023-301                | 109.0000 |           2 |
| 0023-301                |  92.6500 |           3 |
+-------------------------+----------+-------------+

可以在单个查询中完成吗？

小男孩

理想情况下，您可以通过12.19 Window Functions实现您需要的功能，但 MySQL 目前没有官方支持。

例如，在 MariaDB (>= 10.2.0), Window Functions 中，您可以运行如下查询：

SELECT
  `products`.`supplier_catalog_number`,
  `prices`.`price`,
  ROW_NUMBER() OVER (PARTITION BY `products`.`supplier_catalog_number`) - 1 `price_index`
FROM
  `products`
    INNER JOIN `prices` ON `prices`.`product_id` = `products`.`id`
ORDER BY
  `products`.`supplier_catalog_number`,
  `price_index`
LIMIT 10;

见db<>fiddle。

实现您需要的（MySQL）的一种方法是执行以下查询，但是，这不是实现它的唯一或有效方法：

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.19    |
+-----------+
1 row in set (0.00 sec)

mysql> DROP TABLE IF EXISTS `prices`, `products`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `products` (
    ->   `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->   `supplier_catalog_number` VARCHAR(12) NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `prices` (
    ->   `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->   `price` DECIMAL(9, 4) NOT NULL,
    ->   `product_id` BIGINT UNSIGNED NOT NULL,
    ->   FOREIGN KEY (`product_id`)
    ->     REFERENCES `products`(`id`)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `products`
    ->   (`supplier_catalog_number`)
    -> VALUES
    ->   ('00000001600L'),
    ->   ('0023-200'),
    ->   ('0023-300'),
    ->   ('0023-301');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `prices`
    ->   (`price`, `product_id`)
    -> VALUES
    ->   (287.0000, 1), (243.9500, 1),
    ->   (710.0000, 2), (603.5000, 2),
    ->   (232.0000, 3), (185.6000, 3),
    ->   (289.0000, 4), (231.2000, 4),
    ->   (109.0000, 4), (92.6500, 4);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT
    ->   `der`.`supplier_catalog_number`,
    ->   `der`.`price`,
    ->   `der`.`price_index`
    -> FROM (
    ->   SELECT
    ->     `products`.`supplier_catalog_number`,
    ->     `prices`.`price`,
    ->     (SELECT @`index` := IF(@`id` = `products`.`id`, @`index` + 1, 0)) `price_index`,
    ->     (SELECT @`id` := `products`.`id`)
    ->   FROM
    ->     (SELECT @`id` := 0, @`index` := 0) `init`,
    ->     `products`
    ->       INNER JOIN `prices` ON `prices`.`product_id` = `products`.`id`
    ->   ORDER BY
    ->     `products`.`id`,
    ->     `prices`.`price`
    ->   LIMIT 10
    -> ) `der`
    -> ORDER BY
    ->   `der`.`supplier_catalog_number`,
    ->   `der`.`price_index`;
+-------------------------+----------+-------------+
| supplier_catalog_number | price    | price_index |
+-------------------------+----------+-------------+
| 00000001600L            | 287.0000 |           0 |
| 00000001600L            | 243.9500 |           1 |
| 0023-200                | 710.0000 |           0 |
| 0023-200                | 603.5000 |           1 |
| 0023-300                | 232.0000 |           0 |
| 0023-300                | 185.6000 |           1 |
| 0023-301                | 289.0000 |           0 |
| 0023-301                | 231.2000 |           1 |
| 0023-301                | 109.0000 |           2 |
| 0023-301                |  92.6500 |           3 |
+-------------------------+----------+-------------+
10 rows in set (0.00 sec)

见db-fiddle。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。