create table test
(
name varchar(20),
name2 varchar(20)
)
insert into test select 'a','aa'
insert into test select 'a','aa'
insert into test select 'b','bb'
insert into test select 'b','bb'
insert into test select 'c','cc'
insert into test select 'd','dd'
insert into test select 'e','ee'
insert into test select 'e','ee'
insert into test select 'f','ff'
insert into test select 'g','gg'
insert into test select 'g','gg'
insert into test select 'h','hh'执行
mysql> select name,name2
-> from test a
-> where 2=(select count(*) from test where name=a.name);/*
得到这样的效果:
name,name2
a,aa
a,aa
b,bb
b,bb
e,ee
e,ee
g,gg
g,gg
*//*
问的是如何通过update语句达到以下效果:
name,name2
za,zaa
za,zaa
zb,zbb
zb,zbb
ze,zee
ze,zee
zg,zgg
zg,zgg
*/
(
name varchar(20),
name2 varchar(20)
)
insert into test select 'a','aa'
insert into test select 'a','aa'
insert into test select 'b','bb'
insert into test select 'b','bb'
insert into test select 'c','cc'
insert into test select 'd','dd'
insert into test select 'e','ee'
insert into test select 'e','ee'
insert into test select 'f','ff'
insert into test select 'g','gg'
insert into test select 'g','gg'
insert into test select 'h','hh'执行
mysql> select name,name2
-> from test a
-> where 2=(select count(*) from test where name=a.name);/*
得到这样的效果:
name,name2
a,aa
a,aa
b,bb
b,bb
e,ee
e,ee
g,gg
g,gg
*//*
问的是如何通过update语句达到以下效果:
name,name2
za,zaa
za,zaa
zb,zbb
zb,zbb
ze,zee
ze,zee
zg,zgg
zg,zgg
*/
+------+-------+
| name | name2 |
+------+-------+
| a | aa |
| a | aa |
| b | bb |
| b | bb |
| c | cc |
| d | dd |
| e | ee |
| e | ee |
| f | ff |
| g | gg |
| g | gg |
| h | hh |
+------+-------+
12 rows in set (0.00 sec)mysql> update test a inner join
-> (select name from test group by name having count(*)=2) b on a.name=b.name
-> set a.name=concat('z',a.name),a.name2=concat('z',a.name2);
Query OK, 8 rows affected (0.19 sec)
Rows matched: 8 Changed: 8 Warnings: 0mysql> select * from test;
+------+-------+
| name | name2 |
+------+-------+
| za | zaa |
| za | zaa |
| zb | zbb |
| zb | zbb |
| c | cc |
| d | dd |
| ze | zee |
| ze | zee |
| f | ff |
| zg | zgg |
| zg | zgg |
| h | hh |
+------+-------+
12 rows in set (0.00 sec)mysql>
(select name,name2 from test a where 2=(select count(*) from test where name=a.name))b
set a.name=concat(b.name,'z'),a.name2=concat(b.name2,'z')
where a.name=b,name and a.name2=b.name2
实在是万分抱歉,效果说错了。
name,name2
za,zaa
a,aa
zb,zbb
b,bb
ze,zee
e,ee
zg,zgg
g,gg
*/
就是希望这两个字段里没有重复的值
(
id int(8) PRIMARY KEY,
name varchar(20),
name2 varchar(20)
)
insert into test select '1','a','aa'
insert into test select '2','a','aa'
insert into test select '3','b','bb'
insert into test select '4','b','bb'
insert into test select '5','c','cc'
insert into test select '6','d','dd'
insert into test select '7','e','ee'
insert into test select '8','e','ee'
insert into test select '9','f','ff'
insert into test select '10','g','gg'
insert into test select '11','g','gg'
insert into test select '12','h','hh'执行
mysql> select name,name2
-> from test a
-> where 2=(select count(*) from test where name=a.name);/*
得到这样的效果:
id,name,name2
1,a,aa
2,a,aa
3,b,bb
4,b,bb
7,e,ee
8,e,ee
10,g,gg
11,g,gg
*//*
问的是如何通过update语句达到以下效果:
id,name,name2
1,za,zaa
2,a,aa
3,zb,zbb
4,b,bb
7,ze,zee
8,e,ee
10,zg,zgg
11,g,gg
*/
(select id, name,name2 from test a where 2=(select count(*) from test where name=a.name)
group by name,name2)b
set a.name=concat(b.name,'z'),a.name2=concat(b.name2,'z')
where a.id=b.id
+----+------+-------+
| id | name | name2 |
+----+------+-------+
| 1 | a | aa |
| 2 | a | aa |
| 3 | b | bb |
| 4 | b | bb |
| 5 | c | cc |
| 6 | d | dd |
| 7 | e | ee |
| 8 | e | ee |
| 9 | f | ff |
| 10 | g | gg |
| 11 | g | gg |
| 12 | h | hh |
+----+------+-------+
12 rows in set (0.00 sec)mysql> update test a inner join (select name,min(id) as id from test group by name having count(*)=2) b on a.id=b.id
-> set a.name=concat('z',a.name),a.name2=concat('z',a.name2);;
Query OK, 4 rows affected (0.13 sec)
Rows matched: 4 Changed: 4 Warnings: 0mysql> select * from test;
+----+------+-------+
| id | name | name2 |
+----+------+-------+
| 1 | za | zaa |
| 2 | a | aa |
| 3 | zb | zbb |
| 4 | b | bb |
| 5 | c | cc |
| 6 | d | dd |
| 7 | ze | zee |
| 8 | e | ee |
| 9 | f | ff |
| 10 | zg | zgg |
| 11 | g | gg |
| 12 | h | hh |
+----+------+-------+
12 rows in set (0.00 sec)mysql>
mysql> update test a inner join (select name,min(id) as id from test group by name having count(*)=2) b on a.id=b.id
-> set a.name=concat('z',a.name),a.name2=concat('z',a.name2);;count(*)=2,指的是两个相同的,如果改成3就是查出3个相同的,以此类推,那我想查处所有相同的该如何写(包括2个相同,3个相同,4个相同)?