SQL Server创建视图

摘要:总结一下遇到的问题

1、将时间换成年月日

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

2、分组获取最值,同时排序

  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]
评论