与自身联接表与使用MySQL分组 [英] Joining a table with itself vs using Group By MySQL

问题描述

这是我的桌子的简化版:

Here is a simplified version of my table:

它包含列 employee_id column2 column3 x y 他们都是NUMERIC

It contains the columns employee_id column2 column3 x y and they are all NUMERIC

我在编写复杂的MySQL查询时遇到麻烦.我想获取所有employeeid和column3值，这些值在column2中与另一位雇员具有相同的值，并且在所有其他雇员中x和y对的值都不同.例如，如果表中有以下4行:

I am having trouble writing a complicated MySQL query. I want to get all the employeeid and column3 values for all employees that have the same value in column2 as another employee and have DIFFERENT values for x and y PAIR from every other employee. For example, if the following 4 rows were in table:

2 100 123.456 5 7
1 234 123.456 5 7
3 100 456.789 5 10
4 100 123.456 5 7

应获取行2 100 123.456 5 7和3 100 456.789 5 10，因为它们具有不同的员工ID(2与3)，column2的值相同(100和100)，并且不同x，y对:(员工2的x = 5和y = 7，这与x = 5和y = 10不同).

The rows 2 100 123.456 5 7 and 3 100 456.789 5 10 should be obtained because they have different employee ids (2 vs 3), the same value for column2 (100 and 100), and different x, y pair: (Employee 2 has x = 5 and y = 7, which is distinct from x = 5 and y = 10).

如何将表格的文档与自身中的其他文档进行比较?

How can I compare the documents of table with other documents within itself?

推荐答案

SELECT e1.*
FROM employee e1
JOIN employee e2
  ON e1.employee_id <> e2.employee_id  
 AND e1.column2 = e2.column2 
 AND e1.X <> e2.X
 AND e1.Y <> e2.Y

但是我想你的最后一个条件应该是

But I guess your last condition should be

AND (e1.X <> e2.X OR e1.Y <> e2.Y)

这篇关于与自身联接表与使用MySQL分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！