我有个mysql的表,表定义如下:
create table a (
r1 TEXT,
r2 TEXT );
现在想统计如下信息:
在r2列对应的r1值相同的成对列值为1,2,3,...,的分别有多少个?举例说明:
假设表a中存储有如下记录:
r1 r2
aaa bbb
aaa ccc
aaa ddd
ccc eee
ccc fff
ddd ggg
hhh bbb
hhh ccc
xxx bbb
xxx ccc那么在该表中,可获得如下信息
对r1列值相同的成对r2列值 对应的r1列值
<bbb, ccc> aaa、hhh、ccc
<bbb, ddd> aaa
<ccc, ddd> aaa
<eee, fff> ccc
那么在r2列对应的r1值相同的成对列值为3的有1对,为1的有3对。
create table a (
r1 TEXT,
r2 TEXT );
现在想统计如下信息:
在r2列对应的r1值相同的成对列值为1,2,3,...,的分别有多少个?举例说明:
假设表a中存储有如下记录:
r1 r2
aaa bbb
aaa ccc
aaa ddd
ccc eee
ccc fff
ddd ggg
hhh bbb
hhh ccc
xxx bbb
xxx ccc那么在该表中,可获得如下信息
对r1列值相同的成对r2列值 对应的r1列值
<bbb, ccc> aaa、hhh、ccc
<bbb, ddd> aaa
<ccc, ddd> aaa
<eee, fff> ccc
那么在r2列对应的r1值相同的成对列值为3的有1对,为1的有3对。
+------+------+
| r1 | r2 |
+------+------+
| aaa | bbb |
| aaa | ccc |
| aaa | ddd |
| ccc | eee |
| ccc | fff |
| ddd | ggg |
| hhh | bbb |
| hhh | ccc |
| xxx | bbb |
| xxx | ccc |
+------+------+
10 rows in set (0.05 sec)mysql>mysql> select u.r2,v.r2,group_concat(u.r1)
-> from a u,a v
-> where u.r1=v.r1 and u.r2<v.r2
-> group by u.r2,v.r2;
+------+------+--------------------+
| r2 | r2 | group_concat(u.r1) |
+------+------+--------------------+
| bbb | ccc | aaa,hhh,xxx |
| bbb | ddd | aaa |
| ccc | ddd | aaa |
| eee | fff | ccc |
+------+------+--------------------+
4 rows in set (0.03 sec)mysql>
-> from a u,a v
-> where u.r1=v.r1 and u.r2<v.r2
-> group by concat(u.r2,',',v.r2);
-> from a u,a v
-> where u.r1=v.r1 and u.r2<v.r2
-> group by u.r2,v.r2;
+------+------+----------+
| r2 | r2 | count(*) |
+------+------+----------+
| bbb | ccc | 3 |
| bbb | ddd | 1 |
| ccc | ddd | 1 |
| eee | fff | 1 |
+------+------+----------+
4 rows in set (1.16 sec)mysql>
因为我想获得的是:在r2列对应的r1值相同的成对列值为1,2,3,...,的分别有多少个?比如上面的例子,在r2列对应的r1值相同的成对列值为3的有1对,为1的有3对。
现在的答案是这样显示出来了.能不能直接执行结果就是:
我在sql命令里面设置成对列值为1,那么查询结果返回3
在sql命令里面设置成对列值为3,那么查询结果返回1
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) a having count(*)=3;
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) a having count(*)=1;
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 ;