mysql - 如何从以列为中心的表和以行为中心的表构建查询?
问题描述
我想构建一个组合来自两个表的数据的查询。
test_1
桌子:
+----+---------+
| id | field_A |
+----+---------+
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| 4 | 2 |
+----+---------+
test_2
桌子:
+----+---------+---------+---------+
| id | field_A | field_B | field_C |
+----+---------+---------+---------+
| 1 | 1 | 1 | baz |
| 2 | 1 | 2 | zoo |
| 3 | 2 | 1 | bin |
| 4 | 2 | 2 | won |
+----+---------+---------+---------+
field_A
是两个表之间的公共字段,我有一个桥(?)表(test_3
)位于中间:
+----+------+
| id | desc |
+----+------+
| 1 | foo |
| 2 | bar |
+----+------+
查询的输出如下:
+----+---------+------------------+------------------+
| id | field_A | test_2_field_B_1 | test_2_field_B_2 |
+----+---------+------------------+------------------+
| 1 | 1 | baz | zoo |
| 2 | 1 | baz | zoo |
| 3 | 2 | bin | won |
| 4 | 2 | bin | won |
+----+---------+------------------+------------------+
在哪里:
id
并field_A
从复制test_1
test_2_field_B_1
是test_1.field_A
=test_2.field_A
和test_2.field_B
= 1的值test_2_field_B_2
是test_1.field_A
=test_2.field_A
和test_2.field_B
= 2的值
本质上,我需要取消以行为中心的数据test_2
,然后将其与以列为中心的数据结合起来test_1
。
我已经尝试关注这里的帖子并达到:
SELECT
t1.id_,
t1.field_A,
(SELECT field_C FROM test_2 WHERE field_A = 1 AND field_A = t1.field_A) AS test_2_field_B_1,
(SELECT field_C FROM test_2 WHERE field_A = 2 AND field_A = t1.field_A) AS test_2_field_B_2
FROM test_1 AS t1
GROUP BY t1.id_
但是,我返回的所有内容都在 WorkbenchOK
的Output
框中。
我真的很想获得一个基于查询的解决方案(而不是视图),因为最终我的目标是使用 SQLAlchemy 将其转换为 Python。
下面是创建三个表的 SQL:
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `test_1`
--
DROP TABLE IF EXISTS `test_1`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `test_1` (
`id_` int NOT NULL,
`field_A` int DEFAULT NULL,
PRIMARY KEY (`id_`),
KEY `fk1_idx` (`field_A`),
CONSTRAINT `fk1` FOREIGN KEY (`field_A`) REFERENCES `test_3` (`id_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `test_1`
--
LOCK TABLES `test_1` WRITE;
/*!40000 ALTER TABLE `test_1` DISABLE KEYS */;
INSERT INTO `test_1` VALUES (1,1),(2,1),(3,2),(4,2);
/*!40000 ALTER TABLE `test_1` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `test_2`
--
DROP TABLE IF EXISTS `test_2`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `test_2` (
`id_` int NOT NULL,
`field_A` int DEFAULT NULL,
`field_B` int DEFAULT NULL,
`field_C` varchar(3) DEFAULT NULL,
PRIMARY KEY (`id_`),
KEY `fk2_idx` (`field_A`),
CONSTRAINT `fk2` FOREIGN KEY (`field_A`) REFERENCES `test_3` (`id_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `test_2`
--
LOCK TABLES `test_2` WRITE;
/*!40000 ALTER TABLE `test_2` DISABLE KEYS */;
INSERT INTO `test_2` VALUES (1,1,1,'baz'),(2,1,2,'zoo'),(3,2,1,'bin'),(4,2,2,'won');
/*!40000 ALTER TABLE `test_2` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `test_3`
--
DROP TABLE IF EXISTS `test_3`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `test_3` (
`id_` int NOT NULL,
`desc` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `test_3`
--
LOCK TABLES `test_3` WRITE;
/*!40000 ALTER TABLE `test_3` DISABLE KEYS */;
INSERT INTO `test_3` VALUES (1,'foo'),(2,'bar');
/*!40000 ALTER TABLE `test_3` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
解决方案
我想你想要:
select t1.id, t1.field_a,
(select t2.field_c from test_2 t2 where t2.field_a = t1.field_a and t2.field_b = 1) test_2_field_b_1,
(select t2.field_c from test_2 t2 where t2.field_a = t1.field_a and t2.field_b = 2) test_2_field_b_2
from test_1 t1
您可以使用单个连接和条件聚合来实现相同的逻辑:
select t1.id, t1.field_a,
max(case when t2.field_b = 1 then t2.field_c end) test_2_field_b_1,
max(case when t2.field_b = 2 then t2.field_c end) test_2_field_b_2
from test_1 t1
inner join test_2 t2 on t2.field_a = t1.field_a
group by t1.id, t1.field_a
推荐阅读
- javascript - 如何对 keydown 和 click 事件使用相同的功能?
- macos - 移植一个Window专用工具到macOS,这个工具可以修改其他应用程序的属性
- python - 无法在 django 应用程序上显示外键属性
- sql - SQL Server - 成功插入语句但未加载记录
- python - Django项目中的国家/地区访问限制
- javascript - 如何在纯 JavaScript 中找到匹配的 HTML 节点子树
- python - 无法使用 selenium 单击下拉菜单图标(打开以显示菜单项)。NoSuchElement 异常
- javascript - 具有强类型属性的自定义类型中的类型假设
- ruby-on-rails - 防止 will_paginate 的页码 (0) 无效?
- java - Firebase 表标题在哪里定义?