基于单个日期的每年查询计数 [英] Query Count for each year based on single date

问题描述

我被要求根据我们已经收集的信息提供一些关于初学者/离开者的报告.

I am being asked to provide some reporting on Starter/Leavers based on information we are already collecting.

我想弄清楚这是否可以由 MySQL 本身完成，或者我是否需要进行多次调用.

I am trying to figure out whether this can be done by the MySQL itself, or if I will need to make multiple calls.

最简单的数据结构是:

ID    psName     dtStart     dtLeave

(这是由包含 Office、Status 等的多个表生成的，我们可能会使用这些表进行更复杂的查询)

(This is produced from multiple tables that pull in Office, Status, etc, which we might use for more complicated queries)

对于给定的日期，我可以使用以下查询来获取公司的人数:

For a given date I can use the following Query to get the number of people in the company:

SELECT COUNT(*) FROM
(
  SELECT ID, psName, dtStart, dtLeave
  FROM people
) st
WHERE (dtStart <= "2016-06-04" ) 
AND (dtLeave >= "2016-06-04" OR dtLeave IS NULL) 

我希望能够输入一个日期，例如2016-04-06"并获取该日期历史上的计数列表(以列或行的形式)，即

I would like to be able to feed in a date, for example "2016-04-06" and get a list list of counts (either as columns or rows) for that date historically i.e.

6/4/2016 200
6/4/2015 175
6/4/2014 150
6/4/2014 125
6/4/2013 100

这有意义吗?如果有，可能吗?

Does this make sense, and if so, is it possible?

推荐答案

注意没有开始和结束日期，这个查询会扫描所有表记录.

Notice that there is no start and end date, this query will scan all table records.

事实上，您可以将查询简化为:

In fact, you can reduce your query to:

SELECT YEAR(dtStart) as 'year', COUNT(*) FROM
FROM people
WHERE MONTH(dStart) = 4 AND DAY(dStart) = 6
GROUP BY YEAR(dtStart);

更新

要让人们在选定的时间段内就业:

To get people employed on the selected period:

SET @SelectedDate = '2016-04-06';

SELECT YEAR(dStart), COUNT(*), @SelectedDate FROM
(
  SELECT ID, psName, dtStart, dtLeave
  FROM people
  WHERE DATE_FORMAT(dtStart, '%m%d') <= DATE_FORMAT(@SelectedDate, '%m%d')
        AND DATE_FORMAT(dtleave, '%m%d') >= DATE_FORMAT(@SelectedDate, '%m%d')
) st
GROUP BY YEAR(dStart);

这篇关于基于单个日期的每年查询计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！