需要大量的预检查，但从本质上讲，我必须一次基于一个步骤来构建SQL变量，就好像它是在一个步骤中一样.通过创建内部最@SQLVars变量，一旦声明了第一个变量，就可以将其用作下一个变量的基础，依此类推. ..首先，这是完整查询，您可以将其应用于基于当前日期构建的数据.您可能会更好地了解自己的数据，但是我认为这样做可以使您顺利进行.

It takes a lot of pre-checking, but in essence, I had to build SQL variables based on one step at a time, as if it was in a program of "let X = something", "let y = X + somethingelse", etc. By building the inner most @SQLVars variables, once the first is declared, it can be used as the basis of the next variable and so on... First, here's the full query that you can apply to your data that builds based on whatever the current date is. You knowing your data better may have to tweak it some, but I think this gets you well on your way.

select
      CONCAT( 'Q (', LEFT( MonthName( DateBasis.dMonth1 ), 3 ), ' ', RIGHT( Year( DateBasis.dMonth1 ), 2 ), ')' ) as FirstMonth,
      CONCAT( 'U (', LEFT( MonthName( DateBasis.dMonth2 ), 3 ), ' ', RIGHT( Year( DateBasis.dMonth2 ), 2 ), ')' ) as SecondMonth,
      CONCAT( 'V (', LEFT( MonthName( DateBasis.dMonth3 ), 3 ), ' ', RIGHT( Year( DateBasis.dMonth3 ), 2 ), ')' ) as ThirdMonth,
      CONCAT( 'X (', LEFT( MonthName( DateBasis.dMonth4 ), 3 ), ' ', RIGHT( Year( DateBasis.dMonth4 ), 2 ), ')' ) as FourthMonth
   from   
      ( select @FirstOfMonth dFirstOfMonth,
               @FDOM nWeekDay,
               @SWOM nSecondWedOfMonth,
               @SkipMonths nSkip,
               @Month1 dMonth1,
               @Month2 dMonth2,
               @Month3 dMonth3,
               @Month4 dMonth4
           from
              ( select @FirstOfMonth := CONCAT( year(curdate()), '-', month( curdate()), '-01' ),
                       @FDOW := DayOfWeek( @FirstOfMonth ),
                       @SWOM := if( @FDOW <= 4, 12, 19) - @FDOW,
                       @SkipMonths := if( day( CurDate()) <= @SWOM, 1, 2 ),
                       @Month1 := date_add( @FirstOfMonth, interval 0 +  @SkipMonths month ),
                       @Month2 := date_add( @Month1, interval 1 month ),
                       @Month3 := date_add( @Month2, interval 1 month ),
                       @Month4 := date_add( @Month3, interval 1 month )
                       ) sqlvars
      ) DateBasis

上述查询的结果将返回一个SINGLE记录(基于1月31日的当前日期)以显示 第一个月第二个月第三个月第四个月 Q(3月12日)U(4月12日)V(5月12日)X(6月12日)

The result of this one query above will return a SINGLE record (based on current date of Jan 31) to show FirstMonth SecondMonth ThirdMonth FourthMonth Q (Mar 12) U (Apr 12) V (May 12) X (Jun 12)

现在，将其嵌套在查询的其余部分中，以获得类似代码的报价

Now, nest this inside the rest of your query for your ticker IDs something like

SELECT hist.date, 
       hist.ticker_id, 
       hist.settle_price, 
       hist.volume 
   FROM 
      hist,
      ( entire select statement above ) FinalDates

   WHERE 
          hist.ticker_id IN ( FinalDates.FirstMonth,
                              FinalDates.SecondMonth,
                              FinalDates.ThirdMonth,
                              FinalDates.FourthMonth )
      and hist.trade_dt = curdate()

如果您查看前面提到的最里面的@SqlVariables，就像一堆"let x = something".我总是需要一个开始的基础，因此，我首先通过将当前日期的年份+-" +当前日期的月份+组合起来，将给定月份的第一天添加到变量@FirstOfMonth中. "-01"始终在每月的第一天开始.例如:今天是2012年1月31日，它将构建字符串"2012-01-01"，该字符串将以年/月/日的格式立即被MySQL识别.作为日期格式，我们可以对日期进行算术运算.所以现在，我有@FirstOfMonth ='2012-01-01'.现在，我们需要确定该日期的第一个星期几代表我们所在的月份(因此@FDOW).这将返回1-7之间的值(星期日= 1，星期三= 4，星期六= 7).

If you look at the innermost @SqlVariables as mentioned earlier is like a bunch of "let x=something". I always need a basis to begin, so I'm first getting the first day of a given month into a variable @FirstOfMonth by doing a concatenation of whatever the year is of the current date + "-" + month of the current date + "-01" to always start at the first of the month... ex: Today is Jan 31, 2012 will build out a string of '2012-01-01' which in year/month/date format is immediately recognized by MySQL as a date format we can perform date arithmetic on. So now, I have @FirstOfMonth = '2012-01-01'. Now, we need to determine the first Day of Week this date represents of the month we are in (hence @FDOW). This will return a value from 1-7 (Sunday = 1, Wed = 4, Sat = 7).

