mysql - 查找与一个字段匹配的相关实体

Question

我有几个(MySQL)表。有趣的是CampaignRegion它将外键存储到 Campaign和一个外键 City , County , District , State或Country 。始终只有一个特定于位置的外键集，其他为 NULL .

CampaignRegion表格如下所示:

mysql> describe CampaignRegion;
+-------------+---------+------+-----+---------+----------------+
| Field       | Type    | Null | Key | Default | Extra          |
+-------------+---------+------+-----+---------+----------------+
| id          | int(11) | NO   | PRI | NULL    | auto_increment |
| campaign_id | int(11) | YES  | MUL | NULL    |                |
| country_id  | int(11) | YES  | MUL | NULL    |                |
| state_id    | int(11) | YES  | MUL | NULL    |                |
| district_id | int(11) | YES  | MUL | NULL    |                |
| county_id   | int(11) | YES  | MUL | NULL    |                |
| city_id     | int(11) | YES  | MUL | NULL    |                |
+-------------+---------+------+-----+---------+----------------+

我现在需要一个查询来选择 Campaign 。我有一个City并且需要获取Campaigns匹配 City本身或 County或District或State或Country City的.

这实际上非常简单，我想出了这样的东西:

SELECT count(id) as campaign_count, campaign_id
  FROM CampaignRegion
 WHERE (city_id = 32076 OR district_id = 127 OR state_id = 7 OR country_id = 156)
 GROUP BY campaign_id
HAVING campaign_count > 0;

但是我想得到Campaigns的结果集排序匹配 City首先，然后是匹配 County 的，然后是匹配 District 的等等(比如查询中 OR 的顺序。有没有一种简单的方法可以做到这一点？我会根据 SELECT 中的此查询对 Campaign (第一个匹配)JOIN 做什么？或SUBSELECT？你觉得怎么样？

感谢您的帮助。

Answer 1

我认为这就是您正在寻找的:

SELECT count(id) as campaign_count, campaign_id
FROM CampaignRegion
WHERE (city_id = 32076 OR district_id = 127 OR state_id = 7 OR country_id = 156)
GROUP BY campaign_id
HAVING campaign_count > 0;
ORDER BY (city_id = 32076) DESC, (country_id = 156) DESC, (state_id = 7) DESC, (district_id = 127) DESC;