如何根据日期以及使用sql从两个表中获取时间的前5条记录？ [英] How to take first 5 records according to date as well as time from two tables using sql?

问题描述

第一张表：考试表

ex1 2014-08-07 13：21：48.000

ex2 2014-08-07 14：21：48.000

ex3 2014-08-06 13：21：48.000

ex4 2014-08-05 13：21：48.000



第二张桌子：演讲桌



l1 2014-08-07 11：21：48.000

l2 2014-08-07 12:21： 48.000

l3 2014-08-06 11：21：48.000

l4 2014-08-05 12：21：48.000

我想要通过同时计算两者的时间和日期，从两个表中检索前5行。该记录对应于一个学生。我想找出最近的5个考试或学生参加的讲座。预期结果如下：



ex2 2014-08-07 14：21：48.000

ex1 2014-08-07 13:21 ：48.000

l2 2014-08-07 12：21：48.000

l1 2014-08-07 11：21：48.000

ex3 2014 -08-06 13：21：48.000



显示结果，时间和日期两者都应该正确比较。谁能告诉我sql查询呢？

First Table : Exam Table
ex1 2014-08-07 13:21:48.000
ex2 2014-08-07 14:21:48.000
ex3 2014-08-06 13:21:48.000
ex4 2014-08-05 13:21:48.000

Second Table :Lecture Table

l1 2014-08-07 11:21:48.000
l2 2014-08-07 12:21:48.000
l3 2014-08-06 11:21:48.000
l4 2014-08-05 12:21:48.000
I want to retrieve top 5 rows from both the table by calculating time and date of both simultaneously.This record corresponds to one student. I want to find out recent 5 exams or lectures attended by student. Expected Result is as follows :

ex2 2014-08-07 14:21:48.000
ex1 2014-08-07 13:21:48.000
l2 2014-08-07 12:21:48.000
l1 2014-08-07 11:21:48.000
ex3 2014-08-06 13:21:48.000

While displaying result, time and date both should be compared properly. Can anyone tell me sql query for this?

推荐答案

;WITH MyCte(type, date) AS
(
SELECT LectureID, StartDateTime FROM lectureTable
UNION ALL
SELECT Exam_ID, DateOfExam FROM examTable
)

SELECT TOP 5 * FROM MyCte ORDER BY 2 DESC

;WITH MyCte(EType,EventDate) AS
(
SELECT 'Exam',E.DateOfExam FROM tbl_ExamCreation E
UNION ALL
SELECT 'Lecture',LS.StartDateTime FROM tbl_LectureScheduling LS
)
 
SELECT EType,EventDate FROM MyCte ORDER BY 2 DESC 

这是完美的解决方案。当我们选择两列时，我们必须在MyCTe之后提到两个列名。以及在最后一个SELECT语句之后。

This is Perfect Soluction. As we are selecting two columns, we have to mention two column name after MyCTe. as well as after last SELECT statement.

这是一种在不使用公用表表达式（CTE）的情况下获得所需结果的方法。

Here is a way of getting the desired results without using a common table expression (CTE).

--Data setup
--create tables
declare @Exam as table (id varchar(5), theDateTime datetime);
declare @Lecture as table (id varchar(5), theDateTime datetime);

--populate tables
insert into @Exam values ('ex1','2014-08-07 13:21:48.000');
insert into @Exam values ('ex2','2014-08-07 14:21:48.000');
insert into @Exam values ('ex3','2014-08-06 13:21:48.000');
insert into @Exam values ('ex4','2014-08-05 13:21:48.000');

insert into @Lecture values ('l1','2014-08-07 11:21:48.000');
insert into @Lecture values ('l2','2014-08-07 12:21:48.000');
insert into @Lecture values ('l3','2014-08-06 11:21:48.000');
insert into @Lecture values ('l4','2014-08-05 12:21:48.000')

--query, not using CTE
select top 5
    id,
    theDateTime
from (
    select id, theDateTime from @Exam
    union all
    select id, theDateTime from @Lecture
) ExamAndLecture
order by theDateTime desc;

我不确定哪个版本运行得更快，但我假设执行速度没有太大差异。

我已经包含了下面的cte版本，所以你可以测试和比较查询结构中的差异或相似性。

I am not sure which version will run faster, but I assume there will not be much difference in the speed of execution.
I have included the cte version below so you may test and compare the difference or similarity in the query structure.

--query, using CTE
with ExamAndLecture(id, theDateTime) as (
    select id, theDateTime from @Exam
    union all
    select id, theDateTime from @Lecture
)
select top 5
    id,
    theDateTime
from ExamAndLecture
order by theDateTime desc;

这篇关于如何根据日期以及使用sql从两个表中获取时间的前5条记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！