用于生成给定数据的SQL查询

I have a table called student with two columns:

我有一个名为student的表,有两列:

JoinDate DATE
EmployeeName STRING

The contents of the table are as follows:

表格内容如下:

EmployeeName    | JoinDate
----------------+-----------------
jaison          | 1-jan-2008
robin           | 2-feb-2008
binoy           | 3-mar-2008
rahul           | 4-feb-2008

I am looking to prepare the following output based on this table: a table containing 4-columns with names jan,feb,mar,april. Beneath each of these months a count is given as 1,2,1,0. These counts represent the number of employees that joined in that month (January 1 employee, February 2 employees, March 1 employee, April 0 employees)

我希望基于此表准备以下输出:包含名称为jan,feb,mar,april的4列的表。在这几个月的每个月之下,计数为1,2,1,0。这些计数表示当月加入的员工人数(1月1日员工,2月2日员工,3月1日员工,4月0日员工)

Can you give me the required SQL query?

你能给我所需的SQL查询吗?

3 个解决方案

#1

This will work, anyone know any other way?

这会有效,任何人都知道吗?

SELECT Sum([1]) AS Jan, Sum([2]) AS Feb, Sum([3]) AS Mar, Sum([4]) as Apr,
       Sum([5]) as May, Sum([6]) as Jun, Sum([7]) as Jul, Sum([8]) as Aug,
       Sum([9]) as Sep, Sum([10]) as Oct, Sum([11]) as Nov, Sum([12]) as Dec
FROM (SELECT Month(Join_Date) as Mon FROM student) ps
PIVOT
    (Count(Mon) FOR Mon IN 
        ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
    ) AS pvt
GROUP BY [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]

#2

The following query will give you a result set of two columns (month and total), where month is the month name and total is the number of people that joined that month:

以下查询将为您提供两列(月份和总计)的结果集,其中月份是月份名称,总数是该月份加入的人数:

SELECT MONTHNAME(`join_date`) AS `month`, COUNT(*) AS `total` GROUP BY MONTHNAME(`join_date`);

#3

This also will work

这也行

Create table [sample] (Employeename varchar(50), join_date smalldatetime) insert into [sample] values('Ganesh','01/26/2008') insert into [sample] values('Ramesh','02/26/2008') insert into [sample] values('Dinesh','03/26/2008') insert into [sample] values('Suresh','01/26/2008')

创建表[sample](Employeename varchar(50),join_date smalldatetime)插入[sample] values('Ganesh','01/26/2008')insert into [sample] values('Ramesh','02/26 / 2008')插入[样本]值('Dinesh','03/26/2008')插入[样本]值('Suresh','01/26/2008')

Select * from [sample]

从[样本]中选择*

SELECT 'Students Admission by Monthwise' ,[1] AS january,[2] AS february,[3] AS March,[4] AS April FROM( SELECT Employeename, Month(join_date) monthname FROM [sample] ) A PIVOT ( COUNT(Employeename) FOR monthname in ([1],[2],[3],[4]) ) AS PVT

选择'逐月入学',[1] AS 1月,[2] AS 2月,[3] AS March,[4] AS April FROM(SELECT Employeename,Month(join_date)monthname FROM [sample])A PIVOT(COUNT) (Employeename)for monthname in([1],[2],[3],[4]))AS PVT

#1