mysql - 如何在两个以上的 MySQL 表上执行最佳的 FULL OUTER JOIN？-6ren

mysql - 如何在两个以上的 MySQL 表上执行最佳的 FULL OUTER JOIN？

转载作者：行者123 更新时间：2023-11-29 23:18:38

首先，我想说我已经发现MySQL不支持SQL的FULL OUTER JOIN。但是，我需要以这种方式连接数据...希望我说的“我需要完全外部连接”是正确的。如果我错了请纠正我。

我的案例是关于什么的？一张表(users)描述了注册玩游戏的用户。表results1描述了用户玩game1的结果，表results2描述了用户玩game2的结果，依此类推。
现在我想编写一个查询，获取一段时间内所有游戏的用户列表和用户积分。必须将分数相加。查询必须按 user_id 和日期(每月)对结果进行分组。

最大的问题是没有一个表有完整的月份集(所以我不能只做左或右连接)。我考虑过制作某种临时日历表(只是一段时间内的年和月)，然后将表与点连接起来(results1、results2、results3 等..)到该日历表。但这种解决方案似乎也相当复杂。还有其他想法吗？

我的案例(MySQL 转储):

-- --------------------------------------------------------
-- Host:                         192.168.0.60
-- Server version:               5.5.40-cll-lve - MySQL Community Server (GPL) by Atomicorp
-- Server OS:                    Linux
-- HeidiSQL Version:             9.1.0.4867
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8mb4 */;
/*!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' */;

-- Dumping database structure for example
CREATE DATABASE IF NOT EXISTS `example` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;
USE `example`;


-- Dumping structure for table example.results1
CREATE TABLE IF NOT EXISTS `results1` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `points` int(11) DEFAULT NULL,
  `date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- Dumping data for table example.results1: ~8 rows (approximately)
/*!40000 ALTER TABLE `results1` DISABLE KEYS */;
INSERT INTO `results1` (`id`, `user_id`, `points`, `date`) VALUES
    (1, 1, 5, '2014-01-17'),
    (2, 1, 5, '2014-01-18'),
    (3, 2, 10, '2014-02-17'),
    (4, 9, 8, '2014-03-17'),
    (5, 1, 15, '2014-07-17'),
    (6, 3, 9, '2014-10-17'),
    (7, 1, 20, '2015-02-17'),
    (8, 5, 10, '2014-06-17');
/*!40000 ALTER TABLE `results1` ENABLE KEYS */;


-- Dumping structure for table example.results2
CREATE TABLE IF NOT EXISTS `results2` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `points` int(11) DEFAULT NULL,
  `date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT;

-- Dumping data for table example.results2: ~8 rows (approximately)
/*!40000 ALTER TABLE `results2` DISABLE KEYS */;
INSERT INTO `results2` (`id`, `user_id`, `points`, `date`) VALUES
    (1, 1, 50, '2014-01-01'),
    (2, 2, 35, '2014-01-02'),
    (3, 3, 14, '2014-01-03'),
    (4, 4, 18, '2014-06-01'),
    (5, 5, 16, '2014-06-01'),
    (6, 5, 16, '2014-06-02'),
    (7, 6, 4, '2014-10-29'),
    (8, 1, 20, '2014-01-16');
/*!40000 ALTER TABLE `results2` ENABLE KEYS */;


-- Dumping structure for table example.results3
CREATE TABLE IF NOT EXISTS `results3` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `points` int(11) DEFAULT NULL,
  `date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT;

-- Dumping data for table example.results3: ~3 rows (approximately)
/*!40000 ALTER TABLE `results3` DISABLE KEYS */;
INSERT INTO `results3` (`id`, `user_id`, `points`, `date`) VALUES
    (1, 9, 6, '2014-12-17'),
    (2, 1, 10, '2014-01-01'),
    (3, 1, 2, '2014-10-17'),
    (4, 1, 8, '2014-01-03');
/*!40000 ALTER TABLE `results3` ENABLE KEYS */;


-- Dumping structure for table example.results4
CREATE TABLE IF NOT EXISTS `results4` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `points` int(11) DEFAULT NULL,
  `date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT;

