MySQL - 根据日期和类别拉取列表 [英] MySQL - Pulling List depending on date and category

问题描述

这是此问题的附加内容此处

我有一张这样的桌子:

ID_____PostingDate_____PosterID____CategoryID
----------------------------------------------
1______05/01/2012______450_________a
2______05/30/2012______451_________a
3______06/17/2012______451_________b
4______12/10/2012______451_________c
5______06/14/2012______452_________l
6______09/15/2012______452_________l
7______05/01/2012______453_________m
8______07/04/2012______453_________n
9______04/05/2013______454_________p
10_____05/05/2012______454_________l
11_____06/12/2012______455_________x
12_____10/02/2012______455_________x
13_____02/12/2013______455_________y

我正在尝试获取所有海报 ID(包括潜在的重复项)的列表 &在 2012 年 5 月或 6 月发布且此后未在同一类别内再次发布的类别 ID.

I'm trying to get a list of all PosterIDs (including potential duplicates) & CategoryIDs that have posted in May or June of 2012 and have not posted again since then within the same Category.

上表的预期结果:

PosterID_______PostingDate______CategoryID
--------------------------
450____________05/01/2012_______a
451____________05/30/2012_______a
451____________06/17/2012_______b
453____________05/01/2012_______m
454____________05/05/2013_______l

这是我尝试过的:

感谢@Filipe Silva

Curtesy of @Filipe Silva

SELECT DISTINCT PosterID
FROM table1
WHERE PostingDate BETWEEN '2012-05-01' AND '2012-06-30'
AND posterID NOT IN (SELECT PosterID
  FROM table1
  WHERE PostingDate > '2012-07-01');

虽然这只会删除那些在任何类别中再次发布的海报ID，而不是特定类别

though this only removes those PosterID's that have posted again in any category, not category specific

推荐答案

您必须对来自 CategoryID 字段的两个选择的结果进行交叉.我认为这应该有效:

You must cross the results from both selects on the CategoryID field. I think this should work:

SELECT DISTINCT PosterID
FROM table1 x
WHERE PostingDate BETWEEN '2012-05-01' AND '2012-06-30'
AND posterID NOT IN (SELECT PosterID
  FROM table1 y
  WHERE PostingDate > '2012-07-01' AND x.CategoryID = y.CategoryID);  

这篇关于MySQL - 根据日期和类别拉取列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！