选择每月的在职员工，日期格式为dd/mm/yyyy

我很难通过写作来解释这一点，所以请耐心等待。

我正在做这个项目，在这个项目中，我必须选择一个月和一年来了解一年中那个月的所有在职员工。。但在我的数据库中，我以dd/mm/yyyy格式存储它们开始和结束的日期

所以，如果我有一个工作了4个月的员工，例如从2013年1月1日到2013年5月1日，我会在四个月后找到他。我需要让他和其他在这几个月里活跃的员工一起出现在4张桌子上（每活跃一个月一张）。在这种情况下，它们将是：2013年1月、2月、3月和4月。

问题是我不知道如何在这里进行查询或php处理来实现这一点。

我所能想到的是类似于（我会对每个月运行这个查询，将年份和月份作为参数）

pg_query= "SELECT employee_name FROM employees
           WHERE month_and_year between start_date AND finish_date"

但这是不可能的，主要是因为month_and_year必须是列而不是变量
有人有想法吗？

更新

是的，我很抱歉忘记说我使用了DATE作为数据类型。

我找到的最简单的解决方案是使用EXTRACT

select * from employees where extract (year FROM start_date)>='2013'
AND extract (month FROM start_date)='06' AND extract (month FROM finish_date)<='07'

这给了我2013年6月的所有记录，你肯定可以为你喜欢的任何变量替换文字变量

不需要创建一个范围来进行重叠：

select to_char(d, 'YYYY-MM') as "Month", e.name
from
    (
        select generate_series(
            '2013-01-01'::date, '2013-05-01', '1 month'
        )::date
    ) s(d)
    inner join
    employee e on
        date_trunc('month', e.start_date)::date <= s.d
        and coalesce(e.finish_date, 'infinity') > s.d
order by 1, 2

SQL Fiddle

如果希望显示没有在职员工的月份，则将inner更改为left join

欧文，关于你的评论：

第二个表达式必须是coalesce(e.finish_date, 'infinity') >= s.d

注意要求：

因此，如果我有一名员工工作了4个月，例如从2013年1月1日到2013年5月1日，我将在4个月内找到他

从这一点我了解到，最后一个活跃的日子确实是从结束的前一天。

如果我使用您的"修复"，我将在示例中的05月份包括员工f。他完成了2013-05-01:

('f', '2013-04-17', '2013-05-01'),

SQL Fiddle与您的修复

假设您确实不是将日期存储为字符串，而是仅以这种方式输出日期，那么您可以执行以下操作：

SELECT employee_name
FROM employees
WHERE start_date <= <last date of month> and
      (finish_date >= <first date of month> or finish_date is null)

如果你以这种格式存储它们，那么你可以花上几年或几个月的时间
此版本将"日期"转换为"YYYYMM"形式的字符串。只需像这样表达你想要的月份，你就可以进行比较：

select employee_name
from employees e
where right(start_date, 4)||substr(start_date, 4, 2) <= 'YYYYMM' and
      (right(finish_date, 4)||substr(finish_date, 4, 2) >= 'YYYYMM' or finish_date is null)

注意：表达式'YYYYMM'表示您要查找的月份/年份。

首先，您可以使用generate_series()轻松生成多个日期间隔。要获得下限和上限，请在开始时添加1个月的间隔：

SELECT g::date                       AS d_lower
    , (g + interval '1 month')::date AS d_upper
FROM  generate_series('2013-01-01'::date, '2013-04-01', '1 month') g;

产品：

  d_lower   |  d_upper
------------+------------
 2013-01-01 | 2013-02-01
 2013-02-01 | 2013-03-01
 2013-03-01 | 2013-04-01
 2013-04-01 | 2013-05-01

时间范围的上边界是下个月的第一个。这是有意的，因为我们将进一步使用标准的SQLOVERLAPS运算符。引用上述位置的手册：

每个时间段被认为代表半开放间隔开始<=时间<结束[…]

接下来，您使用LEFT [OUTER] JOIN将员工连接到以下日期范围：

SELECT to_char(m.d_lower, 'YYYY-MM') AS month_and_year, e.*
FROM  (
   SELECT g::date                       AS d_lower
       , (g + interval '1 month')::date AS d_upper
   FROM   generate_series('2013-01-01'::date, '2013-04-01', '1 month') g
   ) m
LEFT   JOIN employees e ON (m.d_lower, m.d_upper)
                  OVERLAPS (e.start_date, COALESCE(e.finish_date, 'infinity'))
ORDER  BY 1;

LEFT JOIN包括日期范围，即使没有找到匹配的员工。
对于没有finish_date的员工，请使用COALESCE(e.finish_date, 'infinity'))。他们被认为仍在工作。或者可以使用current_date来代替infinity。
使用to_char()可以获得格式良好的month_and_year值。
您可以轻松地从employees中选择任何需要的列。在我的例子中，我取所有带有e.*.的列
ORDER BY 1中的1是用于简化代码的位置参数。按第一列month_and_year排序。

要快速实现，请在这些表达式上创建多列索引。像

CREATE INDEX employees_start_finish_idx
ON employees (start_date, COALESCE(finish_date, 'infinity') DESC);

请注意第二个索引列的降序。

如果您已经犯了用模式'DD/MM/YYYY'而不是date、timestamp或timestamptz将时间数据存储为字符串类型（text或varchar）的愚蠢行为，请使用to_date()将字符串转换为日期。示例：
```
SELECT to_date('01/03/2013'::text, 'DD/MM/YYYY')
```
将查询的最后一行更改为：
```
...
OVERLAPS (to_date(e.start_date, 'DD/MM/YYYY')
         ,COALESCE(to_date(e.finish_date, 'DD/MM/YYYY'), 'infinity'))
```
你甚至可以有一个这样的函数索引。但是实际上，您应该使用date或timestamp列。

相关内容

最新更新

热门标签：