从MySQL中具有不同列的表的多个联接的结果中删除重复项 [英] Removing duplicates from result of multiple join on tables with different columns in MySQL

问题描述

我正在尝试发表一条语句来从3个相关的表中提取数据(因为它们都共享一个公共的字符串索引).我无法阻止MySQL返回两个表的乘积，从而使结果集比我想要的大得多.每个表具有不同数量的列，并且我宁愿不使用UNION，因为每个表中的数据都是独立的.

I am trying to make one statement to pull data from 3 related tables (as in they all share a common string index). I am having trouble preventing MySQL from returning the product of two of the tables, making the result set much larger than I want it. Each table has a different number of columns, and I would prefer to not use UNION anyway, because the data in each table is separate.

这里是一个例子:

表X是主表，具有字段A B.

Table X is the main table and has fields A B.

表Y的A C D字段.

Table Y has fields A C D.

表Z的字段为A E F G.

Table Z has fields A E F G.

-

我理想的结果将具有以下形式:

My ideal result would have the form:

A1 B1 C1 D1 E1 F1 G1

A1 B2 C2 D2 00 00 00

A2 B3 C3 D3 E2 F2 G2

A2 B4 00 00 E3 F3 G3

等...

-

这是我尝试过的最简单的SQL，它显示了我的问题(也就是说，它返回由A的数据索引的Y * Z的乘积:

Here is the simplest SQL I have tried that shows my problem (that is, it returns the product of Y * Z indexed by data from A:

SELECT DISTINCT *

FROM X

LEFT JOIN Y USING (A)

LEFT JOIN Z USING (A)

-

我尝试将group by子句添加到Y和Z上的字段.但是，如果我仅按一个列进行分组，则它仅返回与该列中的每个唯一值匹配的第一个结果(即:A1 C1 E1，A1 C2 E1，A1 C3 E1).如果我按两列分组，它将再次返回两个表的乘积.

I have tried adding a group by clause to fields on Y and Z. But, if I only group by one column, it only returns the first result matched with each unique value in that column (ie: A1 C1 E1, A1 C2 E1, A1 C3 E1). And if I group by two columns it returns the product of the two tables again.

我也尝试过在查询中执行多个select语句，然后将结果表联接在一起，但是我又收到了表的乘积作为输出.

I've also tried doing multiple select statements in the query, then joining the resulting tables, but I received the product of the tables as output again.

基本上，我想将三个select语句的结果合并为一个结果，而又不给我所有数据组合.如果需要，我可以求助于多个查询.但是，由于它们都包含一个公共索引，因此我认为应该有一种方法可以在我遗失的一个查询中做到这一点.

Basically I want to merge the results of three select statements into a single result, without it giving me all combinations of the data. If I need to, I can resort to doing multiple queries. However, since they all contain a common index, I feel there should be a way to do it in one query that I am missing.

感谢您的帮助.

推荐答案

我不知道我是否理解您的问题，但是为什么要使用LEFT JOIN?故事听起来更像是内心的加入".这里什么都不需要UNION.

I don't know if I understand your problem, but why are you using a LEFT JOIN? The story sounds more like an INNER JOIN. Nothing here calls for a UNION.

好吧，我想我现在明白了你想要的.我从来没有尝试过我要提出的建议，而且，一些数据库尚不支持(但)，但我认为您需要一个窗口功能.

OK, I think I see what you want now. I've never tried what I am about to suggest, and what's more, some DBs don't support it (yet), but I think you want a windowing function.

WITH Y2 AS (SELECT Y.*, ROW_NUMBER() OVER (PARTITION BY A) AS YROW FROM Y),
     Z2 AS (SELECT Z.*, ROW_NUMBER() OVER (PARTITION BY A) AS ZROW FROM Z)
SELECT COALESCE(Y2.A,Z2.A) AS A, Y2.C, Y2.D, Z2.E, Z2.F, Z2.G
FROM Y2 FULL OUTER JOIN Z2 ON Y2.A=Z2.A AND YROW=ZROW;

想法是将列表打印在尽可能少的行中，对吧?因此，如果A1在Y中有10个条目，在Z中有7个条目，那么我们得到10行，其中3个Z字段具有NULL.这在Postgres中有效.我不认为该语法在MySQL中可用.

The idea is to print the list in as few rows as possible, right? So if A1 has 10 entries in Y and 7 in Z, then we get 10 rows with 3 having NULLs for the Z fields. This works in Postgres. I do not believe this syntax is available in MySQL.

Y:

 a | d | c  
---+---+----
 1 | 1 | -1
 1 | 2 | -1
 2 | 0 | -1

Z:

 a | f | g | e 
---+---+---+---
 1 | 9 | 9 | 0
 2 | 1 | 1 | 0
 3 | 0 | 1 | 0

以上语句的输出:

 a | c  | d | e | f | g 
---+----+---+---+---+---
 1 | -1 | 1 | 0 | 9 | 9
 1 | -1 | 2 |   |   |  
 2 | -1 | 0 | 0 | 1 | 1
 3 |    |   | 0 | 0 | 1

这篇关于从MySQL中具有不同列的表的多个联接的结果中删除重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！