mysql如何查询多列重复的数据个数?

期望输出
q1 count1 q2 count2
B 2 B 1
D 1 C 2

只会差寻一列,求教如何查询多列数据重复个数!

我的意见是数据库简单提取数据后,有业务端处理,写各种复杂的select查询影响效率

select distinct q1,t1.count1,tt.q2,tt.count2 ... from t
left join select q1,count1 count1 from t group by q1 t1 on t1.q1 = t.q1
full join select distinct q2,t2.count2 from t left join select q2,count1 count1 from t group by q2 t2 on t2.q1 = t.q1 tt on tt.q2 = t.q1 ...

感觉好粗鄙,等大神来。

select a.*,b.q2,b.q2Count,c.q3,c.q3Count from SELECT @n1:=@n1+1 as id,q1,count* AS q1Count FROM t1 GROUP BY q1 a
, SELECT @n2:=@n2+1 as id,q2,count* AS q2Count FROM t1 GROUP BY q2 b
, SELECT @n3:=@n3+1 as id,q3,count* AS q3Count FROM t1 GROUP BY q3 c
,select @n1:=0,@n2:=0,@n3:=0 i
where a.id=b.id and c.id=a.id

2 D 1 B 2 D 2
1 B 3 C 2 E 1

发表评论

电子邮件地址不会被公开。 必填项已用*标注