仅按月和年选择数据

I have a table with month and year:maintable

我有一个月和年的表：维护

What I want is a range selection: result

我想要的是范围选择：结果

I thought between would be my friend and I tried the following:

我认为介于两者之间是我的朋友，我尝试了以下方法：

SELECT        jahr, monat, alles
FROM            dbo.table
WHERE        (jahr BETWEEN 2017 AND 2018) AND (monat BETWEEN 11 AND 2)

But this does not work. How can I get what I want?

但这不起作用。我怎样才能得到我想要的东西？

8 个解决方案

#1

I would do it like this

我会这样做的

SELECT  jahr, monat, alles 
FROM table1
WHERE cast(cast(jahr as varchar(20))+'-'+cast(monat as varchar(20))+'-01' as date) >=  '2017-11-01'
 and cast(cast(jahr as varchar(20))+'-'+cast(monat as varchar(20))+'-01' as date) <='2018-02-01'

I think this is the best solution because you having the server deal with the details -- you are taking your data model and converting to a date type and then having the server do a date compare for the filter.

我认为这是最好的解决方案，因为您让服务器处理详细信息 - 您正在使用数据模型并转换为日期类型，然后让服务器对过滤器进行日期比较。

http://sqlfiddle.com/#!18/13a69/21/0

Debug

调试

http://sqlfiddle.com/#!18/13a69/20/0

#2

Assuming that jahr and monat are integer formats you can make a key out of them and can do a comparison that way. You essentially end up with YYYYMM format.

假设jahr和monat是整数格式，你可以用它们制作一个键，并且可以用这种方式进行比较。你基本上以YYYYMM格式结束。

SELECT        jahr, monat, alles
FROM            dbo.table
WHERE (jahr*100) + monat between 201711 and 201802

#3

You can compare the two dates as simple as follows in different conditions.

您可以在不同条件下比较两个日期，如下所示。

Compare the date is above the 2017 November (mont <=12 is used as a safety check, you can ignore that if you can guarantee the month column doesn't contain values greater than 12).

比较日期是2017年11月以上（mont <= 12用作安全检查，如果可以保证月份列不包含大于12的值，则可以忽略）。

jahr >= 2017 AND monat >= 11 AND mont <=12

else you can use between jahr >= 2017 AND monat between 11 AND 12

否则你可以在jahr> = 2017和11到12之间的monat之间使用

Compare the date is below the 2018 February (mont >= 1 is used as a safety check, you can ignore that if you can guarantee the month column doesn't contain values less than 1).

比较日期低于2018年2月（mont> = 1用作安全检查，如果可以保证月份列不包含小于1的值，则可以忽略）。

jahr <= 2018 AND monat >= 1 AND mont <=2

else you can use between

否则你可以在之间使用

jahr >= 2017 AND monat between 1 AND 2

The Whole condition with simple operators

整个条件与简单的运算符

(jahr >= 2017 AND monat >= 11 AND monat <=12) AND (jahr <= 2018 AND monat >= 1 AND monat <=2)

（jahr> = 2017 AND monat> = 11 AND monat <= 12）AND（jahr <= 2018 AND monat> = 1 AND monat <= 2）

With between conditions

在条件之间

(jahr >= 2017 AND monat between 11 AND 12) AND (jahr >= 2017 AND monat between 1 AND 2)

（jahr> = 2017年和11至12之间的monat）和（jahr> = 2017年和1至2之间的monat）

Following is the exact sql for your problem. All three queries should work for your purpose.

以下是您的问题的确切sql。所有三个查询都应该适合您的目的。

-- With simple operators
SELECT        jahr, monat, alles
FROM          dbo.table
WHERE         (jahr >= 2017 AND monat >= 11 AND monat <=12) 
AND (jahr <= 2018 AND monat >= 1 AND monat <=2)

-- With simple operators (Without security boundary checks)
SELECT        jahr, monat, alles
FROM          dbo.table
WHERE         (jahr >= 2017 AND monat >= 11) 
AND (jahr <= 2018 AND monat <=2)

-- With between operator
SELECT        jahr, monat, alles
FROM          dbo.table
WHERE         (jahr >= 2017 AND monat between 11 AND 12)
AND (jahr >= 2017 AND monat between 1 AND 2)

#4

SELECT        jahr, monat, alles
FROM           dbo.table
WHERE (jahr = 2017 AND monat IN (11,12))
      OR (jahr = 2018 AND monat IN (1,2))

Output

产量

jahr    monat   alles
2017    11      105
2017    12      105
2018    1       104
2018    2       105

Demo Link

演示链接

http://sqlfiddle.com/#!18/13a69/1

http://sqlfiddle.com/#!18/13a69/1

#5

SELECT        jahr, monat, alles
FROM            dbo.table
WHERE        ( jahr = 2017 AND monat BETWEEN 11 AND 12 )
             OR 
             ( jahr = 2018 AND monat BETWEEN 1 AND 2 )

#6

You can try the following query.

您可以尝试以下查询。

SELECT        jahr, monat, alles
FROM           table1
WHERE CASE WHEN (jahr = 2017 AND monat BETWEEN 11 AND 12) THEN 1
            WHEN (jahr = 2018 AND monat BETWEEN 1 AND 2) THEN 1 
            ELSE 0 END = 1

Thanks

谢谢

#7

You could use the OR operator and some parenthesis:

您可以使用OR运算符和一些括号：

DECLARE @maintable TABLE (jahr int, monat int, alles int)
INSERT INTO @maintable VALUES
(2017, 7, 189),(2017, 8, 125),(2017, 9, 92),(2017, 10, 98),(2017, 11, 105)
,(2017, 12, 105),(2018, 1, 104),(2018, 2, 105),(2018, 3, 99)

SELECT jahr
      ,monat      
      ,alles 
  FROM @maintable
WHERE (jahr >= 2017 AND monat >= 11)
      OR
      (jahr <= 2018 AND monat <= 2)

Produces:

生产：

jahr    monat   alles
2017    11      105
2017    12      105
2018    1       104
2018    2       105

#8

-2

 SELECT jahr, monat, alles FROM dbo.test 
WHERE (jahr BETWEEN 2017 AND 2018) 
AND  monat in (11,12,1,2) 

jahr    monat   alles
2017    11  105
2017    12  105
2018    1   104
2018    2   105

#1