我想构建一个将两个表中的数据组合在一起的查询。
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_
但是,我返回的只是Workbench上Output
框中的OK
。
我真的很想得到一个基于查询的解决方案(而不是视图(,因为最终我的目标是使用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