sqlserver考勤表的显示

Question

我要做一个考勤记录表,显示的样式如下姓名 | 1 | 2      | 3 | 4  | 5    | 6 | 7 | 8 | 9 | 10 |11|12|13|14|15|16|17|18|19|20|张三 |到 |迟到0.5h|到 |放假|早退1h|我数据库中记录的字段如下姓名 | 到 | 迟到 | 早退 | 旷工 | 放假
张三 |0   | 0.5  |  0   |   0  |  0  
李四 |  1 |   0  |  0   |   0  |  0  
王五 |  1 |   0  |  0   |   0  |  0  到 字段是bit类型
迟到 早退 旷工 放假是 decimal(18,2)类型的这是创建数据库的SQL
if exists (select * from sysobjects where id = OBJECT_ID('[ManualSign]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
DROP TABLE [ManualSign]CREATE TABLE [ManualSign] (
[ID] [int]  IDENTITY (1, 1)  NOT NULL,
[UserId] [varchar]  (50) NOT NULL,
[Dao] [bit]  NULL DEFAULT (0),
[ChiDao] [decimal]  (18,2) NULL,
[ZaoTui] [decimal]  (18,2) NULL,
[KuangGong] [decimal]  (18,2) NULL,
[QingJia] [decimal]  (18,2) NULL DEFAULT (0),
[FangJia] [decimal]  (18,2) NULL DEFAULT (0),
[LrUserId] [varchar]  (50) NOT NULL,
[LrDate] [smalldatetime]  NOT NULL DEFAULT (getdate()),
[AddDate] [smalldatetime]  NOT NULL DEFAULT (getdate()),
[Res] [varchar]  (200) NULL)ALTER TABLE [ManualSign] WITH NOCHECK ADD  CONSTRAINT [PK_ManualSign] PRIMARY KEY  NONCLUSTERED ( [ID] )
SET IDENTITY_INSERT [ManualSign] ONINSERT [ManualSign] ([ID],[UserId],[Dao],[ChiDao],[ZaoTui],[KuangGong],[QingJia],[FangJia],[LrUserId],[LrDate],[AddDate]) VALUES ( 3,N'admin',1,0.00,0.00,0.00,0.00,0.00,N'admin',N'2011-12-21 0:00:00',N'2011-12-21 14:55:00')
INSERT [ManualSign] ([ID],[UserId],[Dao],[ChiDao],[ZaoTui],[KuangGong],[QingJia],[FangJia],[LrUserId],[LrDate],[AddDate]) VALUES ( 4,N'admin',1,0.00,0.00,0.00,0.00,0.00,N'admin',N'2011-12-23 0:00:00',N'2011-12-23 0:36:00')SET IDENTITY_INSERT [ManualSign] OFF

Answer 1

表的字段设计有冗余，迟到、早退、请假等为什么不单独用一个字段表示，如下所示：
CREATE TABLE [ManualSign] (
[ID] [int]  IDENTITY (1, 1)  NOT NULL,
[UserId] [varchar]  (50) NOT NULL,
[Type] [varchar] (10) NOT NULL,
[Attendance] [varchar] (20) NOT NULL,
[LrUserId] [varchar]  (50) NOT NULL,
[LrDate] [smalldatetime]  NOT NULL DEFAULT (getdate()),
[AddDate] [smalldatetime]  NOT NULL DEFAULT (getdate()),
[Res] [varchar]  (200) NULL)INSERT [ManualSign] ([ID],[UserId],[Type],[Attendance],[LrUserId],[LrDate],[AddDate]) VALUES ( 3,N'admin','ChiDao',0.5,N'admin',N'2011-12-21 0:00:00',N'2011-12-21 14:55:00')
INSERT [ManualSign] ([ID],[UserId],[Type],[Attendance],[LrUserId],[LrDate],[AddDate]) VALUES ( 3,N'admin','KuangGong',2.0,N'admin',N'2011-12-21 0:00:00',N'2011-12-21 14:55:00')

Answer 2

这个我倒欠考虑了，如果是这样的话，那你原来的Table设计应该是没问题的吧

Answer 3

这个我倒欠考虑了，如果是这样的话，那你原来的Table设计应该是没问题的吧