如何计算P&在sqlserver中 [英] How to count P & a in sqlserver
本文介绍了如何计算P&在sqlserver中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
请帮助别人!
我有一张名为Mark_Attendance的表格,其中包含
Please help someone!
I have a table in name of Mark_Attendance which contains columns like
[Class] | [S_Adm_No] | [Alias], | [DateTime]
要获得我的结果,我使用的是SQL查询,例如
To get my result I am using a sql query like
SELECT * FROM(SELECT [Class] ,[S_Adm_No], [Alias],DATENAME(M, [DateTime])as [Month],
DAY([DateTime]) as [DayValue] FROM [Mark_Attendance]) as Composite PIVOT (MAX([Alias])
FOR [DayValue] IN ([1], [2], [3], [4],[5], [6], [7], [8], [9],[10], [11], [12], [13], [14],[15],
[16], [17], [18], [19],[20], [21], [22], [23], [24],[25], [26], [27], [28], [29],[30],[31]))
AS PivotTable where [Month] = 'May' and Class ='Nur-A'
此查询显示的值类似于
This query is showing value like
Class | S_Adm_NO | Month | 1 | 2 | 3 | 4 | 5 | 6 | 7 | and so on
Nur-A | 1101 | April | A | P | P | P | P | A | A |
Nur-A | 1102 | April | P | P | P | P | P | A | A |
Nur-A | 1103 | April | A | P | A | P | P | P | P |
我的问题是我如何分别计算P和A.
My question is how can I count P and A separately like.
Class | S_Adm_NO | Month | 1 | 2 | 3 | 4 | 5 | 6 | 7 | Total_P | Total_A
Nur-A | 1101 | April | A | P | P | P | P | A | A | 4 | 3
Nur-A | 1102 | April | P | P | P | P | P | A | A | 5 | 2
Nur-A | 1103 | April | A | P | A | P | P | A | A | 3 | 4
我的尝试:
What I have tried:
SELECT * FROM(
SELECT [Class] ,
[S_Adm_No],
[Alias],
DATENAME(M, [DateTime])as [Month],
DAY([DateTime]) as [DayValue]
FROM [View_SMS_Mark_Attendance]) as Composite PIVOT (MAX([Alias])
FOR [DayValue] IN ([1], [2], [3], [4],[5], [6], [7],
[8], [9],[10], [11], [12], [13],
[14],[15],[16], [17], [18], [19],
[20], [21], [22], [23], [24],[25],
[26], [27], [28], [29],[30],[31])
)
AS PivotTable
where [Month] = 'May' and Class ='Nur-A'
推荐答案
尝试加入表格with
Try to join a table with
SELECT pt.[Class], pt.[S_Adm_No], pt.[1], pt.[2], ..., pt.[31], at.[SumOfP], at.[SumOfA]
FROM (
SELECT [Class], [S_Adm_No], [Alias],DATENAME(M, [DateTime])as [Month], DAY([DateTime]) as [DayValue]
FROM [View_SMS_Mark_Attendance]) as Composite
WHERE [Month] = 'May' and Class ='Nur-A'
PIVOT (MAX([Alias]) FOR [DayValue] IN ([1], [2], [3], [4],[5], [6], [7],
[8], [9],[10], [11], [12], [13],
[14],[15],[16], [17], [18], [19],
[20], [21], [22], [23], [24],[25],
[26], [27], [28], [29],[30],[31])
) AS pt LEFT JOIN (
SELECT [Class], [S_Adm_No], SUM(CASE WHEN [Alias] = 'P' THEN 1 ELSE 0 END) AS SumOfP, SUM(CASE WHEN [Alias] = 'A' THEN 1 ELSE 0 END) AS SumOfA
FROM [View_SMS_Mark_Attendance]
GROUP BY [Class], [S_Adm_No]
) AS at ON pt.[Class] = at.[Class] AND pt.[S_Adm_No] = at.[S_Adm_No]
详情请见:< a href =https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins> SQL连接的可视化表示 [ ^ ]
这篇关于如何计算P&在sqlserver中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文