SqlServer 测试删除与日期计算

快来打我* 2024-03-27 10:46 15阅读 0赞

### 删除语句 ###

-- 创建t1表，并插入3条数据
    CREATE TABLE [dbo].[t1] ([id] [INT] NOT NULL, [name] [NCHAR](10) NULL) ON [PRIMARY];
    GO
    INSERT INTO dbo.t1(id, name)
    VALUES(1, -- id - int
    N'lisi' -- name - nchar(10)
        );
    
    INSERT INTO dbo.t1(id, name)
    VALUES(2, -- id - int
    N'zhangsan' -- name - nchar(10)
        );
    
    
    INSERT INTO dbo.t1(id, name)
    VALUES(3, -- id - int
    N'aben' -- name - nchar(10)
        );
    
    -- 创建t2表，并插入2条数据
    CREATE TABLE [dbo].[t2] ([id] [INT] NOT NULL, [name] [NCHAR](10) NULL) ON [PRIMARY];
    GO
    INSERT INTO dbo.t2(id, name)
    VALUES(1, -- id - int
    N'lisi' -- name - nchar(10)
        );
    
    INSERT INTO dbo.t2(id, name)
    VALUES(2, -- id - int
    N'zhangsan' -- name - nchar(10)
        );
    
    
    
    SELECT * FROM dbo.t1;
    
    SELECT * FROM dbo.t2;
    
    SELECT b.* FROM t1 AS a INNER JOIN t2 AS b ON a.id=b.id;
    
    
    -- 删除内关联t1表后的2条记录
    DELETE t1 FROM t1 AS a INNER JOIN t2 AS b ON a.id=b.id;
    
    -- 删除内关联t2表后的2条记录
    DELETE t2 FROM t1 AS a INNER JOIN t2 AS b ON a.id=b.id;
    
    
    
    SELECT b.* FROM t1 AS a LEFT JOIN t2 AS b ON a.id=b.id;
    
    -- 删除左外关联后的t1表3条记录
    DELETE t1 FROM t1 AS a LEFT JOIN t2 AS b ON a.id=b.id;
    
    -- 删除左外关联后的t2表2条记录
    DELETE t2 FROM t1 AS a LEFT JOIN t2 AS b ON a.id=b.id;
    
    
    -- 关联删除t1
    delete from t1  from t2  inner join t1  on t1.id=t2.id ;

### 日期计算 ###

三天 select \* from T\_news where datediff(day,addtime,getdate())<= 2 and datediff(day,addtime,getdate())>= 0  
一周 select \* from T\_news WHERE (DATEPART(wk, addtime) = DATEPART(wk, GETDATE())) AND (DATEPART(yy, addtime) = DATEPART(yy, GETDATE()))  
注意：此时不能用 datediff 差值为7,因为，datediff只表示间隔数  
一月 select \* from T\_news WHERE (DATEPART(yy, addtime) = DATEPART(yy, GETDATE())) AND (DATEPART(mm, addtime) = DATEPART(mm, GETDATE()))  
一季度 select \* from T\_news where DATEPART(qq, addtime) = DATEPART(qq, GETDATE()) and DATEPART(yy, addtime) = DATEPART(yy, GETDATE())  
本周：select \* from table where datediff(week,C\_CALLTIME,getdate())=0 --C\_CALLTIME 为日期字段  
本月：select \* from table where datediff(Month,C\_CALLTIME,getdate())=0 --C\_CALLTIME 为日期字段  
本季：select \* from table where datediff(qq,C\_CALLTIME,getdate())=0  
前半年1-6，后半年7-12：select \* from table where datepart(mm,C\_CALLTIME)/7 = datepart(mm,getdate())/7

--sql得到当前系统时间得 日期部分
    
    CONVERT(varchar(10),getDate(),120)
    
    --昨天
    
    select convert(varchar(10),getdate() - 1,120)
    
    --明天
    
    select convert(varchar(10),getdate() + 1,120)
    
    --最近七天
    
    select * from tb where 时间字段 >= convert(varchar(10),getdate() - 7,120)
    
    --随后七天
    
    select * from tb where 时间字段 <= convert(varchar(10),getdate() + 7,120) and 时间字段 >= --时间字段
    
    --上月
    
    select * from tb where month(时间字段) = month(getdate()) - 1
    
    --本月
    
    select * from tb where month(时间字段) = month(getdate())
    
    --下月
    
    select * from tb where month(时间字段) = month(getdate()) + 1
    
    --昨天
    
    Select * From TableName Where DateDiff(dd, DateTimCol, GetDate()) = 1
    
    --明天
    
    Select * From TableName Where DateDiff(dd, GetDate(), DateTimCol) = 1
    
    --最近七天
    
    Select * From TableName Where DateDiff(dd, DateTimCol, GetDate()) <= 7
    
    --随后七天
    
    Select * From TableName Where DateDiff(dd, GetDate(), DateTimCol) <= 7
    
    --上周
    
    Select * From TableName Where DateDiff(wk, DateTimCol, GetDate()) = 1
    
    --本周
    
    Select * From TableName Where DateDiff(wk, DateTimCol, GetDate()) = 0
    
    --下周
    
    Select * From TableName Where DateDiff(wk, GetDate(), DateTimCol ) = 1
    
    --上月
    
    Select * From TableName Where DateDiff(mm, DateTimCol, GetDate()) = 1
    
    --本月
    
    Select * From TableName Where DateDiff(mm, DateTimCol, GetDate()) = 0
    
    --下月
    
    Select * From TableName Where DateDiff(mm, GetDate(), DateTimCol ) = 1
    
    --------------------------------------------------------
    
    --本周
    
    select * from tb where datediff(week , 时间字段 ,getdate()) = 0
    
    --上周
    
    select * from tb where datediff(week , 时间字段 ,getdate()) = 1
    
    --下周
    
    select * from tb where datediff(week , 时间字段 ,getdate()) = -1