有表 1
id 字段2
1 a 2 b3 b求一语句选 出 重复的项。如,,,得到id 字段2
2 b3 b
因为我要把得重复的改一下。改成不重复。。如改成id 字段2
2 b3 b2如果有一直能改成不重复的语句。更好。
哪位大侠来。帮帮忙。。谢谢了。。
id 字段2
1 a 2 b3 b求一语句选 出 重复的项。如,,,得到id 字段2
2 b3 b
因为我要把得重复的改一下。改成不重复。。如改成id 字段2
2 b3 b2如果有一直能改成不重复的语句。更好。
哪位大侠来。帮帮忙。。谢谢了。。
set 字段2=concat(字段2,trim(id))
where 字段2 in(select 字段2 from tb group by 字段2 having count(1)>1)
/**
id 字段2
1 a
2 b2
3 b3
**/
select a.id, a.字段2 from tt a left join tt b on a.字段2=b.字段2
group by a.id, a.字段2 having count(b.id)>=2
(select 字段2 from tt group by 字段2 having count(*)>=2) b
on tt.字段2=b.字段2
set tt.字段2=concat(tt.字段2,trim(tt.id))
+------+------+
| id | col |
+------+------+
| 1 | a |
| 2 | b |
| 3 | b |
+------+------+
3 rows in set (0.00 sec)mysql> -- 求一语句选 出 重复的项。如,,,得到
mysql> select * from t_fengchujun a
-> where exists (select 1 from t_fengchujun where col=a.col and id!=a.id);
+------+------+
| id | col |
+------+------+
| 2 | b |
| 3 | b |
+------+------+
2 rows in set (0.00 sec)mysql>
-- 因为我要把得重复的改一下。改成不重复。。如改成
mysql> update t_fengchujun a,(
-> select b.id,count(*) as cnt
-> from t_fengchujun b inner join t_fengchujun c on b.col=c.col and b.id>=c.id
-> group by b.id
-> having count(*)>1
-> ) d
-> set a.col = concat(a.col,d.cnt)
-> where a.id=d.id;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from t_fengchujun;
+------+------+
| id | col |
+------+------+
| 1 | a |
| 2 | b |
| 3 | b2 |
+------+------+
3 rows in set (0.00 sec)mysql>
select id, 字段2
from 表1 a inner join
(select 字段2 from 表1 group by 字段2 having count(id)>1) b
on a.字段2=b.字段2改成不重复的:
update 表1 inner join
(select 字段2 from 表1 group by 字段2 having count(id)>1) b
on a.字段2=b.字段2
set a.字段2=concat(a.字段2,trim(a.id))
update 表1 inner join
(select 字段2 from 表1 group by 字段2 having count(id)>1) b
on 表1.字段2=b.字段2
set 表1.字段2=concat(表1.字段2,trim(表1.id))