本文介绍了我的代码查询SQL servei中存在问题的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 我创建表: - Car_id | Job_Card |价值| Change_date | Current_dist | Prev_dist | driver_id 555 | 4000 | 500 | 2016年3月9日| 12000 | 9000 | 4 555 | 3215 | 80 | 2016年3月7日| 9000 | 8500 | 1 555 | 2 | 230 | 2016年3月6日| 8500 | 8000 | 2 555 | 875502 | 1400 | 2016年2月5日| 6000 | 4800 | 2 555 | 8978 | 1500 | 2016年2月5日| 8000 | 6000 | 2 i想要显示max(current_dist)和Change_date跟进移动护理 i写这个查询吧工作但插入许多行或值后它不工作.. SELECT s1。[Car_id],s1。[Change_date],s1。[Current_dist],s1。[Driver_ID] FROM Tashhem s1 内部 加入(选择 Car_id,MAX( Change_date) as ' 最大日期', MAX(Current_dist) as ' 当前dist' 来自 Tashhem group by Car_id) s2 on s1.Car_id = s2。 Car_id 和 s1.Change_date = s2。[Max date ] 和 s1.Current_dist = s2。[当前 dist] 其中 s1。 Car_id = 555 我的尝试: 这个查询有什么问题,谢谢解决方案 选择最大日期和最大current_dist。如果确实如此,那么您在子查询中使用的查询就足够了 选择 Car_id,MAX(Change_date) as ' 最大日期',MAX(Current_dist) as ' Max dist' 来自 Tashhem group by Car_id 如果您想了解有关每个旅程的更多信息,您可以再次加入原始表 - 我在这里使用CTE(公用表表达式)而不是子查询,但是两者的原理相同 ; q as ( 选择 Car_id,MAX(Change_date) as ' 最大日期',MAX(Current_dist) as ' Max dist' 来自 Tashhem group by Car_id ) SELECT q.Car_id,q。[Max date ],s1.Current_dist as dist_on_max_date,q。[Max dist],s2.Change_date as date_of_max_dist 来自 q INNER JOIN Tashhem s1 on q.Car_id = s1.Car_id AND q。[Max date ] = s1。 Change_date INNER JOIN Tashhem s2 on q.Car_id = s2.Car_id AND q。[Max dist] = s2。 Current_dist 导致 Car_id Max_Date dist_on_max_date Max_dist date_of_max_dist 555 2016-03-09 12000 24000 2016-03-08 i create table :- Car_id | Job_Card | Value | Change_date | Current_dist | Prev_dist | driver_id 555 | 4000 | 500 | 2016-03-09| 12000 | 9000 | 4 555 | 3215 | 80 | 2016-03-07| 9000 | 8500 | 1 555 | 2 | 230 | 2016-03-06| 8500 | 8000 | 2 555 | 875502 | 1400 | 2016-02-05| 6000 | 4800 | 2 555 | 8978 | 1500 | 2016-02-05| 8000 | 6000 | 2i want display max(current_dist) and Change_date to follow up move the care i write this query its working but after insert many row or values its not working ..SELECT s1.[Car_id],s1.[Change_date],s1.[Current_dist],s1.[Driver_ID] FROM Tashhem s1inner join (select Car_id, MAX(Change_date) as 'Max date' , MAX(Current_dist) as 'Current dist' from Tashhem group by Car_id)s2 on s1.Car_id = s2.Car_id and s1.Change_date = s2.[Max date] and s1.Current_dist = s2.[Current dist]where s1.Car_id = 555What I have tried:what's wrong in this query and thanks 解决方案 . If that is indeed all you want then the query you used in the sub-query is sufficientselect Car_id, MAX(Change_date) as 'Max date', MAX(Current_dist) as 'Max dist' from Tashhem group by Car_idIf you want to know more information about each of those journeys you can just join back to the original table again - I'm using a CTE (Common Table Expression) here instead of a sub-query, but the principal is the same for both;with q as(select Car_id, MAX(Change_date) as 'Max date', MAX(Current_dist) as 'Max dist' from Tashhem group by Car_id)SELECT q.Car_id, q.[Max date], s1.Current_dist as dist_on_max_date, q.[Max dist], s2.Change_date as date_of_max_distfrom qINNER JOIN Tashhem s1 on q.Car_id=s1.Car_id AND q.[Max date]=s1.Change_dateINNER JOIN Tashhem s2 on q.Car_id=s2.Car_id AND q.[Max dist]=s2.Current_distresulting in Car_id Max_Date dist_on_max_date Max_dist date_of_max_dist5552016-03-0912000 24000 2016-03-08 这篇关于我的代码查询SQL servei中存在问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 10-29 08:39