gpt4 book ai didi

MySQL - 根据日期和类别提取列表

转载 作者:行者123 更新时间:2023-11-29 13:26:23 25 4
gpt4 key购买 nike

这是此问题的附加内容 here

我有一个这样的表:

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

我正在尝试获取 2012 年 5 月或 6 月发布的所有 PosterID(包括可能的重复项)和 CategoryID 的列表,并且此后在同一类别中未再次发布。

上表中所需的结果:

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

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');

尽管这只会删除那些在任何类别中再次发布的 PosterID,而不是特定类别

最佳答案

您必须交叉 CategoryID 字段上两个选择的结果。我认为这应该有效:

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 - 根据日期和类别提取列表,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/20077332/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com