27,579
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('学生信息表') IS NOT NULL DROP TABLE 学生信息表
IF OBJECT_ID('学生考勤表') IS NOT NULL DROP TABLE 学生考勤表
GO
CREATE TABLE 学生信息表(
[sid] nvarchar(MAX)
,姓名 nvarchar(MAX)
,性别 nvarchar(MAX)
,班级 nvarchar(MAX)
)
SET NOCOUNT ON
INSERT INTO dbo.[学生信息表] VALUES(N'1001',N'小明',N'男',N'管信1501')
INSERT INTO dbo.[学生信息表] VALUES(N'1002',N'小红',N'女',N'管信1501')
INSERT INTO dbo.[学生信息表] VALUES(N'1003',N'小丽',N'女',N'管信1501')
INSERT INTO dbo.[学生信息表] VALUES(N'2001',N'小礼',N'男',N'环境1501')
--
CREATE TABLE 学生考勤表(
did NVARCHAR(MAX),
[sid] NVARCHAR(MAX),
[date] NVARCHAR(MAX),
起始时间 DATETIME,
结束时间 DATETIME,
状态 INT
)
GO
----- 以上为测试表及测试数据
--1. 插入
DECLARE @startTime DATETIME,@days INT
SET @startTime='2018-12-01' --起始日期
SET @days=5 --希望加入多少天的数据
INSERT INTO 学生考勤表
(
did,
[sid],
[date]
)
SELECT
REPLACE(CONVERT(CHAR(10),DATEADD(DAY,t.number,@startTime),120),'-','')+ RIGHT('0000'+ltrim(t.number),4) AS did
,s.[sid]
,CONVERT(CHAR(10),DATEADD(DAY,t.number,@startTime) ,120)
FROM 学生信息表 AS s
CROSS APPLY
(SELECT * FROM [master].dbo.spt_values AS sv WHERE sv.[type]='P' AND sv.number BETWEEN 0 AND @days-1) AS t
--2. 查看
SELECT * FROM 学生考勤表 ORDER BY did,[sid]
USE tempdb
GO
IF OBJECT_ID('学生信息表') IS NOT NULL DROP TABLE 学生信息表
IF OBJECT_ID('学生考勤表') IS NOT NULL DROP TABLE 学生考勤表
GO
CREATE TABLE 学生信息表(
[sid] nvarchar(MAX)
,姓名 nvarchar(MAX)
,性别 nvarchar(MAX)
,班级 nvarchar(MAX)
)
SET NOCOUNT ON
INSERT INTO dbo.[学生信息表] VALUES(N'1001',N'小明',N'男',N'管信1501')
INSERT INTO dbo.[学生信息表] VALUES(N'1002',N'小红',N'女',N'管信1501')
INSERT INTO dbo.[学生信息表] VALUES(N'1003',N'小丽',N'女',N'管信1501')
INSERT INTO dbo.[学生信息表] VALUES(N'2001',N'小礼',N'男',N'环境1501')
--
CREATE TABLE 学生考勤表(
did NVARCHAR(MAX),
[sid] NVARCHAR(MAX),
[date] NVARCHAR(MAX),
起始时间 DATETIME,
结束时间 DATETIME,
状态 INT
)
GO
----- 以上为测试表及测试数据
--1. 插入
DECLARE @startTime DATETIME,@days INT
SET @startTime='2018-12-01' --起始日期
SET @days=5 --希望加入多少天的数据
INSERT INTO 学生考勤表
(
did,
[sid],
[date]
)
SELECT
REPLACE(CONVERT(CHAR(10),DATEADD(DAY,t.number,'2018-12-01'),120),'-','')+ RIGHT('0000'+ltrim(t.number),4) AS did
,s.[sid]
,CONVERT(CHAR(10),DATEADD(DAY,t.number,'2018-12-01') ,120)
FROM 学生信息表 AS s
CROSS APPLY
(SELECT * FROM [master].dbo.spt_values AS sv WHERE sv.[type]='P' AND sv.number BETWEEN 0 AND @days-1) AS t
--2. 查看
SELECT * FROM 学生考勤表 ORDER BY did,[sid]
CREATE TABLE [dbo].[student] (
[sid] int NOT NULL ,
[name] varchar(20) COLLATE Chinese_PRC_CI_AS NULL ,
[sex] char(2) COLLATE Chinese_PRC_CI_AS NULL ,
[class] varchar(20) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK__student__DDDFDD36C68E385B] PRIMARY KEY ([sid])
)
ON [PRIMARY]
GO
CREATE TABLE [dbo].[sign] (
[did] bigint NOT NULL ,
[sid] int NULL ,
[date] date NULL ,
[start_time] datetime2(7) NULL ,
[end_time] datetime2(7) NULL ,
[status] tinyint NULL ,
CONSTRAINT [PK__sign__D877D216AF1A4C67] PRIMARY KEY ([did])
)
ON [PRIMARY]
GO
INSERT INTO [test].[dbo].[student] ([sid], [name], [sex], [class]) VALUES ('1001', '小明', '男', '管信1501');
INSERT INTO [test].[dbo].[student] ([sid], [name], [sex], [class]) VALUES ('1002', '小红', '女', '管信1501');
INSERT INTO [test].[dbo].[student] ([sid], [name], [sex], [class]) VALUES ('1003', '小丽', '女', '管信1501');
INSERT INTO [test].[dbo].[student] ([sid], [name], [sex], [class]) VALUES ('2001', '小礼', '男', '环境1501');
DECLARE @i INT = 0, @month CHAR(7) = '2015-02', @day DATE
SET @day = DATEADD(DAY, @i, @month + '-01')
WHILE CONVERT(CHAR(7), @day, 120) = @month
BEGIN
SET @day = DATEADD(DAY, @i, @month + '-01')
INSERT INTO sign (did, sid, date)
SELECT CONVERT(CHAR(8), @day, 112) + RIGHT('000' + CAST(ROW_NUMBER () OVER (ORDER BY sid) AS VARCHAR(3)), 3), sid, @day FROM student
SET @i = @i + 1
END
USE tempdb
GO
IF OBJECT_ID('学生信息表') IS NOT NULL DROP TABLE 学生信息表
IF OBJECT_ID('学生考勤表') IS NOT NULL DROP TABLE 学生考勤表
GO
CREATE TABLE 学生信息表(
[sid] nvarchar(MAX)
,姓名 nvarchar(MAX)
,性别 nvarchar(MAX)
,班级 nvarchar(MAX)
)
SET NOCOUNT ON
INSERT INTO dbo.[学生信息表] VALUES(N'1001',N'小明',N'男',N'管信1501')
INSERT INTO dbo.[学生信息表] VALUES(N'1002',N'小红',N'女',N'管信1501')
INSERT INTO dbo.[学生信息表] VALUES(N'1003',N'小丽',N'女',N'管信1501')
INSERT INTO dbo.[学生信息表] VALUES(N'2001',N'小礼',N'男',N'环境1501')
--
CREATE TABLE 学生考勤表(
did NVARCHAR(MAX),
[sid] NVARCHAR(MAX),
[date] NVARCHAR(MAX),
起始时间 DATETIME,
结束时间 DATETIME,
状态 INT
)
GO
----- 以上为测试表及测试数据
--1. 插入
INSERT INTO 学生考勤表
(
did,
[sid],
[date]
)
SELECT
REPLACE(CONVERT(CHAR(10),DATEADD(DAY,t.number,'2018-12-01'),120),'-','')+ RIGHT('0000'+ltrim(t.number),4) AS did
,s.[sid]
,CONVERT(CHAR(10),DATEADD(DAY,t.number,'2018-12-01') ,120)
FROM 学生信息表 AS s
CROSS APPLY
(SELECT * FROM [master].dbo.spt_values AS sv WHERE sv.[type]='P' AND sv.number BETWEEN 0 AND 2) AS t
--2. 查看
SELECT * FROM 学生考勤表 ORDER BY did,[sid]