SQL MYSQL选择不包含任何重复的记录

Question

我有以下数据

 id |   bill_of_lading |  shipping_rate | 
____|__________________|________________|
   1|              8012|              90|
   2|              6045|              90|
   3|              6045|               0|
   4|              2423|               0|
   5|              5342|              90|
   6|              5643|              90|
   7|              5643|               0|
   8|              7545|               0|
   9|              9034|               0|

我想返回只出现1次bill_of_提单号的行，并使用结果更新shipping rate为0的记录上的shipping_rate。

例如，我想将id为{4,8,9}的运费设置为220，但不设置id为{3,7}的运费

我完全不确定在这种情况下使用什么语法

Answer 1

SELECT bill_of_lading, COUNT(*)
FROM t
GROUP BY bill_of_lading
HAVING COUNT(*) = 1

看它在行动:http://sqlfiddle.com/#!2/842d0/2

要使用结果更新相应的行，只需与它连接:

UPDATE t
JOIN (
    SELECT bill_of_lading, COUNT(*)
    FROM t
    GROUP BY bill_of_lading
    HAVING COUNT(*) = 1
) AS selection USING (bill_of_lading)
SET shipping_rate = 220

看这个在行动:http://sqlfiddle.com/#!2/87b61/1

Answer 2

试试这个:

UPDATE tablename 
SET shipping_rate = 220 
WHERE shipping_rate = 0 AND 
      id IN (SELECT id FROM (SELECT id FROM tablename 
             GROUP BY bill_of_lading HAVING COUNT(id) = 1) as A)

Answer 3

试试这个大小，其他人可能有更优雅的东西

SELECT *, COUNT(*) AS total FROM table_name GROUP BY bill_of_lading HAVING total = 1