有一个表:
id key type
123 11111 first
456 11111 second
456 11111 second
789 22222 second
890 22222 second
012 22222 first 我想要对该表进行信息的统计:
1)根据id要去重;
2)统计key的次数;
3)统计每个key对应的type的次数示例结果:
key times first_times second_times
11111 2 1 1
22222 3 1 2求教高手!mysqlgroup
id key type
123 11111 first
456 11111 second
456 11111 second
789 22222 second
890 22222 second
012 22222 first 我想要对该表进行信息的统计:
1)根据id要去重;
2)统计key的次数;
3)统计每个key对应的type的次数示例结果:
key times first_times second_times
11111 2 1 1
22222 3 1 2求教高手!mysqlgroup
sum(if(type='second',1,0)),
from (
select distinct id,key,type from tt) a
group by key
1)根据id要去重;
而你这里的 select distinct id,key,type from tt
会是按 id,key,type 去重了
用 create table tt(
id int,
`key` int,
type varchar(10)
);
insert into tt values
(123,11111, "first"),
(456,11111, "second"),
(456,11111, "second" ),
(789,22222, "second" ),
(890,22222, "second" ),
(456,22222, "first" );
做下测试就知道了。