id 不相同, 以 id group 得到的结果有何意义?
直接返回有多少号码就得了:
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 |
+-------------+-----+
*/
直接返回有多少号码就得了:
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 |
+-------------+-----+
*/
直接返回有多少号码就得了:
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 |
+-------------+-----+
*/问题是还有其它列呀,需要跟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(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(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 万数据做什么? 谁看得了?语句是对的,但要的结果跟我想要的有点差别,我是想显示出最新的phonenumer对应的列,这个程序显示的是最老的phonenumber对应列,你那个程序确实显示不了其它列,工作需要,需要把这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(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 万数据做什么? 谁看得了?语句是对的,但要的结果跟我想要的有点差别,我是想显示出最新的phonenumer对应的列,这个程序显示的是最老的phonenumber对应列,你那个程序确实显示不了其它列,工作需要,需要把这600万数据做些预处理
还没搞明白? 你那个 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 |
+----+-------------+-----------------------------+
*/
再说了, 你要一次性显示 600 万数据做什么? 谁看得了?语句是对的,但要的结果跟我想要的有点差别,我是想显示出最新的phonenumer对应的列,这个程序显示的是最老的phonenumber对应列,你那个程序确实显示不了其它列,工作需要,需要把这600万数据做些预处理
还没搞明白? 你那个 SQL , 所有的 count 列都是1 , 根本出不来 2 的了。 应该是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 万数据做什么? 谁看得了?语句是对的,但要的结果跟我想要的有点差别,我是想显示出最新的phonenumer对应的列,这个程序显示的是最老的phonenumber对应列,你那个程序确实显示不了其它列,工作需要,需要把这600万数据做些预处理
还没搞明白? 你那个 SQL , 所有的 count 列都是1 , 根本出不来 2 的了。 应该是count(distince phonenumber)这句话表达最早的那一句phonenumber对应的count,后面同一个phonenumber被distinct了,所以只显示count为1的
扯那么复杂做什么?
你直接看我的代码和结果, 按你的写法就全是 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 万数据做什么? 谁看得了?语句是对的,但要的结果跟我想要的有点差别,我是想显示出最新的phonenumer对应的列,这个程序显示的是最老的phonenumber对应列,你那个程序确实显示不了其它列,工作需要,需要把这600万数据做些预处理
还没搞明白? 你那个 SQL , 所有的 count 列都是1 , 根本出不来 2 的了。 应该是count(distince phonenumber)这句话表达最早的那一句phonenumber对应的count,后面同一个phonenumber被distinct了,所以只显示count为1的
扯那么复杂做什么?
你直接看我的代码和结果, 按你的写法就全是 1 嗯,如何让id也能够显示出来呢,另外想问下我想最新的phonenumber对应的tdid,怎么解决呢
你象我那样(见 #3), 把表结构的脚本贴出来, 另加至少 10 条测试数据。
然后把自己想要的结果也贴出来, 这样方便人家理解你的意思。6是测试数据,想要的结果为7那样的,需要最新手机号对应的tdid
你象我那样(见 #3), 把表结构的脚本贴出来, 另加至少 10 条测试数据。
然后把自己想要的结果也贴出来, 这样方便人家理解你的意思。6是测试数据,想要的结果为7那样的,需要最新手机号对应的tdid上图是测试数据,下图是所需要的结果
你象我那样(见 #3), 把表结构的脚本贴出来, 另加至少 10 条测试数据。
然后把自己想要的结果也贴出来, 这样方便人家理解你的意思。6是测试数据,想要的结果为7那样的,需要最新手机号对应的tdid上图是测试数据,下图是所需要的结果
你觉得很清楚, 人家觉得很麻烦, 你自己手打那么多的 uuid 你试下需要多长时间?
建表的 sql , 插入数据的 sql 自己弄一份, 而不是这样搞个图片了事。
h9370913f846f02020213896d4d7e129c 15302302604 1
ha68fabe1ec7d7b4d4ac67d4d86e3e054 15302302604 1
hebace26da292673a787dbcfe41aaa3f6 17620165550 2
hbb6e2df5bc71de21076e565f597f42d8 13533162048 3
hbb6e2df5bc71de21076e565f597f42d8 17620165550 2
3e71ba3c76614e8db50a4f64c79d9301b 13533162048 3
hebace26da292673a787dbcfe41aaa3f6 13602200026 4
3702a7a81afe39ce95526039f5cb5af40 15021778017 5
35d8fc6022289737cc7bd948e455f092d 15302302604 1
370b83e3c059eaf8b502d27502af12777 15818115022 6
302dc8898836c97feb496d4191bbc0999 15622068360 7
ha68fabe1ec7d7b4d4ac67d4d86e3e054 18826412647 8
h46ac207fd78de14cbff77dbf13fcb2ce 13226688562 9
hbdc374fd6e36d3a0ac7e93fe392a8e7b 13600056118 1035d8fc6022289737cc7bd948e455f092d 15302302604 1
hbb6e2df5bc71de21076e565f597f42d8 17620165550 2
3e71ba3c76614e8db50a4f64c79d9301b 13533162048 3
hebace26da292673a787dbcfe41aaa3f6 13602200026 4
3702a7a81afe39ce95526039f5cb5af40 15021778017 5
370b83e3c059eaf8b502d27502af12777 15818115022 6
302dc8898836c97feb496d4191bbc0999 15622068360 7
ha68fabe1ec7d7b4d4ac67d4d86e3e054 18826412647 8
h46ac207fd78de14cbff77dbf13fcb2ce 13226688562 9
hbdc374fd6e36d3a0ac7e93fe392a8e7b 13600056118 10