Sql server MAX值行get [英] Sql server MAX value row get

问题描述

CustomerID   StartWeight   EndWeight    Charges    AddWeight    AddCharges
1              0.00          0.50        50          0            0
1              0.60          1.00        100         1            70
2              0.00          0.50        60          0            0
2              0.60          1.00        110         1            80
3              0.00          1.00        120         1            100

select * from table其中CustomerID = 1

这显示前两行的结果。



我尝试了什么： < br $>

select * from table where CustomerID=1
this show the result of first two line.

What I have tried:

select * from table where CustomerAccountID=1 and EndWeight=(select MAX(EndWeight) from table)

这个显示空值，而我想显示第二行

This show null value while i want to show the second row

推荐答案

不完全确定问题是什么随着你的查询/为什么你得到不正确的数据，我嘲笑你你的数据集和查询提供了预期的结果。

Not entirely sure what the problem is with your query/why you are getting incorrect data, i mocked up your data set and the query provides expected results.

DECLARE @CpMaxValueRow TABLE (
	CustomerId INT NULL,
	EndWeight FLOAT NULL
);

INSERT INTO @CpMaxValueRow
        ( CustomerId, EndWeight )
VALUES  ( 1, -- CustomerId - int
          0.5  -- EndWeight - float
          )
INSERT INTO @CpMaxValueRow
        ( CustomerId, EndWeight )
VALUES  ( 1, -- CustomerId - int
          1  -- EndWeight - float
          )
INSERT INTO @CpMaxValueRow
        ( CustomerId, EndWeight )
VALUES  ( 2, -- CustomerId - int
          .5  -- EndWeight - float
          )
INSERT INTO @CpMaxValueRow
        ( CustomerId, EndWeight )
VALUES  ( 2, -- CustomerId - int
          1  -- EndWeight - float
          )
INSERT INTO @CpMaxValueRow
        ( CustomerId, EndWeight )
VALUES  ( 2, -- CustomerId - int
          1  -- EndWeight - float
          )

		  SELECT * FROM @CpMaxValueRow AS A WHERE CustomerId = 1 AND EndWeight = (SELECT MAX(B.EndWeight) FROM @CpMaxValueRow AS B)

这篇关于Sql server MAX值行get的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！