python - 如何用另一种方法实现mysql left join？

Question

因为 MySQL Left join 限制为 61，也许这是表:

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL,
  `pid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of test
-- ----------------------------
BEGIN;
INSERT INTO `test` VALUES (1, 3);
INSERT INTO `test` VALUES (1, 4);
INSERT INTO `test` VALUES (2, 4);
INSERT INTO `test` VALUES (3, 5);
INSERT INTO `test` VALUES (3, 6);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

这是 MySQL SQL:

    SELECT
    t1.pid AS lev1,
    t2.pid AS lev2,
    t3.pid AS lev3,
    t4.pid AS lev4

FROM
    test AS t1
    LEFT JOIN test AS t2 ON ( t2.id = t1.pid )
    LEFT JOIN test AS t3 ON ( t3.id = t2.pid )
    LEFT JOIN test AS t4 ON ( t4.id = t3.pid )
    LEFT JOIN test AS t5 ON ( t5.id = t4.pid ) 
WHERE t1.id = 1 ;

我想像这样输出但不使用 MYSQL LEFT JOIN:

lev1,lev2,lev3,lev4
 3,   6,
 3,   5,
 4,    ,

如果python能实现，我也需要!

Answer 1

61 的限制是有原因的，您的查询可能运行得有点慢，但类似下面的内容应该可以满足您的需求:

SELECT 
    t1.id AS lev1, t2.id AS lev2, t3.id AS lev3, t4.id AS lev4
FROM
    t1, t2, t3, t4
WHERE
    t2.pid = t1.id AND t3.pid = t1.id AND t4.pid = t1.id