如何根据其列编写查询 [英] how to write a query based on its column

问题描述

我有一个名为TableA的表，如下所示；

I have a table named TableA, as below;

+----+---------------+--------------+--------------+
| Id |  Frequency    | Inserted_Date| Eligibility  |
+----+---------------+--------------+--------------+
|  1 | Halfyearly    |   23/8/2013  |              |
|  2 | Quaterly      |   24/11/2013 |              |
+----+---------------+--------------+--------------+

此处资格"列最初为空.我需要编写一个查询，其中根据当前月份的频率将资格"设置为是"/否".

Here Eligibility column is initially empty.I need to write a query where Eligibility is set to be yes /no based on the frequency for current month.

对于第一条记录， 截至今天为止Eligibility =否

for 1st record, as of today Eligibility=No

对于第二条记录， 截至今天为止Eligibility =是

for 2nd record, as of today Eligibility=Yes

+----+---------------+--------------+--------------+
| Id |  Frequency    |Inserted_Date | Eligibility|
+----+---------------+--------------+--------------+
|  1 | Halfyearly    |   23/8/2013  |   No         |
|  2 | Quaterly      |   24/11/2013 |   Yes        |
+----+---------------+--------------+--------------+

我不知道如何编写查询以获取上述输出，而且我每个月都需要更改它.

I have no idea how to write a query for getting the above output and more over i need to change it for every month.

请帮帮我.

在此先感谢大家的答复.

Thank you all in advance for your response.

推荐答案

我的理解方式，如果Inserted_Date = Jan 2013和Frequency = 'Halfyearly'，则当当前月份为Jan 2013, Jul 2013, Jan 2014, Jul 2014 etc..

The way I understand it, if Inserted_Date = Jan 2013 and Frequency = 'Halfyearly', then Eligibility = 1 when the current month is Jan 2013, Jul 2013, Jan 2014, Jul 2014 etc..

UPDATE TableA
SET Eligibility = CASE WHEN (Frequency = 'Halfyearly' 
                            AND MONTH(Inserted_Date) % 6 = MONTH(NOW()) % 6) 
                         OR (Frequency = 'Quarterly' 
                            AND MONTH(Inserted_Date) % 3 = MONTH(NOW()) % 3)
                      THEN 'Yes'
                      ELSE 'No' 
                 END

如果您也有每年"，则只需检查

If you have Annually as well you can just check

MONTH(Inserted_Date) = MONTH(NOW())

请参见 SQLFiddle

这篇关于如何根据其列编写查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！