-- Dumping data for table example.results4: ~2 rows (approximately)
/*!40000 ALTER TABLE `results4` DISABLE KEYS */;
INSERT INTO `results4` (`id`, `user_id`, `points`, `date`) VALUES
    (1, 4, 41, '2015-03-17'),
    (2, 1, 2, '2014-12-17');
/*!40000 ALTER TABLE `results4` ENABLE KEYS */;


-- Dumping structure for table example.users
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- Dumping data for table example.users: ~10 rows (approximately)
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
INSERT INTO `users` (`id`, `name`) VALUES
    (1, 'Sophie'),
    (2, 'Joshua'),
    (3, 'Isabelle'),
    (4, 'Jack'),
    (5, 'Emily'),
    (6, 'Harry'),
    (7, 'Olivia'),
    (8, 'Oliver'),
    (9, 'Lily'),
    (10, 'Charlie');
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

我用于连接两个表的查询:

    SELECT

        r1_r2.user_id, r1_r2.points, r1_r2.`date`

    FROM (

        SELECT
                IFNULL(rr1.user_id, rr2.user_id) as user_id, (IFNULL(rr1.points,0) + IFNULL(rr2.points,0)) as points, IFNULL(rr1.`date`, rr2.`date`) as `date`
            FROM (
                SELECT
                        r1.user_id, SUM(r1.points) as points, DATE_FORMAT(r1.`date`, '%Y-%m') as `date`
                    FROM results1 as r1
                    GROUP BY r1.user_id, DATE_FORMAT(r1.`date`, '%Y-%m')
            ) as rr1
            LEFT JOIN (
                SELECT
                        r2.user_id, SUM(r2.points) as points, DATE_FORMAT(r2.`date`, '%Y-%m') as `date`
                    FROM results2 as r2
                    GROUP BY r2.user_id, DATE_FORMAT(r2.`date`, '%Y-%m')
            ) as rr2 ON (rr1.user_id = rr2.user_id AND rr1.`date` = rr2.`date`)


        UNION


        SELECT
                IFNULL(rl1.user_id, rl2.user_id) as user_id, (IFNULL(rl1.points,0) + IFNULL(rl2.points,0)) as points, IFNULL(rl1.`date`, rl2.`date`) as `date`
            FROM (
                SELECT
                        r1.user_id, SUM(r1.points) as points, DATE_FORMAT(r1.`date`, '%Y-%m') as `date`
                    FROM results1 as r1
                    GROUP BY r1.user_id, DATE_FORMAT(r1.`date`, '%Y-%m')
            ) as rl1
            RIGHT JOIN (
                SELECT
                        r2.user_id, SUM(r2.points) as points, DATE_FORMAT(r2.`date`, '%Y-%m') as `date`
                    FROM results2 as r2
                    GROUP BY r2.user_id, DATE_FORMAT(r2.`date`, '%Y-%m')
            ) as rl2 ON (rl1.user_id = rl2.user_id AND rl1.`date` = rl2.`date`)

    ) as r1_r2

HAVING
    r1_r2.`date` BETWEEN '2014-01' AND '2014-12'
ORDER BY 
    r1_r2.user_id ASC, r1_r2.`date` ASC

它适用于两个表(results1 和 results2)，但问题是我需要以相同的方式连接两个以上的表...

我对此有某种解决方案(例如一次又一次地嵌套表格..)，但问题是我的解决方案也变得非常复杂(长度很长，阅读和理解非常复杂)。此外，在不久的将来还有可能出现一些额外的 table 。添加额外的 3 或 5 个表后，查询会是什么样子？如果我继续做同样类型的嵌套连接，整个查询将变得越来越复杂，难以阅读、理解、修改......

