在两个不同的表中使用FK查找最小日期时间 [英] Find minimum datetime while using FK in two different tables
问题描述
我有2张表:
课程
------
Id
名称
TEST
------
Id
CourseId(FK到`COURSE.ID`)
DATETIME
NUMBERS
使用<$ c假设 COURSE
具有不同的 DATETIME的8个数据的数据表$ c> ID
1,2(仅2列)和 TEST
/ code>和 CourseId
1(3列)和2(6列)。
I希望通过加入这两个表来找到最低 DATETIME,CourseID
和名称
。下面的查询是给出了一个2输出:
pre $ (SELECT min([DATETIME])作为DATETIME,[TEST] .CourseID,名称
FROM [dbo]。[TEST]
left JOIN [dbo]。[COURSE]
ON [dbo]。[TEST] .CourseID = [COURSE] .ID GROUP BY CourseID,名称)
我想要一个列输出,即单个输出列(最小日期时间以及名称和ID )..我怎样才能实现??
有了2个课程,你总是会得到2行,当这样加入。它会给你每个课程的最低日期值。你可以得到单行的第一种方法是在你的查询中使用 TOP 1
,它将给你提供最早的测试日期。另一种方法是使用 WHERE
子句来过滤一个课程。
请运行这个示例代码注释包括在注释中:
CREATE TABLE #course(id INT,name NVARCHAR(20 ));
CREATE TABLE #Test
(
id INT,
courseId INT,
testDate DATETIME - 不应该使用关键字作为列名
);
INSERT INTO #course
(id,name)
VALUES(1,'Maths'),
(2,'Science');
$ b $ - 注意我使用DATEADD(HOUR,-1,GETDATE())来获取一些随机的日期时间值
INSERT INTO #Test
(id,courseId,testDate)
VALUES(1,1,DATEADD(HOUR,-1,GETDATE())),
(2,1,DATEADD(HOUR,-2,GETDATE())),
3,1,DATEADD(HOUR,-3,GETDATE())),
(4,2,DATEADD(HOUR,-4,GETDATE())),
(5,2,DATEADD HOUR,-5,GETDATE())),
(6,2,DATEADD(HOUR,-6,GETDATE())),
(7,2,DATEADD(HOUR,-7,GETDATE ())),
(8,2,DATEADD(HOUR,-8,GETDATE()));
- 返回每个课程的最小日期 - 2行
SELECT MIN(t.testDate)AS TestDate,
t.courseId,
c.name
FROM #Test t
- 使用内部连接,因为可以看到没有左连接的原因
INNER JOIN #course c ON t.courseId = c.id
GROUP BY courseId,name;
- 以最短日期获得课程 - 1行
SELECT TOP 1
MIN(t.testDate)AS TestDate,
t.courseId,
c.name
FROM #Test t
- 使用内部连接,因为可以看到没有理由左连接
INNER JOIN #course c ON t.courseId = c.id
GROUP BY t.courseId,c.name
ORDER BY MIN(t.testDate); - 需要按
的顺序获得指定课程的最低日期 - 1行
SELECT MIN(t.testDate)AS TestDate,
t.courseId,
c.name
FROM #Test t
- 使用内部连接,因为可以看不到左连接的原因
INNER JOIN #course c ON t.courseId = c.id
WHERE t.courseId = 1 - 要求你指定一个课程id
GROUP BY courseId,name;
DROP TABLE #course;
DROP TABLE #Test;
I have 2 tables:
COURSE
------
Id
Name
TEST
------
Id
CourseId (FK to `COURSE.ID`)
DATETIME
NUMBERS
Suppose COURSE
table with ID
1,2 (only 2 columns) and TEST
table with 8 numbers of data having different DATETIME
and CourseId
of 1 (3 columns) and 2 (6 columns).
I want to find the minimum DATETIME,CourseID
and Name
by joining these 2 tables. The below query is giving a 2 output:
(SELECT min([DATETIME]) as DATETIME ,[TEST].CourseID,Name
FROM [dbo].[TEST]
left JOIN [dbo].[COURSE]
ON [dbo].[TEST].CourseID=[COURSE].ID GROUP BY CourseID,Name)
I want a single column output i.e. a single output column (minimum datetime along with Name and ID)..HOW can i achieve??
With 2 courses you are always going to get 2 rows when joining like this. It will give you the minimum date value for each course. The first way you can get a single row is to use TOP 1
in your query, which will simply give you the course with the earliest test date. The other way is to use a WHERE
clause to filter it by a single course.
Please run this sample code with some variations of what you can do, notes included in comments:
CREATE TABLE #course ( id INT, name NVARCHAR(20) );
CREATE TABLE #Test
(
id INT ,
courseId INT ,
testDate DATETIME -- you shouldn't use a keyword for a column name
);
INSERT INTO #course
( id, name )
VALUES ( 1, 'Maths' ),
( 2, 'Science' );
-- note I used DATEADD(HOUR, -1, GETDATE()) to simply get some random datetime values
INSERT INTO #Test
( id, courseId, testDate )
VALUES ( 1, 1, DATEADD(HOUR, -1, GETDATE()) ),
( 2, 1, DATEADD(HOUR, -2, GETDATE()) ),
( 3, 1, DATEADD(HOUR, -3, GETDATE()) ),
( 4, 2, DATEADD(HOUR, -4, GETDATE()) ),
( 5, 2, DATEADD(HOUR, -5, GETDATE()) ),
( 6, 2, DATEADD(HOUR, -6, GETDATE()) ),
( 7, 2, DATEADD(HOUR, -7, GETDATE()) ),
( 8, 2, DATEADD(HOUR, -8, GETDATE()) );
-- returns minumum date for each course - 2 rows
SELECT MIN(t.testDate) AS TestDate ,
t.courseId ,
c.name
FROM #Test t
-- used inner join as could see no reason for left join
INNER JOIN #course c ON t.courseId = c.id
GROUP BY courseId , name;
-- to get course with minimum date - 1 row
SELECT TOP 1
MIN(t.testDate) AS TestDate ,
t.courseId ,
c.name
FROM #Test t
-- used inner join as could see no reason for left join
INNER JOIN #course c ON t.courseId = c.id
GROUP BY t.courseId , c.name
ORDER BY MIN(t.testDate); -- requires order by
-- to get minimum date for a specified course - 1 row
SELECT MIN(t.testDate) AS TestDate ,
t.courseId ,
c.name
FROM #Test t
-- used inner join as could see no reason for left join
INNER JOIN #course c ON t.courseId = c.id
WHERE t.courseId = 1 -- requires you specify a course id
GROUP BY courseId , name;
DROP TABLE #course;
DROP TABLE #Test;
这篇关于在两个不同的表中使用FK查找最小日期时间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!