时间戳

首页 > 解决方案 > MYSQL加入三张表有异常

mysql - MYSQL加入三张表有异常

问题描述

我正在使用三个 SQL 表，并且正在使用 INNER JOIN 来加入这些表。以下是表格外观的概述。

在此处输入图像描述 

CREATE TABLE `User` (
   `id` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
   `firstName` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
   `lastName` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
   `email` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
   PRIMARY KEY (`id`)                                                                       ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |


CREATE TABLE `Ownership` ( 
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `user` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
   `certificate` int(10) unsigned NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `unicity` (`user`,`certificate`)                                              ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 

CREATE TABLE `Certificate` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `domain` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
   `creationDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,   
   `expirationDate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
   `type` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
   PRIMARY KEY (`id`)                                                                       ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

加入表格的请求如下： 

select user, lastName, domain 
from Ownership 
    INNER JOIN User ON Ownership.user = User.id 
    INNER JOIN Certificate ON Ownership.certificate = Certificate.id;`

例如，我有这种类型的结果： 在此处输入图像描述

我想要的是只为一个域名检索一个用户，并创建一个例外以仅在 certificate.domain 值不存在时才进行加入。

MYSQL 有可能吗？

谢谢

标签： mysqlexceptionjoindatatables

解决方案

您需要一个预查询来至少限定与给定域关联的某些唯一性组件。为此，让我们考虑一下。

select 
      C.domain,
      min( O.user ) JustOneUser 
   from 
      Certificate C
         JOIN Ownership O
            on C.ID = O.Certificate
   group by
      C.Domain

因此，这将为给定域获得一个用户。现在，使用该结果来获取与域关联的用户。

select
      PQ.Domain,
      U.LastName,
      U.FirstName
   from
      (select 
             C.domain,
             min( O.user ) JustOneUser 
          from 
             Certificate C
                JOIN Ownership O
                   on C.ID = O.Certificate
          group by
             C.Domain ) PQ
      JOIN User U
         on PQ.JustOneUser = U.ID

推荐阅读