现在，我们需要计算该月的第二个星期三何时.如果星期几是星期天到星期三(包括星期三)，则第二星期三是减去星期几的12天.例如:第一个星期天是4日，然后是第11个星期三...所以12-1(星期日)=11.如果一个月的第一天是一个星期三，那将是星期几= 4，但是该月的第一天=周三，第二个周三= 8，所以12-4 =8.现在，如果日期是本月的第一天是星期四，周五或周六，则星期几将是5、6或7 .第一个星期三的最短日期为7，第二个星期三的最晚日期为14，所以这是从19开始-一周中的任何一天... 5，6，7 ...例如:19-5(星期四)周)= 14、19-6(星期五)= 13、19-7(周六)=.因此，我们知道第一个星期三将是整周，因此最早be是第7和14，而不是第1和8(月初).

From this, we now need to compute when the 2nd Wednesday of the month will be. If the day of week is Sunday to (and including) Wednesday, the SECOND Wednesday is 12 days MINUS the day of week. Ex: Sunday the 1st would be Wed 4th, then Wed the 11th... so 12 - 1 (Sunday) = 11. If the first day of the month WAS a Wed, it would be a day of week = 4, but the 1st of the month = Wed, the second Wed = 8, so 12 - 4 = 8. Now, if the date was Thu, Fri or Sat as the first of the month, the Day of Week would be a 5, 6 or 7. The MINIMUM Date of the first Wednesday would be 7, the second Wed would be 14, so this is starting with 19 - whatever day of week... 5, 6, 7... Ex: 19 - 5(Thu Day of Week) = 14, 19 - 6(Fri Day of Week) = 13, 19 - 7(Sat Day of Week) = 12.. So, we know that the first Wed will be the full week out, so the earliest it would be is 7th and 14th as opposed to 1st and 8th (earliest of month).

现在我们知道该月的第二个星期三是什么时候，将其与我们基于其运行查询的日期进行比较(即:curdate()).如果当前日期为MONTH(@SWOM)的第二个星期三(@&=;)之前的日期，那么我们只想跳过1个月...如果本月还有几天，则需要跳过2个月

Now that we know WHEN the 2nd Wednesday of the month is, compare that to the date we are running the query based on (ie: curdate() ). If the current date is ON or BEFORE (via <=) the SECOND WED of MONTH (@SWOM), then we only want to skip over 1 month... if we are further in the month, we need to skip 2 months.

现在，建立日期.第1个月的日期基础是当前月份的第一天，再加上可能要跳过的多个月的时间间隔.第二个月是第一个月的一个月，第三个月是第二个月的一个月，第四个月是第三个月的一个月.

Now, build out the dates. The date basis for Month 1 is the first of the current month PLUS an interval of however many months to skip. Month 2 is one month past the first, Month 3 one past Month 2, and Month 4 one past Month 3.

@FirstOfMonth := CONCAT( year(curdate()), '-', month( curdate()), '-01' ),
@FDOW := DayOfWeek( @FirstOfMonth ),
@SWOM := if( @FDOM <= 4, 12, 19) - @FDOM,
@SkipMonths := if( day( CurDate()) <= @SWOM, 1, 2 ),
@Month1 := date_add( @FirstOfMonth, interval 0 +  @SkipMonths month ),
@Month2 := date_add( @Month1, interval 1 month ),
@Month3 := date_add( @Month2, interval 1 month ),
@Month4 := date_add( @Month3, interval 1 month )

因此，我们终于有了所有四个月的基础，可以在(select ...)sqlvars结果集中的一行中显示类似的内容

So we finally have all 4 months basis to work with in a single row of ( select ... ) sqlvars result set showing something like

@Month1     @Month2     @Month3     @Month4
2012-03-01  2012-04-01  2012-05-01  2012-06-01 ... the four months out

最后，一旦此数据显示正常，我们现在可以使用相应的"Q"，"U"，"V"和"X"前缀以及月份名称的左3来构建您要查找的特定字符串.与两位数的年份.

Finally, once this data appears ok, we can now build out the specific strings you are looking for with the respective "Q", "U", "V" and "X" prefixes plus the left 3 of the month name with the 2 digit year.

因此，通过此操作，您可以获取所有期望的日期范围和字符串，然后像我在首字母中列出的那样，针对您的其他表进行查询.

So, with this getting all the date ranges and strings you are expecting, query this against your other table as I listed in the initial.

我希望这对您有所帮助，并让您打开一个全新的上下文来欺骗SQL，以便……本质上可以做一个内联程序来创建许多变量并从中进行操作……很酷吧……

I hope this helps you and opens your eyes to a completely new context to trick SQL to... in essence doing an inline program to create many variables and operate from that... Pretty cool huh...

尽管老实说，这是我第一次专门尝试这种技术，尽管过去我使用SQLVars进行过许多查询.

And in all truthfulness, this is the first time I've specifically tried this technique, although I've done many queries in the past using SQLVars.