以下是对 3 个连接表的查询(results1、results2、results3):

SELECT

        r1_r2_r3.user_id, r1_r2_r3.points, r1_r2_r3.`date`

    FROM (

        SELECT

                IFNULL(r1_r2_l.user_id, r3_l.user_id) as user_id, (IFNULL(r1_r2_l.points,0) + IFNULL(r3_l.points,0)) as points, IFNULL(r1_r2_l.`date`, r3_l.`date`) as `date`

            FROM (

                # BEGIN. RESULT FROM BEFORE

                SELECT

                    r1_r2.user_id, r1_r2.points, r1_r2.`date`

                FROM (

                    SELECT
                            IFNULL(rr1.user_id, rr2.user_id) as user_id, (IFNULL(rr1.points,0) + IFNULL(rr2.points,0)) as points, IFNULL(rr1.`date`, rr2.`date`) as `date`
                        FROM (
                            SELECT
                                    r1.user_id, SUM(r1.points) as points, DATE_FORMAT(r1.`date`, '%Y-%m') as `date`
                                FROM results1 as r1
                                GROUP BY r1.user_id, DATE_FORMAT(r1.`date`, '%Y-%m')
                        ) as rr1
                        LEFT JOIN (
                            SELECT
                                    r2.user_id, SUM(r2.points) as points, DATE_FORMAT(r2.`date`, '%Y-%m') as `date`
                                FROM results2 as r2
                                GROUP BY r2.user_id, DATE_FORMAT(r2.`date`, '%Y-%m')
                        ) as rr2 ON (rr1.user_id = rr2.user_id AND rr1.`date` = rr2.`date`)


                    UNION


                    SELECT
                            IFNULL(rl1.user_id, rl2.user_id) as user_id, (IFNULL(rl1.points,0) + IFNULL(rl2.points,0)) as points, IFNULL(rl1.`date`, rl2.`date`) as `date`
                        FROM (
                            SELECT
                                    r1.user_id, SUM(r1.points) as points, DATE_FORMAT(r1.`date`, '%Y-%m') as `date`
                                FROM results1 as r1
                                GROUP BY r1.user_id, DATE_FORMAT(r1.`date`, '%Y-%m')
                        ) as rl1
                        RIGHT JOIN (
                            SELECT
                                    r2.user_id, SUM(r2.points) as points, DATE_FORMAT(r2.`date`, '%Y-%m') as `date`
                                FROM results2 as r2
                                GROUP BY r2.user_id, DATE_FORMAT(r2.`date`, '%Y-%m')
                        ) as rl2 ON (rl1.user_id = rl2.user_id AND rl1.`date` = rl2.`date`)
                ) as r1_r2
                # END. RESULT FROM BEFORE
            ) as r1_r2_l
            LEFT JOIN (
                SELECT
                        r3.user_id, SUM(r3.points) as points, DATE_FORMAT(r3.`date`, '%Y-%m') as `date`
                    FROM results3 as r3
                    GROUP BY r3.user_id, DATE_FORMAT(r3.`date`, '%Y-%m')
            ) as r3_l ON (r1_r2_l.user_id = r3_l.user_id AND r1_r2_l.`date` = r3_l.`date`)


    UNION


        SELECT

                IFNULL(r1_r2_r.user_id, r3_r.user_id) as user_id, (IFNULL(r1_r2_r.points,0) + IFNULL(r3_r.points,0)) as points, IFNULL(r1_r2_r.`date`, r3_r.`date`) as `date`

            FROM (

                # BEGIN. RESULT FROM BEFORE

                SELECT

                    r1_r2.user_id, r1_r2.points, r1_r2.`date`

                FROM (

                    SELECT
                            IFNULL(rr1.user_id, rr2.user_id) as user_id, (IFNULL(rr1.points,0) + IFNULL(rr2.points,0)) as points, IFNULL(rr1.`date`, rr2.`date`) as `date`
                        FROM (
                            SELECT
                                    r1.user_id, SUM(r1.points) as points, DATE_FORMAT(r1.`date`, '%Y-%m') as `date`
                                FROM results1 as r1
                                GROUP BY r1.user_id, DATE_FORMAT(r1.`date`, '%Y-%m')
                        ) as rr1
                        LEFT JOIN (
                            SELECT
                                    r2.user_id, SUM(r2.points) as points, DATE_FORMAT(r2.`date`, '%Y-%m') as `date`
                                FROM results2 as r2
                                GROUP BY r2.user_id, DATE_FORMAT(r2.`date`, '%Y-%m')
                        ) as rr2 ON (rr1.user_id = rr2.user_id AND rr1.`date` = rr2.`date`)


                    UNION


                    SELECT
                            IFNULL(rl1.user_id, rl2.user_id) as user_id, (IFNULL(rl1.points,0) + IFNULL(rl2.points,0)) as points, IFNULL(rl1.`date`, rl2.`date`) as `date`
                        FROM (
                            SELECT
                                    r1.user_id, SUM(r1.points) as points, DATE_FORMAT(r1.`date`, '%Y-%m') as `date`
                                FROM results1 as r1
                                GROUP BY r1.user_id, DATE_FORMAT(r1.`date`, '%Y-%m')
                        ) as rl1
                        RIGHT JOIN (
                            SELECT
                                    r2.user_id, SUM(r2.points) as points, DATE_FORMAT(r2.`date`, '%Y-%m') as `date`
                                FROM results2 as r2
                                GROUP BY r2.user_id, DATE_FORMAT(r2.`date`, '%Y-%m')
                        ) as rl2 ON (rl1.user_id = rl2.user_id AND rl1.`date` = rl2.`date`)
                ) as r1_r2
                # END. RESULT FROM BEFORE
            ) as r1_r2_r
            RIGHT JOIN (
                SELECT
                        r3.user_id, SUM(r3.points) as points, DATE_FORMAT(r3.`date`, '%Y-%m') as `date`
                    FROM results3 as r3
                    GROUP BY r3.user_id, DATE_FORMAT(r3.`date`, '%Y-%m')
            ) as r3_r ON (r1_r2_r.user_id = r3_r.user_id AND r1_r2_r.`date` = r3_r.`date`)


    ) as r1_r2_r3

