最近出行的乘客

Question

​

​

从出行日期在过去2个月的每个月中按乘客分组的表中选择不同的乘客

当在10月-9月和8月运行查询时，应该拉取数据。当在11月-9月和10月运行时，应该提取数据。

在上面的示例中，应该拉取"passenger 1“，因为他在8月和9月都旅行过。“乘客2&3”不能拉。

Answer 1

我会使用date_trunc()进行日期过滤。然后：

select t.passenger
from t
where travel_date >= date_trunc('month', current_date) - interval '2 month' and
      travel_date < date_trunc('month', current_date)
group by t.passenger
having count(distinct date_trunc('month', travel_date)) = 2;

这使用了count(distinct)，因为它是可泛化的。例如，对于在过去6个月中有4个月出差的人，可以很容易地调整此查询。

Answer 2

我理解您作为的要求选择过去两个月(从今天起)每月至少旅行一次的乘客，不包括当月的旅行。

所需的SQL代码：

WITH cte
AS (
    SELECT passenger AS passenger
        ,MONTH(travel_date) AS travel_month
        ,COUNT(*) AS number_of_travels
    FROM [dbo].[tblTravels]
    WHERE DATEDIFF(month, travel_date, GETDATE()) <= 2
        AND MONTH(travel_date) != MONTH(GETDATE())
    GROUP BY passenger
        ,MONTH(travel_date)
    )
SELECT passenger
FROM cte
GROUP BY passenger
HAVING COUNT(*) > 1

关于answer的更多解释:从今天开始获取最近两个月，不包括当前月份

DATEDIFF(month, travel_date, GETDATE()) <= 2
        AND MONTH(travel_date) != MONTH(GETDATE())

如果要验证代码，只需运行以下脚本：：

CREATE TABLE [dbo].[tblTravels] (
    [passenger] [int] NULL
    ,[travel_date] [datetime] NULL
    ,[travel_id] [nchar](10) NULL
    ) ON [PRIMARY]

DELETE FROM [dbo].[tblTravels]

INSERT INTO [dbo].[tblTravels] VALUES (1, '2020-08-01', 'a')
INSERT INTO [dbo].[tblTravels] VALUES (1, '2020-09-01', 'a')
INSERT INTO [dbo].[tblTravels] VALUES (2, '2020-08-01', 'a')
INSERT INTO [dbo].[tblTravels] VALUES (3, '2020-09-01', 'a')
GO

SELECT *
FROM [dbo].[tblTravels]
GO

WITH cte
AS (
    SELECT passenger AS passenger
        ,MONTH(travel_date) AS travel_month
        ,COUNT(*) AS number_of_travels
    FROM [dbo].[tblTravels]
    WHERE DATEDIFF(month, travel_date, GETDATE()) <= 2
        AND MONTH(travel_date) != MONTH(GETDATE())
    GROUP BY passenger
        ,MONTH(travel_date)
    )
SELECT passenger
FROM cte
GROUP BY passenger
HAVING COUNT(*) > 1

DROP TABLE [dbo].[tblTravels]

Answer 3

使用下面的查询

Select passenger FROM travel
WHERE  DATEDIFF(month, travel_date, CURRENT_TIMESTAMP)<=2
group BY passenger

对于获取当前日期，可以使用getdate()或current_timestamp.

如果你想要完整的细节，你不需要从group by中获取。

Select passenger,travel_date,travel_id FROM travel
WHERE  DATEDIFF(month, travel_data, CURRENT_TIMESTAMP)<=2
order by passenger