摘要:总结一下遇到的问题
SELECT dbo.t_b_Consumer.f_CardNO, dbo.t_d_ManualCardRecord.f_ReadDate AS ReadTime,
dbo.t_b_Consumer.f_ConsumerName, CONVERT(varchar(100), dbo.t_d_ManualCardRecord.f_ReadDate, 23) AS ReadDate,
dbo.t_d_ManualCardRecord.f_CardNO AS f_RecID
FROM dbo.t_d_ManualCardRecord INNER JOIN
dbo.t_b_Consumer ON dbo.t_d_ManualCardRecord.f_ConsumerID = dbo.t_b_Consumer.f_ConsumerID
SELECT TOP (100) PERCENT f_ConsumerName AS username, ReadDate, DATEDIFF(mi, MIN(ReadTime), MAX(ReadTime)) AS work_time,standard_time = '540',(DATEDIFF(mi, MIN(ReadTime), MAX(ReadTime))-540) AS diff_time,
MIN(ReadTime) AS PunchIn, MAX(ReadTime) AS PunchOut
FROM dbo.[oceania_all_attendance]
GROUP BY f_ConsumerName, ReadDate
ORDER BY ReadDate DESC
这里的TOP (100) PERCENT 去掉会报错,加上就可以
3、创建UNION 视图
CREATE VIEW oceania_all_attendance
AS
SELECT f_CardNO, f_ConsumerName, ReadTime, ReadDate, f_RecID
FROM [dbo].[RecordData]
UNION ALL
SELECT f_CardNO, f_ConsumerName, ReadTime, ReadDate, f_RecID
FROM [dbo].[oceania_am_attendance]
UNION ALL
SELECT f_CardNO, f_ConsumerName, ReadTime, ReadDate, f_RecID
FROM [dbo].[oceania_book_attendance]
UNION ALL
SELECT f_CardNO, f_ConsumerName, ReadTime, ReadDate, f_RecID
FROM [dbo].[oceania_pm_attendance]