考勤系统查询 [英] Attendance System Query

问题描述

您好我正在使用c＃和sql server进行考勤系统我第一次遇到下面提到的问题



i有这样的记录





Student_Name |出勤率|类



&NBSP&NBSP&NBSP琼斯&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP P&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 1个

&NBSP&NBSP&NBSP史密斯&NBSP&NBSP &NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP P&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 1个

&NBSP&NBSP Raven&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP A&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 1个

&NBSP&NBSP Jash&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP L&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP等级1个





现在我想这个检索此纪录





等级   |    NoOfStduent | NoOfPresents | NoOfAbsents | NoOfLeaves



级1&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 4&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 2&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP页 1&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 1个





是这个可能的用例？

Hello i am working on attendance system using c# and sql server for the first time i am facing a problem mentioned below

i have some records like this


Student_Name | Attendance | Class

      Jones                   P            Grade 1
      Smith                  P            Grade 1
     Raven                  A            Grade 1
     Jash                      L            Grade 1


now i want this retrieve this record


Class    |    NoOfStduent | NoOfPresents | NoOfAbsents | NoOfLeaves

Grade 1                4                 2                        1                       1


Is this possible using case ?

推荐答案

您可以使用从< table>中选择Class，Attendance，count（*）作为No按班级分组，出勤< / table>



然后将其用作枢轴上的内部查询，将数据转换为您需要的方式。

You can use select Class, Attendance, count(*) as No from <table> group by Class, Attendance</table>

Then use this as the inner query on a pivot to transform the data to how you need.

select Class, P+A+L as Students, P as Present, A as Absent, L as Leaves
from (select Class, Attendance, count(*) as No 
    from <table> 
    group by Class, Attendance
) src Pivot ( sum(No) for Attendance in ( [P], [A], [L] )) pvt
</table>

另一种方式......

another way...

SELECT  Class, SUM(Students) NoOfStduent ,SUM(Presents) NoOfPresents ,SUM(Absents) NoOfAbsents ,SUM(Leaves) NoOfLeaves
FROM
(
    SELECT
        Class,
        1 as Students,
        CASE WHEN Attendance = 'P' THEN 1 ELSE 0 END AS Presents,
        CASE WHEN Attendance = 'A' THEN 1 ELSE 0 END AS Absents,
        CASE WHEN Attendance = 'L' THEN 1 ELSE 0 END AS Leaves
    FROM    tableName
) as a
GROUP BY Class

快乐编码！

：）

Happy Coding!
:)

这篇关于考勤系统查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！