我如何在Sql Server中使用最大日期计算值 [英] How I Count Values With Max Date In Sql Server

问题描述

我有这样一张桌子： -

Car_id，Car_Mode ........ Car_id是PK



<表2： - Parts_id是PK

i have 1 table like this :-
Car_id,Car_Mode ........ Car_id is PK


table 2:- Parts_id is PK

Parts_id   Part_Type
--------   ---------
   1         Part1
   2         Part2
   3         Part3

表3如下： - Car_id是FK，Parts_id是FK

table 3 like this:- Car_id is FK , Parts_id is FK

Car_id   Job_Number   Item_No    Price_Part    Change_date   Parts_id
-------  ----------  ---------   -----------    -----------  ----------
  123       1234         1          500          22/9/2012       2
  123       1234         2          600          22/9/2012       3
  123       2222         1          700          1/10/2015       3
  123       2222         2          300          1/10/2015       1
  123       2222         3          400          1/10/2015       2

i想要disp将此表格设置为这样的用户，使用Max date和count items_No： -

i want display this table to user like this with Max date and count items_No :-

Car_id   Job_Number   Item_Count   Change_date   
------   ----------   ----------   -----------   
  123      2222          3          1/10/2015

[edit]已添加代码块 - OriginalGriff [/ edit]

[edit]Code block added - OriginalGriff[/edit]

推荐答案

尝试：

Try:

SELECT TOP 1 Car_id, Job_number, COUNT(Item_no) AS Item_count, Change_date FROM Table3
GROUP BY Change_Date, Car_id, Job_number
ORDER BY Change_date DESC

SELECT T3.car_id, T3.job_number, COUNT(T3.Item_no) as Item_Count, T3.Change_date
FROM Table3 T3
INNER JOIN 
    (
        --Get the max date for each car_id and job_number
        SELECT car_id,MAX(job_number) as job_number,MAX(Change_date) as Change_date
        FROM Table3
        GROUP BY car_id,job_number

    )X ON T3.Change_date = X.Change_date and T3.car_id = X.car_id and T3.job_number = X.job_number
GROUP BY T3.car_id , T3.job_number, T3.Change_date

这篇关于我如何在Sql Server中使用最大日期计算值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！