将数据作为最新日期时间进行检索 [英] To retrive the data as latest datetime

问题描述

我需要获取状态为1的最新日期时间数据。



Teststatus | ExamDate |主题

Johan | 0 | 2019-01-01 25:46 |数学

Johan | 1 | 2019-01-01 25:48 |数学

Johan | 0 | 2019-01-01 25:47 |数学

Ram | 0 | 2019-01-01 25:13 |数学

Ram | 0 | 2019-01-01 25:45 |数学

Ram | 1 | 2019-01-01 25:42 |数学



我尝试过：



选择Et.Name， Et.Status，Max（Et.ExamDate）

来自Exam Et加入主题S加上Et.Code = S.code

group by Et，Name，Et.Status < br $>




名称| Teststatus | ExamDate

Johan | 1 | 2019-01-01 25:48

Ram | 0 | 2019-01-01 25:45

Ram | 1 | 2019-01-01 25:42

Johan | 0 | 2019-01-01 25:47



但是我需要获得最新的约会时间以及谁通过了考试



名称| Teststatus | ExamDate

Johan | 1 | 2019-01-01 25:48

I need to fetch the data whose latest datetime who status 1.

Name | Teststatus | ExamDate | Subject
Johan | 0 | 2019-01-01 25:46 | Maths
Johan | 1 | 2019-01-01 25:48 | Maths
Johan | 0 | 2019-01-01 25:47 | Maths
Ram | 0 | 2019-01-01 25:13 | Maths
Ram | 0 | 2019-01-01 25:45 | Maths
Ram | 1 | 2019-01-01 25:42 | Maths

What I have tried:

select Et.Name, Et.Status, Max(Et.ExamDate)
from Exam Et join Subject S on Et.Code =S.code
group by Et,Name,Et.Status


Name | Teststatus | ExamDate
Johan | 1 | 2019-01-01 25:48
Ram | 0 | 2019-01-01 25:45
Ram | 1 | 2019-01-01 25:42
Johan | 0 | 2019-01-01 25:47

But i need to get latest datetime and who has passed in exam

Name | Teststatus | ExamDate
Johan | 1 | 2019-01-01 25:48

推荐答案

GROUP BY 不能做你想做的事 - 特别是没有像那样。看看这里： SQL GROUP By并且列'名称'在选择列表中无效，因为......错误 [ ^ ]，它解释了它的作用！



您需要考虑您的数据以及您想要检索的内容：

GROUP BY doesn't do what you want - particularly not like that. Have a look here: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^] which explains what it does do!

You need to think about your data and what you want to retrieve:

"Passed test"

和

"Latest date"

这样做：

So do that:

SELECT TOP 1 Name, TestStatus, ExamDate FROM Exam
WHERE TestStatus = 1
ORDER BY ExamDate DESC

TOP 1 说只返回第一个值， WHERE 将其限制为通过测试， ORDER BY 控制返回行的顺序。

TOP 1 says "return only the first value", the WHERE restricts it to passed tests, and the ORDER BY controls the order in which rows are returned.

with co
(
Select code, max(examdate) as examdate from exam group by code
)
select et.name, et.status, c.examdate
join co c on et.code = c.code

如果你没有从中选择，你为什么加入主题？



你也可以加入到另一张桌子，如果他们通过了拉，但你没有告诉我如何

Why did you join on subject if you don't select from it?

you can also join to another table to pull if they passed, but you didn't show me how

这篇关于将数据作为最新日期时间进行检索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！