比如圖中資料，8:01分的，我只想取到第一條資料，即 2019-11-04 08:01:54 ，8:02我只要取到第6行資料，請大神幫忙看看，這個語法該怎么寫？
謝謝！
/****** Object: Table [dbo].[tbm_tmp_importdata] Script Date: 2020/11/9 16:41:00 ******/
CREATE TABLE [dbo].[tbm_tmp_importdata](
[addressid] [char](20) NULL,
[calendartime] [char](19) NULL,
[datastatus] [smallint] NULL,
[datatype] [smallint] NOT NULL,
[dr] [smallint] NULL,
[exceptionflag] [smallint] NULL,
[pk_corp] [char](4) NOT NULL,
[pk_importdata] [varchar](20) NOT NULL,
[pk_machine] [char](20) NULL,
[pk_psndoc] [char](20) NOT NULL,
[timecardid] [varchar](100) NULL,
[ts] [char](19) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[tbm_tmp_importdata] ([addressid], [calendartime], [datastatus], [datatype], [dr], [exceptionflag], [pk_corp], [pk_importdata], [pk_machine], [pk_psndoc], [timecardid], [ts]) VALUES (NULL, N'2019-11-04 08:01:54', 2, 0, 0, 0, N'1001', N'10012019110002927064', NULL, N'1001V1100000001A4MQV', N'102142 ', N'2019-11-20 10:37:11')
INSERT [dbo].[tbm_tmp_importdata] ([addressid], [calendartime], [datastatus], [datatype], [dr], [exceptionflag], [pk_corp], [pk_importdata], [pk_machine], [pk_psndoc], [timecardid], [ts]) VALUES (NULL, N'2019-11-04 08:01:55', 2, 0, 0, 0, N'1001', N'10012019110002927065', NULL, N'1001V1100000001A4MQV', N'102142 ', N'2019-11-20 10:37:11')
INSERT [dbo].[tbm_tmp_importdata] ([addressid], [calendartime], [datastatus], [datatype], [dr], [exceptionflag], [pk_corp], [pk_importdata], [pk_machine], [pk_psndoc], [timecardid], [ts]) VALUES (NULL, N'2019-11-04 08:01:56', 2, 0, 0, 0, N'1001', N'10012019110002927066', NULL, N'1001V1100000001A4MQV', N'102142 ', N'2019-11-20 10:37:11')
INSERT [dbo].[tbm_tmp_importdata] ([addressid], [calendartime], [datastatus], [datatype], [dr], [exceptionflag], [pk_corp], [pk_importdata], [pk_machine], [pk_psndoc], [timecardid], [ts]) VALUES (NULL, N'2019-11-04 08:01:57', 2, 0, 0, 0, N'1001', N'10012019110002927067', NULL, N'1001V1100000001A4MQV', N'102142 ', N'2019-11-20 10:37:11')
INSERT [dbo].[tbm_tmp_importdata] ([addressid], [calendartime], [datastatus], [datatype], [dr], [exceptionflag], [pk_corp], [pk_importdata], [pk_machine], [pk_psndoc], [timecardid], [ts]) VALUES (NULL, N'2019-11-04 08:01:58', 2, 0, 0, 0, N'1001', N'10012019110002927068', NULL, N'1001V1100000001A4MQV', N'102142 ', N'2019-11-20 10:37:11')
INSERT [dbo].[tbm_tmp_importdata] ([addressid], [calendartime], [datastatus], [datatype], [dr], [exceptionflag], [pk_corp], [pk_importdata], [pk_machine], [pk_psndoc], [timecardid], [ts]) VALUES (NULL, N'2019-11-04 08:02:04', 2, 0, 0, 0, N'1001', N'10012019110002927071', NULL, N'1001V1100000001A4MQV', N'102142 ', N'2019-11-20 10:37:11')
INSERT [dbo].[tbm_tmp_importdata] ([addressid], [calendartime], [datastatus], [datatype], [dr], [exceptionflag], [pk_corp], [pk_importdata], [pk_machine], [pk_psndoc], [timecardid], [ts]) VALUES (NULL, N'2019-11-04 08:02:05', 2, 0, 0, 0, N'1001', N'10012019110002927072', NULL, N'1001V1100000001A4MQV', N'102142 ', N'2019-11-20 10:37:11')
INSERT [dbo].[tbm_tmp_importdata] ([addressid], [calendartime], [datastatus], [datatype], [dr], [exceptionflag], [pk_corp], [pk_importdata], [pk_machine], [pk_psndoc], [timecardid], [ts]) VALUES (NULL, N'2019-11-04 10:02:26', 2, 0, 0, 0, N'1001', N'10012019110002928743', NULL, N'1001V1100000001A4MQV', N'102142 ', N'2019-11-20 10:37:12')
INSERT [dbo].[tbm_tmp_importdata] ([addressid], [calendartime], [datastatus], [datatype], [dr], [exceptionflag], [pk_corp], [pk_importdata], [pk_machine], [pk_psndoc], [timecardid], [ts]) VALUES (NULL, N'2019-11-04 10:09:44', 2, 0, 0, 0, N'1001', N'10012019110002928884', NULL, N'1001V1100000001A4MQV', N'102142 ', N'2019-11-20 10:37:13')
INSERT [dbo].[tbm_tmp_importdata] ([addressid], [calendartime], [datastatus], [datatype], [dr], [exceptionflag], [pk_corp], [pk_importdata], [pk_machine], [pk_psndoc], [timecardid], [ts]) VALUES (NULL, N'2019-11-04 15:02:53', 2, 0, 0, 0, N'1001', N'10012019110002931998', NULL, N'1001V1100000001A4MQV', N'102142 ', N'2019-11-20 10:37:21')
INSERT [dbo].[tbm_tmp_importdata] ([addressid], [calendartime], [datastatus], [datatype], [dr], [exceptionflag], [pk_corp], [pk_importdata], [pk_machine], [pk_psndoc], [timecardid], [ts]) VALUES (NULL, N'2019-11-04 15:09:52', 2, 0, 0, 0, N'1001', N'10012019110002932141', NULL, N'1001V1100000001A4MQV', N'102142 ', N'2019-11-20 10:37:22')
INSERT [dbo].[tbm_tmp_importdata] ([addressid], [calendartime], [datastatus], [datatype], [dr], [exceptionflag], [pk_corp], [pk_importdata], [pk_machine], [pk_psndoc], [timecardid], [ts]) VALUES (NULL, N'2019-11-04 17:38:09', 2, 0, 0, 0, N'1001', N'10012019110002951751', NULL, N'1001V1100000001A4MQV', N'102142 ', N'2019-11-20 10:37:25')
INSERT [dbo].[tbm_tmp_importdata] ([addressid], [calendartime], [datastatus], [datatype], [dr], [exceptionflag], [pk_corp], [pk_importdata], [pk_machine], [pk_psndoc], [timecardid], [ts]) VALUES (NULL, N'2019-11-04 17:38:10', 2, 0, 0, 0, N'1001', N'10012019110002951752', NULL, N'1001V1100000001A4MQV', N'102142 ', N'2019-11-20 10:37:25')
uj5u.com熱心網友回復：
SELECT * FROM
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY LEFT(calendartime,16) ORDER BY calendartime) AS id
FROM tbm_tmp_importdata
) a WHERE id=1
uj5u.com熱心網友回復：
應該是where 陳述句里面，用convert函式取出時 分，這樣可以判斷了。uj5u.com熱心網友回復：
SELECT * FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY CONVERT(CHAR(16),[calendartime],120) ORDER BY [addressid]) AS rid,*
FROM [tbm_tmp_importdata]
) AS tt
WHERE tt.rid=1
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/208858.html
標籤：疑難問題