交叉表查询(出勤) [英] Cross table query (attendance)
问题描述
即时生成使用交叉表查询的学生每月出勤率的报告..我有一个表格,其中包含User_Id,日期,时间和状态字段
我试过的代码显示出勤率分别为同一个User_id的每个日期
User_Id 1 2 3 4
1121 P null null null
1121 null P null null
1121 null null P null
我该如何解决?
我尝试过:
选择[User_Id],[来自
的1],[2],[3],[4](从[first_db]中选择[User_Id],day([Date])作为日期,时间,状态。[dbo]。 [MarkA])ma
pivot
(
max [status]
for date in([1] ,[2],[3],[4])
)作为pvt
命令由
pvt.User_Id
SELECT [User_Id],
MAX([1])AS [1],
MAX([2])AS [2],
MAX ([3])AS [3],
MAX([4])AS [4]
FROM [first_db]。[dbo]。[MarkA]
PIVOT
(
MAX(状态)FOR日期([1],[2],[3],[4])
)as pvt
GROUP BY [User_Id] ORDER BY [User_Id]
i m generating a report of the monthly attendance of the students using the cross table query ..i have a table having fields as User_Id,Date,Time and status
the code which i tried shows attendance seperately for each date for the same User_id
User_Id 1 2 3 4
1121 P null null null
1121 null P null null
1121 null null P null
how do i solve it???
What I have tried:
select [User_Id],[1],[2],[3],[4] from
(select [User_Id], day([Date])as date,Time,Status from [first_db].[dbo].[MarkA]) ma
pivot
(
max[status]
for Date in ([1],[2],[3],[4])
)as pvt
order by
pvt.User_Id
SELECT [User_Id], MAX([1]) AS [1], MAX([2]) AS [2], MAX([3]) AS [3], MAX([4]) AS [4] FROM [first_db].[dbo].[MarkA] PIVOT ( MAX(Status) FOR Date in ([1],[2],[3],[4]) )as pvt GROUP BY [User_Id] ORDER BY [User_Id]
这篇关于交叉表查询(出勤)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!