选择指定年份的新商品或退货商品 [英] Select new or returning items for a specified year

问题描述

我有一个大致像这样的表:

I have a table which looks roughly like this:

+---------------------+----------+
| date                | item     |
+---------------------+----------+
| 2008-11-30 11:15:59 | Plums    |
| 2012-11-08 19:42:37 | Lemons   |
| 2013-01-30 18:58:07 | Apples   |
| 2013-02-12 13:44:45 | Pears    |
| 2014-06-08 11:46:48 | Apples   |
| 2014-09-01 20:28:03 | Oranges  |
+---------------------+----------+

我现在要为两种情况选择项目:

I would now like to select items for two cases:

1)我想选择今年(或指定的不同年份)的所有不同项目，这些项目今年都是新的，这意味着它们在前几年没有出现.因此，该查询应将2014年的结果显示为橙色".

1) I'd like to select all distinct items for this year (or a different specified year) which are NEW this year, meaning that they have not appeared in previous years. This query should therefore give me "Oranges" as a result for 2014.

2)我还想选择本(或另一个指定的)年份中所有属于RETURNING的不同项目，这显然意味着它们已出现在前几年中.该查询应该给我苹果"作为2014年的结果.

2) I'd also like to select all distinct items for this (or another specified) year which are RETURNING, which obviously means that they have appeared in previous years. This query should give me "Apples" as a result for 2014.

为澄清起见，我只想将这两个查询用于与往年的比较.因此，如果我将2013指定为运行该查询的年份，则"Apples"应显示为新的，而不是返回.

To clarify, I would like to use both queries only for comparison to previous years. So if I were to specify 2013 as the year for which this query is run, "Apples" should appear as new, not returning.

我并不一定要在一个查询中同时使用这两种方法(我怀疑是否有可能)，因此两个不同的查询(每种情况一个)非常好.

I don't necessarily need both things in one query (I doubt it's possible), so two different queries, one for each case, are perfectly fine.

推荐答案

如何(( < NEW 项的strong> SQL Fiddle ):

How about (SQL Fiddle) for NEW items:

SELECT DISTINCT m1.item
FROM MyTable m1
  LEFT JOIN MyTable m2 ON m1.item = m2.item AND YEAR(m2.date) < 2014
WHERE YEAR(m1.date) = 2014
  AND m2.date IS NULL

并且( SQL小提琴 )用于返回项:

SELECT DISTINCT m1.item
FROM MyTable m1
  INNER JOIN MyTable m2 ON m1.item = m2.item
WHERE YEAR(m1.date) = 2014
  AND YEAR(m2.date) < 2014

如果您要将两个查询组合在一起( SQL Fiddle 或 SQL小提琴 ):

If you want to combine the two queries together (SQL Fiddle or SQL Fiddle):

SELECT DISTINCT m1.item, 'New' AS Status
FROM MyTable m1
  LEFT JOIN MyTable m2 ON m1.item = m2.item AND YEAR(m2.date) < 2014
WHERE YEAR(m1.date) = 2014
  AND m2.date IS NULL
UNION ALL
SELECT DISTINCT m1.item, 'Returning' AS Status
FROM MyTable m1
  INNER JOIN MyTable m2 ON m1.item = m2.item
WHERE YEAR(m1.date) = 2014
  AND YEAR(m2.date) < 2014
ORDER BY Status;

这篇关于选择指定年份的新商品或退货商品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！