表中有
namea nameb rpow其中namea和nameb组合为唯一索引..
假设现在有数据A B 1
C B 2而我使用语句
update mytable set namea= "A" where namea="C";
这肯定是错了,因为 A-B 是唯一索引..如果我想再出现duplicate key的情况下,依然执行,且合并为一条记录,rpow =
所有冲突的之和,上例为3.
该怎么写?
namea nameb rpow其中namea和nameb组合为唯一索引..
假设现在有数据A B 1
C B 2而我使用语句
update mytable set namea= "A" where namea="C";
这肯定是错了,因为 A-B 是唯一索引..如果我想再出现duplicate key的情况下,依然执行,且合并为一条记录,rpow =
所有冲突的之和,上例为3.
该怎么写?
+-------+-------+------+
| namea | nameb | rpow |
+-------+-------+------+
| A | B | 1 |
| C | B | 2 |
+-------+-------+------+
2 rows in set (0.00 sec)mysql> update t_seadaughter a inner join (select nameb,sum(rpow) as sum_rpow fro
m t_seadaughter group by nameb) b
-> on a.nameb=b.nameb
-> set a.rpow=b.sum_rpow;
Query OK, 2 rows affected (0.05 sec)
Rows matched: 2 Changed: 2 Warnings: 0mysql> select * from t_seadaughter;
+-------+-------+------+
| namea | nameb | rpow |
+-------+-------+------+
| A | B | 3 |
| C | B | 3 |
+-------+-------+------+
2 rows in set (0.00 sec)mysql>
结果只存在:
+-------+-------+------+
| namea | nameb | rpow |
+-------+-------+------+
| A | B | 3 |
+-------+-------+------+
from t_seadaughter
group by namebmysql> select * from t_seadaughter;
+-------+-------+------+
| namea | nameb | rpow |
+-------+-------+------+
| A | B | 3 |
| C | B | 3 |
+-------+-------+------+
2 rows in set (0.00 sec)mysql> select min(namea),nameb,sum(rpow)
-> from t_seadaughter
-> group by nameb;
+------------+-------+-----------+
| min(namea) | nameb | sum(rpow) |
+------------+-------+-----------+
| A | B | 6 |
+------------+-------+-----------+
1 row in set (0.00 sec)mysql>
where a.nameb=b.nameb and a.namea>b.min_namea;你可以通过这句将不需要的记录删除。或者就要使用触发器。