当项目没有按顺序排列时，多个SQL MAX

Question

我有一些数据如下:

DECLARE @MyTable AS TABLE 
(productName varchar(13), test1  int,test2  int)

INSERT INTO @MyTable
    (productName, test1,test2)
VALUES
    ('a', 1,1),
    ('a', 2,2),
    ('a', 3,3),
    ('b', 1,4),
    ('b', 2,5),
    ('b', 3,6),
    ('a', 1,7),
    ('a', 4,8),
    ('a', 5,9)
;
SELECT productname,MAX(test1) from @MyTable group BY productname

对test1列的MAX查询得到

a,5 
b,3

但是我需要结果为

a,3 
b,3 
a,5 

当我有order by test2

Answer 1

您可以通过使用row_numbers技巧来解决这个问题，这样您就可以分配2个不同的行号，一个用于整个数据，另一个按productname进行分区。如果你比较这些数字之间的差异，你可以弄清楚什么时候产品名称发生了变化，并使用它来确定每组的最大值。

select productname, max(test1) from (
SELECT *,
    row_number() over (order by test2 asc) -
    row_number() over (partition by productname order by test2 asc) as GRP 
from @MyTable
) X 
group by productname, GRP

可以在SQL Fiddle

中进行测试。

如果test2列总是一个没有空格的行号，您也可以使用它来代替第一个行号列。如果您需要在数据中排序，您将不得不使用例如test1的max来做到这一点。

Answer 2

请查看以下SQL Select语句

DECLARE @MyTable AS TABLE (productName varchar(13), test1  int,test2  int)
INSERT INTO @MyTable
    (productName, test1,test2)
VALUES
    ('a', 1,1),
    ('a', 2,2),
    ('a', 3,3),
    ('b', 1,4),
    ('b', 2,5),
    ('b', 3,6),
    ('a', 1,7),
    ('a', 4,8),
    ('a', 5,9)
DECLARE @MyTableNew AS TABLE (id int identity(1,1), productName varchar(13), test1  int,test2  int)
insert into @MyTableNew select * from @MyTable
--select * from @MyTableNew
;with cte as (
    SELECT
        id, productName, test1, test2,
        case when (lag(productName,1,'') over (order by id)) = productName then 0 else 1 end ischange
    from @MyTableNew
), cte2 as (
    select t.*,(select sum(ischange) from cte where id <= t.id) grp from cte t
)
select distinct grp, productName, max(test1) over (partition by grp) from cte2

这是根据以下SQL Server Lag()函数教程实现的Lag()函数用于识别和排序表数据

Answer 3

请尝试此查询

DECLARE @MyTable AS TABLE 
(productName varchar(13), test1  int,test2  int)

INSERT INTO @MyTable
    (productName, test1,test2)
VALUES
    ('a', 1,1),
    ('a', 2,2),
    ('a', 3,3),
    ('b', 1,4),
    ('b', 2,5),
    ('b', 3,6),
    ('a', 1,7),
    ('a', 4,8),
    ('a', 5,9)
;
SELECT productname,MAX(test1) 
from @MyTable 
where test1 = test2
group BY productname
union all
SELECT productname,MAX(test1) 
from @MyTable 
where test1 != test2
group BY productname