红移 在多个条件下加入，但仅在一个条件不匹配时返回

Question

我有两个表，第一个表有ID和数字。身份证和号码可能会被列出不止一次。

我的第二个表有行(一亿(，其中再次包含ID和数字(以及其他数据(。我需要在该表中搜索结果表中数字列表中没有数字的任何ID。

我遇到了一个联接问题，该联接返回表2中与第一个限定符匹配但与第二个限定符不匹配的任何记录。

DROP TABLE IF EXISTS `myTable`;
CREATE TABLE `myTable` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`ID` varchar(255),
`Numbers` mediumint default NULL,
PRIMARY KEY (`id`)
) AUTO_INCREMENT=1;
INSERT INTO `myTable` (`ID`,`Numbers`)
VALUES
("CRQ44MPX1SZ",1890),
("UHO21QQY3TW",4370),
("JTQ62CBP6ER",1825),
("RFD95MLC2MI",5014),
("URZ04HGG2YQ",2859),
("CRQ44MPX1SZ",1891),
("UHO21QQY3TW",4371),
("JTQ62CBP6ER",1826),
("RFD95MLC2MI",5015),
("URZ04HGG2YQ",2860),
("CRQ44MPX1SZ",1892),
("UHO21QQY3TW",4372),
("JTQ62CBP6ER",1827),
("RFD95MLC2MI",5016),
("URZ04HGG2YQ",2861);

DROP TABLE IF EXISTS `myTable2`;
CREATE TABLE `myTable2` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`ID` varchar(255),
`Numbers` mediumint default NULL,
PRIMARY KEY (`id`)
) AUTO_INCREMENT=1;
INSERT INTO `myTable2` (`ID`,`Numbers`)
VALUES
("CRQ44MPX1SZ",1870),
("UHO21QQY3TW",4350),
("JTQ62CBP6ER",1825),
("RFD95MLC2MI",5014),
("URZ04HGG2YQ",2859),
("CRQ44MPX1SZ",1891),
("UHO21QQY3TW",4371),
("JTQ62CBP6ER",1826),
("RFD95MLC2MI",5015),
("URZ04HGG2YQ",2860),
("CRQ44MPX1SZ",1882),
("UHO21QQY3TW",4372),
("JTQ62CBP6ER",1827),
("RFD95MLC2MI",5016),
("URZ04HGG2YQ",2861);
SELECT mytable1.ID, listagg(distinct mytable2.Numbers, ',') as unauth_list, count(mytable2.Numbers) as unauth_count
FROM mytable1
LEFT JOIN mytable2 on mytable1.id = mytable2.id
WHERE (mytable1.id = mytable2.id)
AND (mytable1.Numbers <> mytable2.Numbers)
GROUP BY mytable1.id

预期输出：

(“CRQ44MPX1SZ”, ”1870,1882”, 2)
(“UHO21QQY3TW”, ”4350”, 1)

Answer 1

Steve。你很接近。您需要反转FROM子句中的表(或使用RIGHT JOIN(，并添加一个JOIN ON子句。WHERE子句现在将基于在表1的数字中看到NULL。用你的数据设置试试这个：

SELECT mytable2.ID, listagg(distinct mytable2.Numbers::text, ',') as unauth_list, count(mytable2.Numbers) as unauth_count
FROM mytable2
LEFT JOIN mytable1 on mytable1.id = mytable2.id 
and mytable1.numbers = mytable2.numbers
Where mytable1.Numbers is null
GROUP BY mytable2.id;

见小提琴-http://sqlfiddle.com/#！15/44b5e/4

产生结果：

id  unauth_list unauth_count
CRQ44MPX1SZ 1870,1882   2
UHO21QQY3TW 4350    1

SF想让我重复你的数据设置，这样

CREATE TABLE myTable1 (
ID varchar(255),
Numbers int default NULL
) ;
INSERT INTO myTable1 (ID,Numbers)
VALUES
('CRQ44MPX1SZ',1890),
('UHO21QQY3TW',4370),
('JTQ62CBP6ER',1825),
('RFD95MLC2MI',5014),
('URZ04HGG2YQ',2859),
('CRQ44MPX1SZ',1891),
('UHO21QQY3TW',4371),
('JTQ62CBP6ER',1826),
('RFD95MLC2MI',5015),
('URZ04HGG2YQ',2860),
('CRQ44MPX1SZ',1892),
('UHO21QQY3TW',4372),
('JTQ62CBP6ER',1827),
('RFD95MLC2MI',5016),
('URZ04HGG2YQ',2861);

CREATE TABLE myTable2 (
ID varchar(255),
Numbers int default NULL
) ;
INSERT INTO myTable2 (ID,Numbers)
VALUES
('CRQ44MPX1SZ',1870),
('UHO21QQY3TW',4350),
('JTQ62CBP6ER',1825),
('RFD95MLC2MI',5014),
('URZ04HGG2YQ',2859),
('CRQ44MPX1SZ',1891),
('UHO21QQY3TW',4371),
('JTQ62CBP6ER',1826),
('RFD95MLC2MI',5015),
('URZ04HGG2YQ',2860),
('CRQ44MPX1SZ',1882),
('UHO21QQY3TW',4372),
('JTQ62CBP6ER',1827),
('RFD95MLC2MI',5016),
('URZ04HGG2YQ',2861);