suchen

Heim  >  Fragen und Antworten  >  Hauptteil

sqlserver - mysql如何查询多列重复的数据个数？

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

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

高洛峰高洛峰2786 Tage vor670

Antworte allen(3)Ich werde antworten

  • 怪我咯

    怪我咯2017-04-17 15:59:43

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

    Antwort
    0
  • PHP中文网

    PHP中文网2017-04-17 15:59:43

    select distinct q1,t1.count1,tt.q2,tt.count2 ... from t
left join (select q1,count(1) 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,count(1) count1 from t group by q2) t2 on t2.q1 = t.q1
) tt on tt.q2 = t.q1 ...

    感觉好粗鄙，等大神来。

    Antwort
    0
  • ringa_lee

    ringa_lee2017-04-17 15:59:43

    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

    Antwort
    0
  • StornierenAntwort