添加 SQL 大小写以在更新查询中提供默认值

Question

我正在处理一个查询，该查询将根据用户的纬度/经度坐标将用户分配到特定的建筑物。 该查询确实有效并将建筑物 ID 值分配给关联的用户，但如果用户当前位置不存在建筑物，我想分配"未知位置"的值。

本质上，如果位置已知 SET u.building_id = b.building_id，否则 SET u.building_id = "未知位置";

当前查询如下所示：

UPDATE USERS u 
JOIN BUILDINGS b ON u.latitude BETWEEN (b.latitude - 0.001) AND (b.latitude + 0.001)
    AND u.longitude BETWEEN (b.longitude - 0.001) AND (b.longitude + 0.001)
SET u.building_id = b.building_id

我认为这可以通过添加SQL案例来完成吗？ 我应该如何处理这个问题？

Answer 1

你可以这样使用CASE，并带有LEFT JOIN

UPDATE USERS u
LEFT JOIN BUILDINGS b 
  ON u.latitude BETWEEN (b.latitude - 0.001) AND (b.latitude + 0.001)
  AND u.longitude BETWEEN (b.longitude - 0.001) AND (b.longitude + 0.001)
SET u.building_id = 
    CASE 
       WHEN b.building_id IS NULL THEN 'unknown location'
       ELSE b.building_id
    END;

虽然我个人会用COALESCE做同样的事情：

UPDATE USERS u
LEFT JOIN BUILDINGS b 
     ON u.latitude BETWEEN (b.latitude - 0.001) AND (b.latitude + 0.001)
     AND u.longitude BETWEEN (b.longitude - 0.001) AND (b.longitude + 0.001)
SET u.building_id = COALESCE(b.building_id, 'unknown location');

将INNER JOIN替换为LEFT JOIN将确保所有用户都得到更新，包括那些没有匹配建筑物的用户（因此NULL building_id）。请注意，如果经度/经度有多个匹配项，则建筑物的分配将是相当随意的。

SqlFiddle here

Answer 2

你可以试试这个：

UPDATE USERS u JOIN BUILDINGS b ON u.latitude BETWEEN (b.latitude - 0.001) AND (b.latitude + 0.001)
AND
u.longitude BETWEEN (b.longitude - 0.001) AND (b.longitude + 0.001)
SET u.building_id = (case when **location known** then u.building_id = b.building_id ELSE u.building_id = 'unknown location' END)

显然，您需要更改逻辑表达式已知的位置

Answer 3

UPDATE USERS u LEFT JOIN BUILDINGS b ON u.latitude BETWEEN (b.latitude - 0.001) AND (b.latitude + 0.001)
AND
u.longitude BETWEEN (b.longitude - 0.001) AND (b.longitude + 0.001)
SET u.building_id = IF( ISNULL(b.building_id), 'unknown', b.building_id);