56,677
社区成员
发帖
与我相关
我的任务
分享
127.0.0.1~root@localhost~test>select * from (select mobile_no,group_concat(service_id) as service_id
-> from data_sms_tmp
-> group by mobile_no) a where FIND_IN_SET(1,service_id)>0 and FIND_IN_SET(4,service_id)>0 ;
+-------------+------------+
| mobile_no | service_id |
+-------------+------------+
| 18601121001 | 1,2,3,4 |
| 18601121003 | 4,3,2,1 |
| 18601121004 | 1,4 |
| 18601121006 | 1,2,4 |
| 18601121007 | 4,2,1 |
| 18601121010 | 1,2,3,4 |
| 18601121014 | 1,2,4 |
| 18601121015 | 4,1 |
+-------------+------------+
8 rows in set (0.00 sec)
127.0.0.1~root@localhost~test>
127.0.0.1~root@localhost~test>select * from (select mobile_no,group_concat(service_id) as service_id
-> from data_sms_tmp
-> group by mobile_no) a where FIND_IN_SET(2,service_id)>0 and FIND_IN_SET(4,service_id)>0
-> and FIND_IN_SET(1,service_id)=0;
+-------------+------------+
| mobile_no | service_id |
+-------------+------------+
| 18601121008 | 2,3,4 |
| 18601121009 | 4,2 |
| 18601121017 | 2,4 |
+-------------+------------+
3 rows in set (0.00 sec)
mysql> select distinct a.mobile_no
-> from data_sms_tmp a,data_sms_tmp b
-> where a.mobile_no=b.mobile_no
-> and a.service_id=2
-> and b.service_id=4
-> and not exists (
-> select 1
-> from data_sms_tmp
-> where mobile_no=a.mobile_no and service_id=1
-> );
+-------------+
| mobile_no |
+-------------+
| 18601121008 |
| 18601121009 |
| 18601121017 |
+-------------+
3 rows in set (0.00 sec)
mysql>
mysql> select distinct a.mobile_no
-> from data_sms_tmp a,data_sms_tmp b
-> where a.mobile_no=b.mobile_no
-> and a.service_id=1
-> and b.service_id=4;
+-------------+
| mobile_no |
+-------------+
| 18601121001 |
| 18601121003 |
| 18601121004 |
| 18601121006 |
| 18601121007 |
| 18601121010 |
| 18601121014 |
| 18601121015 |
+-------------+
8 rows in set (0.11 sec)
mysql>
CREATE TABLE `data_sms_tmp` (
`mobile_no` varchar(20) NOT NULL default 'NA',
`service_id` varchar(30) NOT NULL,
`prov` varchar(20) NOT NULL,
`city` varchar(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121001', '1', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121001', '2', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121001', '3', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121001', '4', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121002', '3', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121002', '3', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121003', '1', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121003', '2', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121003', '3', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121003', '4', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121004', '1', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121004', '4', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121005', '1', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121005', '2', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121005', '3', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121006', '1', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121006', '2', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121006', '4', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121007', '1', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121007', '2', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121007', '4', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121008', '2', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121008', '3', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121008', '4', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121009', '2', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121009', '4', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121010', '1', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121010', '2', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121010', '3', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121010', '4', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121011', '2', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121011', '3', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121012', '1', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121013', '2', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121014', '1', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121014', '2', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121014', '4', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121015', '1', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121015', '4', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121016', '1', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121016', '3', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121017', '2', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121017', '4', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121018', '2', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121018', '3', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121019', '1', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121019', '2', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121020', '1', '北京', '北京');
INSERT INTO `data_sms_tmp` (`mobile_no`, `service_id`, `prov`, `city`) VALUES ('18601121020', '3', '北京', '北京');