我的代码查询SQL servei中存在问题 [英] My code query where there is a problem in 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匹配时才会起作用。



根据您在问题中提供的数据，您将得到一个结果最大日期2016-03-09恰好是护理555最大里程为12000的日期。但是如果你添加一行数据

 将 插入 Tashhem 值（ 555 ， 4001 ， 501 ，'  2016-03-08'， 24000 ，< span class =code-digit> 9000 ， 6 ）

然后什么都不会返回（因为最大里程没有在最大日期完成）。



您声明

引用：

我希望每个car_id

选择最大日期和最大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   |	  2	

i 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 s1
inner 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 = 555

What I have tried:

what's wrong in this query and thanks

解决方案

As per my comment above, your query will only work when there is a row that matches BOTH the maximum date and the maximum Current_Dist.

With the data you present in the question then you will get a result for max date 2016-03-09 which happens to be the date on which care 555 did the maximum mileage of 12000. But if you add a single line of data

insert into Tashhem values (555, 4001, 501, '2016-03-08', 24000,9000,6)

Then nothing will be returned (because the maximum mileage was not done on the maximum date).

You stated

Quote:

i want select max date and max current_dist each car_id

. If that is indeed all you want then the query you used in the sub-query is sufficient

select Car_id, MAX(Change_date) as 'Max date', MAX(Current_dist) as 'Max dist'  from Tashhem group by Car_id

If 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_dist
from 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

resulting in

Car_id  Max_Date       dist_on_max_date  Max_dist  date_of_max_dist
555	2016-03-09	12000	         24000	   2016-03-08

这篇关于我的代码查询SQL servei中存在问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！