查询以显示两个表中指定月份和一个月之前的匹配记录 [英] query to show matching records from two tables for a specified month and one month prior

问题描述

我需要显示来自两个表的匹配记录.该参数是用户将以数字形式输入的月份，即1到12.现在一个月和一年没有问题，但是我需要显示两个月的记录，例如，如果用户输入1/2011，那么我需要显示1/2011和12/2010的匹配记录，即用户输入月份的匹配记录和该用户输入的前一个月的匹配记录.我认为第1个月不是一个好的解决方案.有没有人有更好的解决方案.在此先感谢

i need to show matching records from two table. the parameter is month which the user will enter in numeric i.e. 1 to 12.now for a single month and year there is no problem, but i need to show two months records for e.g if the user enters 1/2011 then i need to show the matching records for 1/2011 and 12/2010 i.e matching records from the month entered by user and one month previous from that. month-1 is not a good solution i think. does any one have better solution. thanks in advance

推荐答案

如果这是SQL Server，则可以使用例如 ^ ].

[添加]
您的意思是这样的吗:

If this is SQL Server, you can use for example DATEADD[^].

[Addition]
Do you mean something like this:

select convert(date, '1/1/2011', 103) as OriginalInput ,
       dateadd(m, -1, convert(date, '1/1/2011', 103)) as LowerBoundary,
       dateadd(m, +1, convert(date, '1/1/2011', 103)) as UpperBoundary

示例中的日期部分将是一个常量，始终为1.有了这些边界，您可以搜索记录，例如:

The day portion in the example would be a constant, always 1. With those boundaries you could search records for example like:

SELECT ...
FROM ...
WHERE SomeDateField >= dateadd(m, -1, convert(date, '1/1/2011', 103))
AND   SomeDateField <  dateadd(m, +1, convert(date, '1/1/2011', 103))

I found the answer i can write query this way

select ------where(months=@months and years=@years) or (months=@months-1 and years=@years-1) where @months and @years are the parameters user is entering, and it is working in any case

thanks for ur consideration

这篇关于查询以显示两个表中指定月份和一个月之前的匹配记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！