选择在一列中相同但在另一列中不同的行

X    Y    DATE
1    20   20120101
1    21   20120101
2    30   20120201
3    40   20120201
3    41   20120301

I want to select any rows that have another row where X is the same, but the date is different, i.e. the answer would be

我想要选择任何有另一行的行，其中X是相同的，但是日期不同，也就是说答案是

3    40   20120201
3    41   20120301

3 个解决方案

#1

Try this...

试试这个…

SELECT *
FROM YourTable
WHERE X IN (
  SELECT T1.X
  FROM YourTable T1 INNER JOIN
       YourTable T2 ON T1.X = T2.X
  WHERE T1.DATE <> T2.DATE
);

This should work in most ANSI-compliant database products.

这应该适用于大多数与ansi兼容的数据库产品。

#2

select distinct t1.*
  from table t1
  join table t2
    on (t1.X = t2.X and t1.date <> t2.date);

#3

declare @t table(X int, Y int, DATE CHAR(8))
insert @t values
(1, 20, '20120101' ),
(1, 21, '20120101' ),
(2, 30, '20120201'),
(3, 40, '20120201'),
(3, 41, '20120301')

select x,y, date, maxy, miny from
(
  select *, max(date) over (partition by x) maxdate, 
  min(date) over (partition by x) mindate
  from @t
) a
where mindate <> maxdate

#1