>我有两个名为 public 和 private 的表，其中 private 从 public 继承主键 （id），以便 private.id ⊆ public
我想从两个表中查询最多与某些条件匹配的最大总记录数，但如果 id 驻留在两个表中，我会使用 private 中的行，因为数据不同。
我相信天真的方法是两个问题：
SELECT id, data, lat, lng, (function($lat,$lng)) AS distance
FROM private
WHERE lng BETWEEN $lng1 AND $lng2
AND lat BETWEEN $lat1 and $lat2
HAVING distance < $dist LIMIT 0, $max;
$remaining = $max - $result->num_rows;
if(remaining > 0){
SELECT id, data, lat, lng, (function($lat,$lng)) AS distance
FROM public
WHERE lng BETWEEN $lng1 AND $lng2
AND lat BETWEEN $lat1 and $lat2
HAVING distance < $dist LIMIT 0, $remaining;
}
虽然这似乎是UNION的完美位置，尽管我似乎错过了如何根据文档中第一个选择的结果限制第二个选择。
编辑：删除了联接并阐明了变量。
向结果集添加一个附加列，并在并集后对其进行排序
(SELECT id, data, lat, lng, (function($lat,$lng)) AS distance, 1 as OrderCol
FROM private
WHERE lng BETWEEN $lng1 AND $lng2
AND lat BETWEEN $lat1 and $lat2
HAVING distance < $dist LIMIT 0, $max)
UNION
(SELECT id, data, lat, lng, (function($lat,$lng)) AS distance, 2 AS OrderCol
FROM public
WHERE lng BETWEEN $lng1 AND $lng2
AND lat BETWEEN $lat1 and $lat2
HAVING distance < $dist LIMIT 0, $max) ORDER ON OrderCol;
第二个 SELECT 将需要额外的 JOIN 运算符来从私有中排除重复项。
首先为每个SELECT分配一个组标识符
然后使用变量创建row_id。
最后过滤row_id。
SQL 小提琴演示
SET @row_total = 10;
SELECT *
FROM
(
SELECT distance,
@row := @row + 1 as rn
FROM
(
SELECT `BadgeCode` as distance,
'private' as grp
FROM Table1
UNION ALL
SELECT `Dept`as distance,
'public' as grp
FROM Table2
LEFT JOIN Table1
ON Table2.`Dept` = Table1.`BadgeCode`
WHERE Table1.`BadgeCode` IS NULL
) X
CROSS JOIN (SELECT @row :=0) Y
ORDER BY grp
) T
WHERE rn <= @row_total;