mysql - 存在语句时的MySQL多种情况

我有两张桌子。我们称之为：座位和座位分配规则表。
下面是表架构：

CREATE TABLE IF NOT EXISTS `SEATS` (
  `SeatID` int(11) NOT NULL AUTO_INCREMENT,
  `SeatName` varchar(255) NOT NULL DEFAULT '',
   PRIMARY KEY (`SeatID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

INSERT INTO `SEATS` (`SeatID`, `SeatName`) VALUES
(1, 'Super VIP'),
(2, 'VIP'),
(3, 'Business'),
(4, 'Economy'),
(5, 'Standing');

CREATE TABLE IF NOT EXISTS `SEAT_ALLOCATION_RULE` (
 `SeatID` int(11) NOT NULL DEFAULT '0',
 `Origin` varchar(50) NOT NULL DEFAULT '0',
 `Destination` varchar(50) NOT NULL DEFAULT '',
 `Passenger_Type` varchar(25) NOT NULL DEFAULT '',
  PRIMARY KEY (`SeatID`,`Origin`,`Destination`,`Passenger_Type`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `SEAT_ALLOCATION_RULE` (`SeatID`, `Origin`, `Destination, `Passenger_Type`) VALUES
(1, 'Malaysia','',''),
(2, 'Malaysia','Singapore',''),
(3, 'Malaysia','Singapore','Senior_Citizen'),
(4, 'Bangkok','Japan','Student'),
(5, 'Cambodia','China','Senior_Citizen');

座位分配规则表根据以下优先顺序确定乘客应分配到哪个座位：

1. Origin, destination, and  passenger_type match
2. Origin and destination match
3. Origin match

这意味着，如果所有字段（始发地、目的地和乘客类型）都匹配，那么它应该比只有两个字段匹配等等时具有更高的优先级。如果列为空，则将其视为未指定，因此具有较低的优先级。例如：
如果始发地是马来西亚，目的地是新加坡，乘客类型是老年人，则应返回Seatid 3
如果始发地是马来西亚，目的地是新加坡，旅客类型是学生，则应返回SEATID 2（因为它只匹配始发地和目的地）
如果始发地是马来西亚，目的地是美国，乘客类型是学生，则应返回seatid 1（因为它只匹配始发地）。
根据以上规则，如果始发地为马来西亚，目的地为新加坡，旅客类型为学生，则返回seatid的查询如下：

SELECT s.SeatID, s.SeatName
FROM SEATS s
WHERE
CASE WHEN EXISTS(
  select 1
  from SEAT_ALLOCATION_RULE r
  where s.SeatID = r.SeatID
  AND r.Origin = 'Malaysia'
  AND r.Destination = 'Singapore'
  AND r.Passenger_Type='Student') Then 1
WHEN EXISTS(
  select 1
  from SEAT_ALLOCATION_RULE r
  where s.SeatID = r.SeatID
  AND r.Origin = 'Malaysia'
  AND r.Destination = 'Singapore'
  AND r.Passenger_Type='') Then 1
WHEN EXISTS(
  select 1
  from SEAT_ALLOCATION_RULE r
  where s.SeatID = r.SeatID
  AND r.Origin = 'Malaysia'
  AND r.Destination = ''
  AND r.Passenger_Type='') Then 1 END

但是，上面的查询不起作用，因为它将返回seatid 1和2，但是预期的输出只有seatid 2（因为源和目标匹配，并且它具有更高的优先级）。有人能帮我更正SQL查询吗？

最佳答案

这应该可以做到：

select seatid
  from seat_allocation_rule sar
order by ((sar.origin = :origin) << 2) + ((sar.destination = :destination) << 1) + (sar.passenger_type = :passenger_type) desc,
         ((sar.origin <> '')     << 2) + ((sar.destination <> '')          << 1) + (sar.passenger_type <> '')             asc
 limit 1

要了解如何：

create table testcase (
    origin         varchar(255),
    destination    varchar(255),
    passenger_type varchar(255),
    expected_seat  int(11)
);

insert into testcase values ('Malaysia','Singapore','Senior_Citizen',3),
                            ('Malaysia','Singapore','Student',2),
                            ('Malaysia','US','Student',1);

select * from (
select tc.*,
       sar.seatid,
       case when sar.seatid = tc.expected_seat then 'Y' else '-' end as pass,
         ((sar.origin = tc.origin) << 2)
       + ((sar.destination = tc.destination) << 1)
       + ((sar.passenger_type = tc.passenger_type) << 0) as score,
         ((sar.origin <> '') << 2)
       + ((sar.destination <> '') << 1)
       + ((sar.passenger_type <> '') << 0) as priority
  from seat_allocation_rule sar
       cross join testcase tc
) x order by expected_seat desc, score desc, priority asc;

关于mysql - 存在语句时的MySQL多种情况，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/48648404/