34,594
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE [dbo].[tb](
[id] [int] IDENTITY(1,1) NOT NULL,
[title] [nvarchar](50) NULL,
[content] [nvarchar](50) NULL,
[createTime] [datetime] NULL,
CONSTRAINT [PK_tb] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tb_log](
[id] [int] IDENTITY(1,1) NOT NULL,
[tb_id] [int] NULL,
[updateTime] [datetime] NULL,
CONSTRAINT [PK_tb_log] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[tb] ON
GO
INSERT [dbo].[tb] ([id], [title], [content], [createTime]) VALUES (1, N'测试1', N'测试1', CAST(N'2017-06-01 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tb] ([id], [title], [content], [createTime]) VALUES (2, N'测试2', N'测试2', CAST(N'2017-06-04 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tb] ([id], [title], [content], [createTime]) VALUES (3, N'测试3', N'测试3', CAST(N'2017-07-20 00:00:00.000' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[tb] OFF
GO
SET IDENTITY_INSERT [dbo].[tb_log] ON
GO
INSERT [dbo].[tb_log] ([id], [tb_id], [updateTime]) VALUES (1, 1, CAST(N'2017-07-31 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tb_log] ([id], [tb_id], [updateTime]) VALUES (2, 1, CAST(N'2017-08-01 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[tb_log] ([id], [tb_id], [updateTime]) VALUES (3, 3, CAST(N'2017-08-01 00:00:00.000' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[tb_log] OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'新增时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb', @level2type=N'COLUMN',@level2name=N'createTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'跟进时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_log', @level2type=N'COLUMN',@level2name=N'updateTime'
GO
SELECT *
FROM [tb] AS a
WHERE NOT EXISTS ( SELECT 1
FROM [tb_log]
WHERE [tb_id] = a.[ID]
AND [createTime] >= CONVERT(VARCHAR(10), GETDATE()
- 30, 120) )
AND A.[createTime] < CONVERT(VARCHAR(10), GETDATE() - 30, 120);
SELECT tb.*
FROM tb
LEFT JOIN ( SELECT tb_id ,
MAX(updateTime) AS updateTime
FROM dbo.tb_log
GROUP BY tb_id
) t ON t.tb_id = tb.id
WHERE DATEDIFF(DAY, createTime, ISNULL(updateTime, GETDATE())) >= 30
虽然我还是不知道规则……看大版那个代码的这里感觉应该上边那个意思
[createTime] >= CONVERT(VARCHAR(10), GETDATE()
- 30, 120) )
SELECT *
FROM tb
WHERE DATEDIFF(DAY, createTime, GETDATE()) > 30
SELECT *
FROM [tb] AS a
WHERE NOT EXISTS ( SELECT 1
FROM [tb_log]
WHERE [tb_id] = a.[ID]
AND [createTime] >= CONVERT(VARCHAR(10), GETDATE()
- 30, 120) );