SQL中每个组的最小值，但有一个警告

Question

我在下面的SQL中得到了这个表，我需要返回“如果购车者是一个理性的人，那么永远不会使用的汽车供应商”或“所有汽车价格都比其他人更贵的供应商”。我试着自己加入的想法，但我不能让它工作。由此产生的输出应该是供应商3，因为它对汽车3和4的价格比其他选项更昂贵。

id  car_vendor_id   vendor_name car_id  price    
---------------------------------------------
1        1            Vendor 1    1     25000    
2        1            Vendor 1    2     40000
3        2            Vendor 2    2     35000
4        2            Vendor 2    3     25000
5        3            Vendor 3    3     28000
6        3            Vendor 3    4     40000
7        4            Vendor 4    4     35000
8        4            Vendor 4    5     20000
9        5            Vendor 5    5     18000
10       5            Vendor 5    6     32000
11       6            Vendor 6    6     30000
12       6            Vendor 6    7     20000

Answer 1

一种方法是row_number()和聚合：

select car_vendor_id, vendor_name
from (select t.*,
             rank() over (partition by car_id order by price) as seqnum
      from t
     ) t
group by car_vendor_id, vendor_name
having min(seqnum) > 1; 

having子句选择供应商没有基于价格的“第一辆”汽车的行。

Answer 2

下面的查询使用CTE计算出每辆汽车的价格订单，因此最昂贵的是1。

然后，它排除那些不是最昂贵的供应商的行，最后检查它们不是汽车的唯一供应商。

declare @Car table(Vendor int, Car int, Price int)
insert @Car values (1,1,25000),(1,2,40000),(2,2,35000),(2,3,25000),(3,3,28000),(3,4,40000),(4,4,35000),(4,5,20000),(5,5,18000),(5,6,32000),(6,6,30000),(6,7,20000)
;with Price as (
    select *, row_number() over(partition by Car order by Price desc) as r from @Car Car
)
select * from Price
where not exists(select * from Price p2 where p2.Vendor=Price.Vendor and p2.r>1)
and Vendor not in (
select Vendor from @Car where Car in (select Car from @Car group by Car having count(*)=1)
)

Answer 3

检查下一个查询：

declare @car table(Vendor int, Car int, Price int);
insert @car
  values
    (1,1,25000),(1,2,40000),(2,2,35000),(2,3,25000),
    (3,3,28000),(3,4,40000),(4,4,35000),(4,5,20000),
    (5,5,18000),(5,6,32000),(6,6,30000),(6,7,20000);

with
  a as (
    select
      vendor, price,
      count(*) over(partition by car) cq,
      count(*) over(partition by vendor) vcq,
      max(price) over(partition by car) xcp
    from @car
  )
select vendor
from a
where cq > 1 and xcp = price
group by vendor, vcq
having count(*) = vcq;

要尝试在线查询，请单击here。