时间戳

首页 > 解决方案 > 多对多加入当前拥有的行

mysql - 多对多加入当前拥有的行

问题描述

本质上是尝试为位置索引视图编写 MySQL 查询（可以是任何版本），该视图显示与位置具有当前关联的所有项目。item_location 表代表项目移动的位置和时间。


items
______________
| id | name  |
--------------
| 1  | item1 |
| 2  | item2 |
| 3  | item3 |
| 4  | item4 |
--------------

locations
______________
| id | city  |
--------------
| 1  | city1 |
| 2  | city2 |
| 3  | city3 |
| 4  | city4 |
--------------

item_location
_______________________________________________________
| id | item_id | location_id | date                   |
------------------------------------------------------|
| 1  | 1       | 1           | 1998-01-01 04:00:00    |
| 2  | 1       | 2           | 1998-01-02 04:00:00    |
| 3  | 2       | 1           | 1998-01-03 04:00:00    |
| 4  | 3       | 1           | 1998-01-04 04:00:00    |
| 4  | 4       | 3           | 1999-01-04 04:00:00    |
| 4  | 4       | 4           | 1999-02-04 04:00:00    |
-------------------------------------------------------

expected output (with limit 3):
____________________________________________________________________________________________
| location.id | location.city | items                                                      |
--------------------------------------------------------------------------------------------
| 1           | city1         | [{"id":"2", "name": "item2"}, {"id":"3", "name": "item3"}] |
| 2           | city2         | [{"id":"1", "name": "item1"}]                              |
| 3           | city3         | [], '', or null because city4 has latest date.             |
--------------------------------------------------------------------------------------------

如果在这种情况下有更常见或更有效的做法，我愿意重组我的数据库。

我一直在使用 JSON_ARRAYAGG(JSON_OBJECT("id", item.id, "name", item.name)) 来构建 json 对象的数组。如果也有更好/通用/性能更好的替代方案，我很开放。

我已经为我的情况创建了一个可重现的可运行示例。

架构（MySQL v5.7）

CREATE TABLE `item_location` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `location_id` int unsigned NOT NULL,
  `item_id` int unsigned NOT NULL,
  `date` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `location_id` (`location_id`),
  KEY `machine_id` (`item_id`)
) ENGINE=InnoDB;

CREATE TABLE `locations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `city` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `items` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(25) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `items` (`id`, `name`) VALUES
(1, '1405'), (2, '1447'), (3, '1641');

INSERT INTO `locations` (`id`, `city`) VALUES
(1, 'Kingsville'), (2, '
Wright City'), (3, 'Racine');

INSERT INTO `item_location` (`id`, `location_id`, `item_id`, `date`) VALUES
(1, 3, 1, '1992-01-14 05:00:00'),
(2, 3, 2, '1993-02-05 05:00:00'),
(3, 3, 3, '1997-05-13 04:00:00'),
(4, 1, 3, '2018-01-15 05:00:00'),
(5, 2, 3, '2006-07-01 04:00:00')

尝试的查询 #1

SELECT DISTINCT item_location.location_id, locations.city, date, JSON_ARRAYAGG(JSON_OBJECT("id", items.id, "name", items.name)) AS items
FROM item_location 
INNER JOIN items ON item_location.item_id = items.id
INNER JOIN locations ON item_location.location_id = locations.id
GROUP BY items.name, item_location.location_id, date;


| location_id | date                | city         | items                       |
| ----------- | ------------------- | ------------ | --------------------------- |
| 3           | 1992-01-14 05:00:00 | Racine       | [{"id": 1, "name": "1405"}] |
| 3           | 1993-02-05 05:00:00 | Racine       | [{"id": 2, "name": "1447"}] |
| 1           | 2018-01-15 05:00:00 | Kingsville   | [{"id": 3, "name": "1641"}] |
| 2           | 2006-07-01 04:00:00 | Wright City  | [{"id": 3, "name": "1641"}] |
| 3           | 1997-05-13 04:00:00 | Racine       | [{"id": 3, "name": "1641"}] |

---

在 DB Fiddle 上查看

预期结果：

| location_id | city         | items                                                    |
| ----------- | ------------ | ———————————————————————————————————————————————————————— |
| 3           | Racine       | [{"id": "1", "name": "1405"}, {"id": "2", "name": "1447"}]  |
| 1           | Kingsville   | [{"id": "3", "name": "1641"}]                              |
| 2           | Wright City  | []                                                       |

这是我最接近的查询，除了我不确定 MySQL 是否有办法能够知道哪个项目是该位置的最新所有者：

查询 #1

select `locations`.`id`, `locations`.`city` as `city`, `itms`.`items` from locations
left join (
SELECT il.location_id AS id, JSON_ARRAYAGG(JSON_OBJECT("name", i.name)) AS items
FROM   item_location il
JOIN   items i ON i.id = il.item_id
GROUP  BY il.location_id
) itms USING (id)  
order by `city`;


| id  | city         | items                                                  |
| --- | ------------ | ------------------------------------------------------ |
| 2   | Wright City | [{"name": "1641"}]                                 |
| 1   | Kingsville   | [{"name": "1641"}]                                     |
| 3   | Racine       | [{"name": "1405"}, {"name": "1447"}, {"name": "1641"}] |

标签： mysql

解决方案

SELECT DISTINCT item_location.location_id, locations.city, JSON_ARRAYAGG(JSON_OBJECT("id", items.id, "name", items.name)) AS items
FROM item_location 
INNER JOIN items ON item_location.item_id = items.id
INNER JOIN locations ON item_location.location_id = locations.id
GROUP BY items.name

您可以根据需要将日期添加到上述查询中。

您的问题的答案将是以下是需要改进的领域。了解 MySQL EXPLAIN。阅读更多。

EXPLAIN SELECT DISTINCT item_location.location_id, locations.city, JSON_ARRAYAGG(JSON_OBJECT("id", items.id, "name", items.name)) AS items FROM item_location INNER JOIN items ON item_location.item_id = items.id INNER JOIN locations ON item_location.location_id = locations.id GROUP BY items.name

在此处输入图像描述

您需要学习如何删除 Using temporary；使用文件排序等。互联网上有大量教程可供学习。

使用索引，使用临时，使用文件排序 - 如何解决这个问题？

如果您需要进一步的帮助，请告诉我。

推荐阅读