比较同一表中相同的列

Question

如果我在table1中有这样的数据

column1 column2 date
111     00      2016-10-11
111     00      2016-10-11
111     20      2016-10-12
111     20      2016-10-12
222     00      2016-10-11
222     20      2016-10-12
333     20      2016-10-11
333     20      2016-10-12

我想构建一个查询，它只选择333作为重复，因为column1 = column1 (333=333)，column2 = column2 (20 = 20)和date <> date (2016-10-11 <> 2016-10-12 )谢谢

Answer 1

看来自我加入会有效..。

SELECT T1.*, T2.*
FROM Table1 T1
INNER JOIN table2 T2
 on T1.Column1 = T2.Column1
and T2.column2 = T2.Column2
and T1.Date <> T2.Date

或存在(更快但只访问T1数据)

SELECT T1.*
FROM Table1 T1
WHERE Exists (Select 1 
              from table1 T2 
              where T1.Column1 = T2.Column1
                and T2.column2 = T2.Column2
                and T1.Date <> T2.Date)

Answer 2

select column1 from table1 t
join table1 jt
  on t.column1 = jt.column1
  and t.column2 = jt.column2
  and jt.date <> jt.date

Answer 3

使用自连接

 select t1.column1
 from my_table t1
 inner join my_table t2
 on t1.column1 = t2.column2
 and t1.ccolumn2 = t2.column2
 and t1.date <> t2.date