我有个mysql的表,表定义如下:
create table a (
r1 TEXT,
r2 TEXT );
现在想统计如下信息:若有两个记录,其r1列值相同,r2列值不同,我们就成为相应的r2列值的构成了r2对,有可能有一个r2对,其每构成一次r2对时对应的r1列值分别为(aaa, bbb, ccc, ddd),就认为对应r1值相同的该r2的值为4.
还有可能有另一个r2对,其每构成一次r2对的时对应的r1列值分别为(eee, fff, ggg, hhh),还有可能有另一个r2对,其每构成一次r2对的时候对应的r1列值分别为(www, vvv, jjj, kkk)
那么,我们查询的时候,对应的r1列值相同的成对r2的值为4的r2对有3个。
通过在sql语句中设置值为4,返回结果3. 如何实现这样的查询呢?
create table a (
r1 TEXT,
r2 TEXT );
现在想统计如下信息:若有两个记录,其r1列值相同,r2列值不同,我们就成为相应的r2列值的构成了r2对,有可能有一个r2对,其每构成一次r2对时对应的r1列值分别为(aaa, bbb, ccc, ddd),就认为对应r1值相同的该r2的值为4.
还有可能有另一个r2对,其每构成一次r2对的时对应的r1列值分别为(eee, fff, ggg, hhh),还有可能有另一个r2对,其每构成一次r2对的时候对应的r1列值分别为(www, vvv, jjj, kkk)
那么,我们查询的时候,对应的r1列值相同的成对r2的值为4的r2对有3个。
通过在sql语句中设置值为4,返回结果3. 如何实现这样的查询呢?
r1 r2
aaa bbb
aaa ccc
aaa ddd
ccc eee
ccc fff
ddd ggg
hhh bbb
hhh ccc
xxx bbb
xxx ccc
yyy eee
yyy fff
www eee
www fff
我们从直观上可以获得如下信息:
相对于r2列,(bbb,ccc)对对应的r1值分别为(aaa, hhh, xxx),(eee,fff)对对应的r1列值分别为(ccc, yyy, www),那么对应的r1列值相同的成对r2的值为3的r2对有2个
(bbb,ddd)对对应的r1列值为(aaa),(ccc, ddd)对对应的r1列值为(aaa),那么就认为对应的r1列值相同的成对r2的值为1的r2对有2个
select t1.r2,t2.r2 from a t1 inner join a t2 on t1.r1=t2.r1 and t1.r2<t2.r2 group by t1.r2,t2.r2 having count(*)=3) a ;select count(*) from (
select t1.r2,t2.r2 from a t1 inner join a t2 on t1.r1=t2.r1 and t1.r2<t2.r2 group by t1.r2,t2.r2 having count(*)=1) a ;
select t1.r2,t2.r2 from test t1 inner join test t2 on t1.r1=t2.r1 and t1.r2<t2.r2 group by t1.r2,t2.r2 having count(*)=1) test ;
我真正的表名是test,提示的错误信息为:
“duplicate column name r2”
select t1.r2,t2.r2 from test t1 inner join test t2 on t1.r1=t2.r1 and t1.r2<t2.r2 group by t1.r2,t2.r2 having count(*)=3) test ;
我的表名为test
错误信息为“duplicate column name r2”
select count(*) from (
select t1.r2,t2.r2 as r3 from test t1 inner join test t2 on t1.r1=t2.r1 and t1.r2<t2.r2 group by t1.r2,r3 having count(*)=3) test ;
现在能通过了,可是结果不正确。设置为3的时候,针对上面的例子,返回结果应该是2,结果是1
+------+------+
| r1 | r2 |
+------+------+
| f1 | f2 |
| aaa | bbb |
| aaa | ccc |
| aaa | ddd |
| ccc | eee |
| ccc | fff |
| ddd | ggg |
| hhh | bbb |
| hhh | ccc |
| xxx | bbb |
| xxx | ccc |
| yyy | eee |
| yyy | fff |
| www | eee |
| www | fff |
+------+------+
15 rows in set (0.00 sec)mysql> SELECT COUNT(*) FROM (
-> SELECT t1.r2,t2.r2 AS r3 FROM aa t1 INNER JOIN aa t2 ON t1.r1=t2.r1 AND t
1.r2<t2.r2 GROUP BY t1.r2,r3 HAVING COUNT(*)=3) a;
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
1 row in set (0.02 sec)mysql>