56,687
社区成员
发帖
与我相关
我的任务
分享
drop table if exists quchong;
create table quchong(
id int,
`phonenumber` varchar(20)
);
insert into quchong values(1,'13912345670');
insert into quchong values(2,'13912345671');
insert into quchong values(3,'13912345671');
insert into quchong values(4,'13912345671');
insert into quchong values(5,'13912345676');
insert into quchong values(6,'13912345676');
insert into quchong values(7,'13912345676');
insert into quchong values(8,'13912345678');
insert into quchong values(9,'13912345678');
insert into quchong values(10,'13912345679');
select phonenumber,count(id) as cnt
from quchong group by phonenumber;
/*
+-------------+-----+
| phonenumber | cnt |
+-------------+-----+
| 13912345670 | 1 |
| 13912345671 | 3 |
| 13912345676 | 3 |
| 13912345678 | 2 |
| 13912345679 | 1 |
+-------------+-----+
*/
select *, count(distinct phonenumber) from quchong group by id;
/*
+----+-------------+-----------------------------+
| id | phonenumber | count(distinct phonenumber) |
+----+-------------+-----------------------------+
| 1 | 13912345670 | 1 |
| 2 | 13912345671 | 1 |
| 3 | 13912345671 | 1 |
| 4 | 13912345671 | 1 |
| 5 | 13912345676 | 1 |
| 6 | 13912345676 | 1 |
| 7 | 13912345676 | 1 |
| 8 | 13912345678 | 1 |
| 9 | 13912345678 | 1 |
| 10 | 13912345679 | 1 |
+----+-------------+-----------------------------+
*/
再说了, 你要一次性显示 600 万数据做什么? 谁看得了?[/quote]语句是对的,但要的结果跟我想要的有点差别,我是想显示出最新的phonenumer对应的列,这个程序显示的是最老的phonenumber对应列,你那个程序确实显示不了其它列,工作需要,需要把这600万数据做些预处理[/quote]
还没搞明白? 你那个 SQL , 所有的 count 列都是1 , 根本出不来 2 的了。[/quote] 应该是count(distince phonenumber)这句话表达最早的那一句phonenumber对应的count,后面同一个phonenumber被distinct了,所以只显示count为1的
[/quote]
扯那么复杂做什么?
你直接看我的代码和结果, 按你的写法就全是 1 drop table if exists quchong;
create table quchong(
id int,
`phonenumber` varchar(20)
);
insert into quchong values(1,'13912345670');
insert into quchong values(2,'13912345671');
insert into quchong values(3,'13912345671');
insert into quchong values(4,'13912345671');
insert into quchong values(5,'13912345676');
insert into quchong values(6,'13912345676');
insert into quchong values(7,'13912345676');
insert into quchong values(8,'13912345678');
insert into quchong values(9,'13912345678');
insert into quchong values(10,'13912345679');
select phonenumber,count(id) as cnt
from quchong group by phonenumber;
/*
+-------------+-----+
| phonenumber | cnt |
+-------------+-----+
| 13912345670 | 1 |
| 13912345671 | 3 |
| 13912345676 | 3 |
| 13912345678 | 2 |
| 13912345679 | 1 |
+-------------+-----+
*/
select *, count(distinct phonenumber) from quchong group by id;
/*
+----+-------------+-----------------------------+
| id | phonenumber | count(distinct phonenumber) |
+----+-------------+-----------------------------+
| 1 | 13912345670 | 1 |
| 2 | 13912345671 | 1 |
| 3 | 13912345671 | 1 |
| 4 | 13912345671 | 1 |
| 5 | 13912345676 | 1 |
| 6 | 13912345676 | 1 |
| 7 | 13912345676 | 1 |
| 8 | 13912345678 | 1 |
| 9 | 13912345678 | 1 |
| 10 | 13912345679 | 1 |
+----+-------------+-----------------------------+
*/
再说了, 你要一次性显示 600 万数据做什么? 谁看得了?[/quote]语句是对的,但要的结果跟我想要的有点差别,我是想显示出最新的phonenumer对应的列,这个程序显示的是最老的phonenumber对应列,你那个程序确实显示不了其它列,工作需要,需要把这600万数据做些预处理[/quote]
还没搞明白? 你那个 SQL , 所有的 count 列都是1 , 根本出不来 2 的了。[/quote] 应该是count(distince phonenumber)这句话表达最早的那一句phonenumber对应的count,后面同一个phonenumber被distinct了,所以只显示count为1的
drop table if exists quchong;
create table quchong(
id int,
`phonenumber` varchar(20)
);
insert into quchong values(1,'13912345670');
insert into quchong values(2,'13912345671');
insert into quchong values(3,'13912345671');
insert into quchong values(4,'13912345671');
insert into quchong values(5,'13912345676');
insert into quchong values(6,'13912345676');
insert into quchong values(7,'13912345676');
insert into quchong values(8,'13912345678');
insert into quchong values(9,'13912345678');
insert into quchong values(10,'13912345679');
select phonenumber,count(id) as cnt
from quchong group by phonenumber;
/*
+-------------+-----+
| phonenumber | cnt |
+-------------+-----+
| 13912345670 | 1 |
| 13912345671 | 3 |
| 13912345676 | 3 |
| 13912345678 | 2 |
| 13912345679 | 1 |
+-------------+-----+
*/
select *, count(distinct phonenumber) from quchong group by id;
/*
+----+-------------+-----------------------------+
| id | phonenumber | count(distinct phonenumber) |
+----+-------------+-----------------------------+
| 1 | 13912345670 | 1 |
| 2 | 13912345671 | 1 |
| 3 | 13912345671 | 1 |
| 4 | 13912345671 | 1 |
| 5 | 13912345676 | 1 |
| 6 | 13912345676 | 1 |
| 7 | 13912345676 | 1 |
| 8 | 13912345678 | 1 |
| 9 | 13912345678 | 1 |
| 10 | 13912345679 | 1 |
+----+-------------+-----------------------------+
*/
再说了, 你要一次性显示 600 万数据做什么? 谁看得了?[/quote]语句是对的,但要的结果跟我想要的有点差别,我是想显示出最新的phonenumer对应的列,这个程序显示的是最老的phonenumber对应列,你那个程序确实显示不了其它列,工作需要,需要把这600万数据做些预处理[/quote]
还没搞明白? 你那个 SQL , 所有的 count 列都是1 , 根本出不来 2 的了。[/quote] 应该是count(distince phonenumber)这句话表达最早的那一句phonenumber对应的count,后面同一个phonenumber被distinct了,所以只显示count为1的
[/quote]
扯那么复杂做什么?
你直接看我的代码和结果, 按你的写法就全是 1 [/quote]嗯,如何让id也能够显示出来呢,另外想问下我想最新的phonenumber对应的tdid,怎么解决呢drop table if exists quchong;
create table quchong(
id int,
`phonenumber` varchar(20)
);
insert into quchong values(1,'13912345670');
insert into quchong values(2,'13912345671');
insert into quchong values(3,'13912345671');
insert into quchong values(4,'13912345671');
insert into quchong values(5,'13912345676');
insert into quchong values(6,'13912345676');
insert into quchong values(7,'13912345676');
insert into quchong values(8,'13912345678');
insert into quchong values(9,'13912345678');
insert into quchong values(10,'13912345679');
select phonenumber,count(id) as cnt
from quchong group by phonenumber;
/*
+-------------+-----+
| phonenumber | cnt |
+-------------+-----+
| 13912345670 | 1 |
| 13912345671 | 3 |
| 13912345676 | 3 |
| 13912345678 | 2 |
| 13912345679 | 1 |
+-------------+-----+
*/
select *, count(distinct phonenumber) from quchong group by id;
/*
+----+-------------+-----------------------------+
| id | phonenumber | count(distinct phonenumber) |
+----+-------------+-----------------------------+
| 1 | 13912345670 | 1 |
| 2 | 13912345671 | 1 |
| 3 | 13912345671 | 1 |
| 4 | 13912345671 | 1 |
| 5 | 13912345676 | 1 |
| 6 | 13912345676 | 1 |
| 7 | 13912345676 | 1 |
| 8 | 13912345678 | 1 |
| 9 | 13912345678 | 1 |
| 10 | 13912345679 | 1 |
+----+-------------+-----------------------------+
*/
再说了, 你要一次性显示 600 万数据做什么? 谁看得了?[/quote]语句是对的,但要的结果跟我想要的有点差别,我是想显示出最新的phonenumer对应的列,这个程序显示的是最老的phonenumber对应列,你那个程序确实显示不了其它列,工作需要,需要把这600万数据做些预处理[/quote]
还没搞明白? 你那个 SQL , 所有的 count 列都是1 , 根本出不来 2 的了。drop table if exists quchong;
create table quchong(
id int,
`phonenumber` varchar(20)
);
insert into quchong values(1,'13912345670');
insert into quchong values(2,'13912345671');
insert into quchong values(3,'13912345671');
insert into quchong values(4,'13912345671');
insert into quchong values(5,'13912345676');
insert into quchong values(6,'13912345676');
insert into quchong values(7,'13912345676');
insert into quchong values(8,'13912345678');
insert into quchong values(9,'13912345678');
insert into quchong values(10,'13912345679');
select phonenumber,count(id) as cnt
from quchong group by phonenumber;
/*
+-------------+-----+
| phonenumber | cnt |
+-------------+-----+
| 13912345670 | 1 |
| 13912345671 | 3 |
| 13912345676 | 3 |
| 13912345678 | 2 |
| 13912345679 | 1 |
+-------------+-----+
*/
select *, count(distinct phonenumber) from quchong group by id;
/*
+----+-------------+-----------------------------+
| id | phonenumber | count(distinct phonenumber) |
+----+-------------+-----------------------------+
| 1 | 13912345670 | 1 |
| 2 | 13912345671 | 1 |
| 3 | 13912345671 | 1 |
| 4 | 13912345671 | 1 |
| 5 | 13912345676 | 1 |
| 6 | 13912345676 | 1 |
| 7 | 13912345676 | 1 |
| 8 | 13912345678 | 1 |
| 9 | 13912345678 | 1 |
| 10 | 13912345679 | 1 |
+----+-------------+-----------------------------+
*/
drop table if exists quchong;
create table quchong(
id int,
`phonenumber` varchar(20)
);
insert into quchong values(1,'13912345670');
insert into quchong values(2,'13912345671');
insert into quchong values(3,'13912345671');
insert into quchong values(4,'13912345671');
insert into quchong values(5,'13912345676');
insert into quchong values(6,'13912345676');
insert into quchong values(7,'13912345676');
insert into quchong values(8,'13912345678');
insert into quchong values(9,'13912345678');
insert into quchong values(10,'13912345679');
select phonenumber,count(id) as cnt
from quchong group by phonenumber;
/*
+-------------+-----+
| phonenumber | cnt |
+-------------+-----+
| 13912345670 | 1 |
| 13912345671 | 3 |
| 13912345676 | 3 |
| 13912345678 | 2 |
| 13912345679 | 1 |
+-------------+-----+
*/
select *, count(distinct phonenumber) from quchong group by id;
/*
+----+-------------+-----------------------------+
| id | phonenumber | count(distinct phonenumber) |
+----+-------------+-----------------------------+
| 1 | 13912345670 | 1 |
| 2 | 13912345671 | 1 |
| 3 | 13912345671 | 1 |
| 4 | 13912345671 | 1 |
| 5 | 13912345676 | 1 |
| 6 | 13912345676 | 1 |
| 7 | 13912345676 | 1 |
| 8 | 13912345678 | 1 |
| 9 | 13912345678 | 1 |
| 10 | 13912345679 | 1 |
+----+-------------+-----------------------------+
*/
再说了, 你要一次性显示 600 万数据做什么? 谁看得了?drop table if exists quchong;
create table quchong(
id int,
`phonenumber` varchar(20)
);
insert into quchong values(1,'13912345670');
insert into quchong values(2,'13912345671');
insert into quchong values(3,'13912345671');
insert into quchong values(4,'13912345671');
insert into quchong values(5,'13912345676');
insert into quchong values(6,'13912345676');
insert into quchong values(7,'13912345676');
insert into quchong values(8,'13912345678');
insert into quchong values(9,'13912345678');
insert into quchong values(10,'13912345679');
select phonenumber,count(1) as cnt
from quchong group by phonenumber;
/*
+-------------+-----+
| phonenumber | cnt |
+-------------+-----+
| 13912345670 | 1 |
| 13912345671 | 3 |
| 13912345676 | 3 |
| 13912345678 | 2 |
| 13912345679 | 1 |
+-------------+-----+
*/
[/quote]问题是还有其它列呀,需要跟phonenumber一起展示出来的,要下面最新生成的同一个phonenumber对应的列。你这个只能显示phonenumber,显示不了其它列呀drop table if exists quchong;
create table quchong(
id int,
`phonenumber` varchar(20)
);
insert into quchong values(1,'13912345670');
insert into quchong values(2,'13912345671');
insert into quchong values(3,'13912345671');
insert into quchong values(4,'13912345671');
insert into quchong values(5,'13912345676');
insert into quchong values(6,'13912345676');
insert into quchong values(7,'13912345676');
insert into quchong values(8,'13912345678');
insert into quchong values(9,'13912345678');
insert into quchong values(10,'13912345679');
select phonenumber,count(1) as cnt
from quchong group by phonenumber;
/*
+-------------+-----+
| phonenumber | cnt |
+-------------+-----+
| 13912345670 | 1 |
| 13912345671 | 3 |
| 13912345676 | 3 |
| 13912345678 | 2 |
| 13912345679 | 1 |
+-------------+-----+
*/