查询选择当月周末和公众假期的月份和次数 [英] Query to select the month and nos of weekend and public holiday in that month

问题描述

我的数据库表 Holiday 包括字段 ad Holidaydate->datetime , WeekendHoliday->bit, PublicHoliday->bit我想要特定月份中的周末假期和公共假期的编号.

My database table Holiday includes field ad Holidaydate->datetime , WeekendHoliday->bit, PublicHoliday->bit I want the nos of weekendholiday and also publicholiday in a specific month year.

我的表格值为(示例)

Holidaydate       WeekendHoliday       PublicHoliday 
-----------       --------------       -------------
4/02/2012             true                 false
4/20/2012             true                 false
5/3/2012              true                 False
5/30/2012             false                true
4/05/2013             false                true   

所以现在输出应该是这样的:

So now output should be like this:

year      Month   count(weekend)   count(public)
----      -----   --------------   -------------
2012      April      2               0
2012      May        1               1
2013      April      0               1

推荐答案

     SELECT year(holidaydate),month(holidaydate), 
            sum(case Weekend when true then 1 else 0 end) wkEnd, 
            sum(case PublicHoliday when true then 1 else 0 end) pubHol
      FROM Holiday 
      GROUP BY year(holidaydate),month(holidaydate)

我没有可用的 SQL 服务器.这是在mysql上测试的.这里年和月是返回日期的年和月的函数.CASE 的语法应该跨数据库相同.

I don't have SQL server available. THis is tested on mysql. Here year and month are function to return the year and month of date. Syntax of CASE should be same across database.

这篇关于查询选择当月周末和公众假期的月份和次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！