SELECT A.F1,A.F2,A.F3 FROM TTH A INNER JOIN TTH B ON A.F1=B.F1 OR A.F2=B.F2 OR A.F3=B.F3 GROUP BY A.F1,A.F2,A.F3 having COUNT(B.F1)>=2 or COUNT(B.F2)>=2 or COUNT(B.F3)>=2
create table a(id int,t text,a1 int); insert into a values(1,'ta',0); insert into a values(1,'tc',0); insert into a values(3,'tt',7); insert into a values(4,'rt',7); insert into a values(4,'gg',7); insert into a values(6,'sr',2); insert into a values(7,'qq',4); insert into a values(8,'sd',5); insert into a values(8,'vs',5); insert into a values(10,'ws',4); insert into a values(11,'rf',9);SELECT A.id,A.t,A.a1 FROM a A INNER JOIN a B ON A.id=B.id OR A.t=B.t OR A.a1=B.a1 GROUP BY A.id,A.t,A.a1 having COUNT(B.id)>=2 or COUNT(B.t)>=2 or COUNT(B.a1)>=2; 贴不了图,你试试
mysql> SELECT A.id,A.t,A.a1 FROM a A INNER JOIN a B ON A.id=B.id OR A.t=B.t OR A .a1=B.a1 -> GROUP BY -> A.id,A.t,A.a1 -> HAVING COUNT(B.id)>=2 OR COUNT(B.t)>=2 OR COUNT(B.a1)>=2; +------+------+------+ | id | t | a1 | +------+------+------+ | 1 | ta | 0 | | 1 | tc | 0 | | 3 | tt | 7 | | 4 | gg | 7 | | 4 | rt | 7 | | 7 | qq | 4 | | 8 | sd | 5 | | 8 | vs | 5 | | 10 | ws | 4 | +------+------+------+ 9 rows in set (0.00 sec)mysql>
SELECT A.id,A.t,A.a1 FROM a A INNER JOIN a B ON (A.id=B.id AND (A.t=B.t OR A.a1=B.a1)) GROUP BY A.id,A.t,A.a1 HAVING COUNT(b.id)>=2你将(A.t=B.t OR A.a1=B.a1)中的字段名修改为你的字段名即可
A.t=B.t OR A.a1=B.a1? 我所有的字段都要来判断,是不是所有的都要些进去了,我有40多个字段了!!
不好意思,写快了点,应该是这样
A,B,C
A,E,C
A,G,H
A,E,G
这比如说是四条记录,我想取得
A,B,C
A,E,C
A,E,G
select * from a where c1=c2 or c1=c3 or c2=c3
比如
A,B,C
A,E,C
有A=A,C=C
A,G,H
A,E,G
也有相同的,A=A,G=G
啊! AGH也是要查出来的! 反正只要有两列以上相同的都查出来!
谢谢.......
GROUP BY
A.F1,A.F2,A.F3
having COUNT(B.F1)>=2 or COUNT(B.F2)>=2 or COUNT(B.F3)>=2
create table a(id int,t text,a1 int);
insert into a values(1,'ta',0);
insert into a values(1,'tc',0);
insert into a values(3,'tt',7);
insert into a values(4,'rt',7);
insert into a values(4,'gg',7);
insert into a values(6,'sr',2);
insert into a values(7,'qq',4);
insert into a values(8,'sd',5);
insert into a values(8,'vs',5);
insert into a values(10,'ws',4);
insert into a values(11,'rf',9);SELECT A.id,A.t,A.a1 FROM a A INNER JOIN a B ON A.id=B.id OR A.t=B.t OR A.a1=B.a1
GROUP BY
A.id,A.t,A.a1
having COUNT(B.id)>=2 or COUNT(B.t)>=2 or COUNT(B.a1)>=2;
贴不了图,你试试
.a1=B.a1
-> GROUP BY
-> A.id,A.t,A.a1
-> HAVING COUNT(B.id)>=2 OR COUNT(B.t)>=2 OR COUNT(B.a1)>=2;
+------+------+------+
| id | t | a1 |
+------+------+------+
| 1 | ta | 0 |
| 1 | tc | 0 |
| 3 | tt | 7 |
| 4 | gg | 7 |
| 4 | rt | 7 |
| 7 | qq | 4 |
| 8 | sd | 5 |
| 8 | vs | 5 |
| 10 | ws | 4 |
+------+------+------+
9 rows in set (0.00 sec)mysql>
1, 'tc', 0
4, 'gg', 7
4, 'rt', 7
8, 'sd', 5
8, 'vs', 5
GROUP BY
A.id,A.t,A.a1 HAVING COUNT(b.id)>=2
;
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`lrsj` varchar(45) DEFAULT NULL,
`hybh` varchar(45) DEFAULT NULL,
`xm` varchar(45) DEFAULT NULL,
`xb` varchar(45) DEFAULT NULL,
`csny` varchar(45) DEFAULT NULL,
`khdj` varchar(45) DEFAULT NULL,
`sj1` varchar(45) DEFAULT NULL,
`sj2` varchar(45) DEFAULT NULL,
`email` varchar(45) DEFAULT NULL,
`qq` varchar(45) DEFAULT NULL,
`zbgw` varchar(45) DEFAULT NULL,
`tjr` varchar(45) DEFAULT NULL,
`scys` varchar(45) DEFAULT NULL,
`sczs` varchar(45) DEFAULT NULL,
`swcz` varchar(45) DEFAULT NULL,
`bzcz` varchar(45) DEFAULT NULL,
`xaks` varchar(45) DEFAULT NULL,
`xacz` varchar(45) DEFAULT NULL,
`csjg` varchar(45) DEFAULT NULL,
`hffs` varchar(45) DEFAULT NULL,
`hfsj` varchar(45) DEFAULT NULL,
`djzt` varchar(45) DEFAULT NULL,
`ydsj` varchar(45) DEFAULT NULL,
`ydlx` varchar(45) DEFAULT NULL,
`cz` varchar(45) DEFAULT NULL,
`cpbh` varchar(45) DEFAULT NULL,
`yjdj` varchar(45) DEFAULT NULL,
`sfgm` varchar(45) DEFAULT NULL,
`gmsj` varchar(45) DEFAULT NULL,
`gmlx` varchar(45) DEFAULT NULL,
`gmcz` varchar(45) DEFAULT NULL,
`gmcpbh` varchar(45) DEFAULT NULL,
`gmjg` varchar(45) DEFAULT NULL,
`gmks` varchar(45) DEFAULT NULL,
`ywzs` varchar(45) DEFAULT NULL,
`zsbm` varchar(45) DEFAULT NULL,
`jtzycy` varchar(100) DEFAULT NULL,
`jtdz` varchar(100) DEFAULT NULL,
`gzdw` varchar(45) DEFAULT NULL,
`zw` varchar(45) DEFAULT NULL,
`sr` varchar(45) DEFAULT NULL,
`dwdz` varchar(100) DEFAULT NULL,
`bz` varchar(200) DEFAULT NULL,
`a` varchar(45) DEFAULT NULL,
`b` varchar(45) DEFAULT NULL,
`c` varchar(45) DEFAULT NULL,
`d` varchar(45) DEFAULT NULL,
`e` varchar(45) DEFAULT NULL,
`f` varchar(45) DEFAULT NULL,
`g` varchar(45) DEFAULT NULL,
`h` varchar(45) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;INSERT INTO `tjzb` (`ID`,`lrsj`,`hybh`,`xm`,`xb`,`csny`,`khdj`,`sj1`,`sj2`,`email`,`qq`,`zbgw`,`tjr`,`scys`,`sczs`,`swcz`,`bzcz`,`xaks`,`xacz`,`csjg`,`hffs`,`hfsj`,`djzt`,`ydsj`,`ydlx`,`cz`,`cpbh`,`yjdj`,`sfgm`,`gmsj`,`gmlx`,`gmcz`,`gmcpbh`,`gmjg`,`gmks`,`ywzs`,`zsbm`,`jtzycy`,`jtdz`,`gzdw`,`zw`,`sr`,`dwdz`,`bz`,`a`,`b`,`c`,`d`,`e`,`f`,`g`,`h`) VALUES
(1,'2010-6-6','','张三','男','1999-9-9','','6','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','6','','','','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
(2,'2010-6-6','','李四','女','1988-8-8','','6','','8','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','8',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
(3,'2010-6-6','','王五','男','2010-6-6','','6','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','3','','','','8',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);我从数据库导出来的...
GROUP BY
A.id,A.t,A.a1 HAVING COUNT(b.id)>=2你将(A.t=B.t OR A.a1=B.a1)中的字段名修改为你的字段名即可
我所有的字段都要来判断,是不是所有的都要些进去了,我有40多个字段了!!