请,有人可以帮助我,如何获取文档状态信息... 通常,我需要给定的时间段(开始日期,结束日期过滤器)来检查文档是否活动(A)或非活动(I)
Please, can someone help me, how to get information of doc status... Generally, I need for given period of time (start date, end date filters) to check if document is Active (A) or Inactive (I)
Table Documents ID Doc Date Status 1 11 1.1.2012. A 2 11 1.4.2012. I 3 11 25.4.2012. A 4 11 1.6.2012. I 5 22 18.4.2012. A 6 22 30.4.2012. I Dynamic filters: @start,@end Example: @start= 2.3.2012 @end=5.5.2012 Result should be 11 2.3.-1.4. Status=A 1.4.-25.4 Status=I 25.4.-5.5. Status=A 22 2.3.-18.4. 'not exist' 18.4-30.4. Status=A 30.4.-5.5. Status=I If filter is @start= 1.2. @end= 28.2. Result should be 11 'A' 22 'not exist' If filter is @start= 18.4. @end= 20.4. Result should be 11 'I' 22 'A'编辑:
对不起,我不想听起来像是为我做 ... 我尝试过这样的事情
Sorry, I didn't want to sound like 'do it for me'... I have tried something like this
WITH a AS ( SELECT documents.*,lag(date) OVER (PARTITION BY doc ORDER BY DATE) AS pre_date FROM documents ORDER BY DATE ) SELECT a.* from a WHERE (@start between a.pre_date AND a.date) AND (@end between a.pre_date AND a.date)这不是我所需要的。 这也是sql小提琴 sqlfiddlelink 中的示例。 我更改过滤器表以测试@start和@end的不同值
It is not quite what I need. Here is also example in sql fiddle sqlfiddlelink. I change Filter table to test for different values of @start and @end
谢谢
推荐答案基本上,@ Glenn的答案涵盖了这一点。我赞成。我仅将其发布以演示其他详细信息-太多内容无法放入评论中:
Basically, @Glenn's answer covers it. I upvoted it. I only post this to demonstrate additional details - too many to fit into a comment:
-
使用多行 INSERT 语法。
在 CTE ,这比为此创建一个额外的表要方便得多。
Provide filters in a CTE, that's much more convenient than creating an extra table for that.
此查询可以一次处理多个过滤器。
This query can handle multiple filters at once.
使用 lead(date,1,'infinity') 来消除 COALESCE 的需要。
演示一种不太复杂的输入日期文字的方法-ISO 8601格式'yyyy-mm-dd'与 any 语言环境是明确的:
Demonstrate a less convoluted way to enter date literals - The ISO 8601 format 'yyyy-mm-dd' is unambiguous with any locale:
'2012-02-03'::date或
date '2012-02-03'而不是:
to_date('2012-02-03', 'yyyy-mm-dd')
将所有内容以一种噪音较小,易读的格式
Put it all in a less noisy, more readable format
CREATE TEMP TABLE documents (id int, doc int, date date, status "char"); INSERT INTO documents VALUES (1,'11','2012-01-01','A') ,(2,'11','2012-04-01','I') ,(3,'11','2012-04-25','A') ,(4,'11','2012-06-01','I') ,(5,'22','2012-04-18','A') ,(6,'22','2012-04-30','I'); WITH filter(filter_id, start_date, end_date) AS( VALUES (1, '2012-04-18'::date, '2012-04-20'::date) ,(2, '2012-03-02'::date, '2012-05-05'::date) ) , d AS ( SELECT doc, status, date AS d1 ,lead(date,1,'infinity') OVER (PARTITION BY doc ORDER BY date) AS d2 FROM documents ) SELECT f.filter_id, d.doc ,GREATEST(f.start_date, d.d1) AS start ,LEAST(f.end_date, d.d2) AS end ,d.status FROM filter f, d WHERE f.start_date <= d.d2 AND f.end_date >= d.d1 ORDER BY f.filter_id, d.doc, d.d1;
更多推荐
日期范围之间的字段值
发布评论