选择在同一列中具有不同值的记录，在没有子查询的另一列中选择不同的值

I have this table:

我有这张桌子:

item        year
----        ----
1           1999 
2           1999
3           1999 
2           2000 
4           2000 
2           2001
3           2001
4           2001
5           2001

And I need this result (with 1999 selected)

我需要这个结果(选择1999年)

item        year
----        ----
4           2000
4           2001
5           2001

To sum up, I need to obtain new items which aren't in 1999, but which are defined in another different year (this is for adding them with another query later).

总而言之,我需要获得不在1999年但在另一个不同年份定义的新项目(这是为了稍后再添加它们)。

I'm using an old MySQL version and I'm not able to use subqueries. Is there any compatible solution?

我使用的是旧的MySQL版本,但我无法使用子查询。有兼容的解决方案吗?

2 个解决方案

#1

What about something like this

这样的事情怎么样?

SELECT t1.item, t1.year from table as t1
LEFT JOIN table as t2 ON t2.item = t1.item AND t2.year = 1999
WHERE t2.item IS NULL

In "english" it means, select all the items from my table. Then for each item try to find if they are in the table with the year 1999, else put NULL. Then you just have to say "keep the elements that do not have a matching element in t2.

在“英语”中,它意味着,从我的表中选择所有项目。然后为每个项目尝试查找它们是否在1999年的表中,否则将其设置为NULL。然后你只需要说“保留t2中没有匹配元素的元素。

Not sure if my explanation is better anyway

不知道我的解释是否更好

EDIT: you of course need to replace table with the name of your own table. I tested it on a small example and it seems to work.

编辑:您当然需要将表替换为您自己的表的名称。我在一个小例子上测试它似乎工作。

EDIT2 : If you look at the result of the LEFT JOIN you would have something like this :

编辑2:如果你看一下LEFT JOIN的结果,你会得到这样的结果:

t1.item     t1.year    t2.item     t2.year
----        ----       ----        ----       
1           1999       1           1999
2           1999       2           1999
3           1999       3           1999
2           2000       2           1999
4           2000       NULL        NULL
2           2001       2           1999
3           2001       3           1999
4           2001       NULL        NULL
5           2001       NULL        NULL

Then if you add WHERE t2.item IS NULL you only keep the ones not having a year 1999 in the table. Is it a better way to understand it?

然后,如果你添加WHERE t2.item IS NULL,你只保留表中没有1999年的那些。这是理解它的更好方法吗?

#2

select t2.item, t2.year
from mytable t1
left join mytable t2 
    on t2.year > t1.year 
    and t2.item != t1.item
where t1.year = 1999
and t2.item is null

#1