HAVING
    r1_r2_r3.`date` BETWEEN '2014-01' AND '2014-12'
ORDER BY 
    r1_r2_r3.user_id ASC, r1_r2_r3.`date` ASC

...我想您可以明白我的意思，如果我们继续以相同的方式连接更多表，查询就会变得非常难以理解。

顺便说一句，这只是真实情况的简化版本。实际上，results1、results2、results3 和 results4 是通过连接其他表、计算值而获得的表之间...所以我必须处理的最终查询比上面示例中提到的要复杂得多。

我的问题是:我可以使连接两个以上表的查询更短、更容易理解吗？

最佳答案

我认为您可以使用union all和聚合来完成您想要的操作。我认为以下内容符合您的要求:

select user_id, year(date), month(date), sum(points1) as point31,
       sum(points2) as points2, sum(points3) as points3
from users u left join
     ((select r1.user_id, r1.date, r1.points as points1, NULL as points2, NULL as points3
       from results1 r1
      ) union all
      (select r2.user_id, r2.date, NULL as points1, r2.points as points2, NULL as points3
       from results2 r2
      ) union all
      (select r3.user_id, r3.date, NULL as points1, NULL as points2, r3.points as points3
       from results3 r3
      ) 
     ) r
     on u.id = r.user_id
group by user_id, year(date), month(date);

关于mysql - 如何在两个以上的 MySQL 表上执行最佳的 FULL OUTER JOIN？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/27545556/

文章推荐： php - 如何在php中获取好友列表？

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

mysql - 如何在两个以上的 MySQL 表上执行最佳的 FULL OUTER JOIN？