sql - 从每个类别中至少选择一个但不超过一个，并且没有与另一列重复

Question

给定一个表，如下所示:

 Id | Vehicle Type | Manufacturer
 --------------------------------
 1  | Car          | SpaceCo
 2  | Car          | NeatCarsInc
 3  | Car          | NeatCarsInc
 4  | Spaceship    | SpaceCo
 5  | Spaceship    | NeatCarsInc
 6  | Spaceship    | SpaceCo
 7  | Boat         | WeMakeBoats
 8  | Boat         | SpaceCo
 9  | Boat         | NeatCarsInc

我需要能够编写满足以下条件的查询:

请求 #1 确保给定的车辆类型在结果集中没有重复

请求 #2 确保给定的制造商在结果集中没有重复

请求 #3 如果车辆类型有任何条目，那么每个车辆类型都应该有一个输出行(在 Req #1 和 Req #2 的约束内)

注意:每个Manufacturer在结果集中可以出现0-1次，没有要求尝试选择每个Manufacturer

我并不特别关注返回哪个 Id(例如，Minimum Id 就足够了)。

有效的结果集可能是:

1  | Car          | SpaceCo
5  | Spaceship    | NeatCarsInc      
7  | Boat         | WeMakeBoats  

这同样可以接受:

2  | Car          | NeatCarsInc
4  | Spaceship    | SpaceCo      
7  | Boat         | WeMakeBoats  

但是，理想情况下，这将 不是 是可以接受的(因为它为 Car Vehicle Type 留下了一行，从而违反了 Req #3):

9  | Boat         | NeatCarsInc
6  | Spaceship    | SpaceCo   

重要的是 都不是 车辆类型或制造商列有任何重复，并且如果可能，为每个车辆类型选择一个条目。

请注意，这与此问题相似但不同: Select at least one from each category?因为我们 (1) 使用两个类别，并且 (2) 不允许任何一个类别重复条目。

尝试的方法

反射(reflection)一下，我不确定这在 SQL 中是否可行，因为它听起来非常接近背包问题......

我能得到的最接近的是:

对车辆类型进行排名，为每个车辆类型选择第一个条目

将此集合排在制造商之上，为每个制造商选择第一个条目

这要求我违反 请求 #3 ，即它可以根据使用的排名顺序产生以下任何结果:

按升序编号排序:

1  | Car          | SpaceCo
7  | Boat         | WeMakeBoats   

按降序 Id 排序:

6  | Spaceship    | SpaceCo
9  | Boat         | NeatCarsInc  

按随机 ID 排序(可能性):

2  | Car          | NeatCarsInc
6  | Spaceship    | SpaceCo
7  | Boat         | WeMakeBoats  

示例 SQL 如下:

   SELECT Id, VehicleType, Manufacturer
   FROM
   (
     SELECT 
       RANK() OVER (PARTITION BY Manufacturer ORDER BY [Id] ASC) ManufacturerRank,
       Id,
       VehicleType,
       Manufacturer
     FROM
     (
       SELECT 
         RANK() OVER (PARTITION BY VehicleType ORDER BY [Id] ASC) VehicleRank,
         Id,
         VehicleType,
         Manufacturer
       FROM
         Vehicles
     ) RankedPerVehicleType
     WHERE VehicleRank = 1
   ) RankedPerManufacturer
   WHERE ManufacturerRank = 1

Answer 1

如果你不介意ID，这可以让你给个办法

CREATE TABLE #TAB (Id INT, Vehicle_Type VARCHAR(250), Manufacturer VARCHAR(250))

INSERT INTO #TAB
SELECT 1,'Car','SpaceCo'
UNION ALL
SELECT 2,'Car','NeatCarsInc'
UNION ALL
SELECT 3,'Car','NeatCarsInc'
UNION ALL
SELECT 4,'Spaceship','SpaceCo'
UNION ALL
SELECT 5,'Spaceship','NeatCarsInc'
UNION ALL
SELECT 6,'Spaceship','SpaceCo'
UNION ALL
SELECT 7,'Boat','WeMakeBoats'
UNION ALL
SELECT 8,'Boat','SpaceCo'
UNION ALL
SELECT 9,'Boat','NeatCarsInc'


SELECT  Vehicle_Type,Manufacturer  
FROM (
SELECT DISTINCT DENSE_RANK() OVER ( ORDER BY Vehicle_Type) AS SNO, Vehicle_Type FROM #TAB 
)VT 
INNER JOIN (
SELECT DISTINCT DENSE_RANK() OVER ( ORDER BY Manufacturer) AS SNO,  Manufacturer FROM #TAB 
)MF
ON VT.SNO= MF.SNO

结果将是

+--------------+--------------+
| Vehicle_Type | Manufacturer |
+--------------+--------------+
| Boat         | NeatCarsInc  |
| Car          | SpaceCo      |
| Spaceship    | WeMakeBoats  |
+--------------+--------------+