我正试图解决WHERE子查询或找到不同的解决方案。我想要实现的是基于这个查询:
SELECT c.orig_point_id,
(SELECT attempts
FROM
(SELECT
orig_carrier_id,
orig_point_id,
term_point_id,
term_route,
currency_sell,
is_special,
COUNT(*) AS attempts
FROM cdr
WHERE 1=1
AND start_time >= '2016-10-01 0:00:00'
AND start_time <= '2016-10-31 23:59:59'
GROUP BY orig_carrier_id, currency_sell) AS c0
WHERE c0.orig_carrier_id=3
AND c0.currency_sell="USD"
LIMIT 1) AS attempts,
(SELECT SPLIT(clear_number) as array
FROM
(SELECT
COUNT(*) as total,
clear_number,
orig_carrier_id,
currency_sell
FROM `cdr`
WHERE `start_time`>='2016-10-01 00:00:00'
AND start_time <= '2016-10-31 23:59:59'
GROUP BY `clear_number`
ORDER BY total DESC) AS c0
WHERE c0.orig_carrier_id=3
AND c0.currency_sell="USD"
LIMIT 1) AS splitted_number
FROM cdr AS c
GROUP BY c.orig_carrier_id, c.currency_sell;
SPLIT是函数。该部分中的查询找到一个数字(最常见),并将其拆分为例12345,1234,123,12,1。问题来了,当我试图使用它作为子查询。当直接使用时,mysql表示不支持功能。看起来查询太复杂了。当我别名子查询作为一个解决方案,它返回NULL,所以解决方案不工作,我相信它返回NULL,因为它不可行的原因相同。
SELECT
CONCAT_WS(" - ",country,region) AS route_name
FROM numbering_plan_external
WHERE
prefix IN(
SELECT array
FROM
(SELECT SPLIT(clear_number) as array
FROM
(SELECT
COUNT(*) as total,
clear_number,
orig_carrier_id,
currency_sell
FROM `cdr`
WHERE `start_time`>='2016-10-01 00:00:00'
AND start_time <= '2016-10-31 23:59:59'
GROUP BY `clear_number`
ORDER BY total DESC) AS c0
WHERE c0.orig_carrier_id=3
AND c0.currency_sell="USD"
LIMIT 1) AS splitted_number)
ORDER BY prefix DESC LIMIT 1) AS top_route
我在这里做错了什么,或者有不同的方法来实现这一点。我可以只留下split number,然后通过PHP找到路由。这将需要根据结果进行大量查询,如果可能的话,我将尽量避免它。
提前感谢各位。
一些样本数据
CREATE TABLE IF NOT EXISTS `numbering_plan_external` (
`id` int(11) NOT NULL,
`country` varchar(255) NOT NULL,
`region` varchar(255) DEFAULT NULL,
`prefix` varchar(50) NOT NULL,
`is_mobile` tinyint(1) NOT NULL DEFAULT '0',
`last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`min_sale_price_currency` char(3) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`min_sale_price_amount` decimal(10,4) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=14004 DEFAULT CHARSET=latin1;
INSERT INTO `numbering_plan_external`
(`id`, `country`, `region`,`prefix`, `is_mobile`, `last_updated`, `min_sale_price_currency`, `min_sale_price_amount`)
VALUES
(13047, 'Tunisia', '', '216', 0, '2016-02-17 12:30:44', NULL, NULL),
(13048, 'Tunisia', 'Mobile (ORANGE)', '2165', 1, '2016-02-17 12:30:44', NULL, NULL),
(13049, 'Tunisia', 'Mobile (ORASCOM)', '2162', 1, '2016-02-17 12:30:44', NULL, NULL),
(13050, 'Tunisia', 'Mobile (TUNTEL)', '21640', 1, '2016-02-17 12:30:44', NULL, NULL),
(13051, 'Tunisia', 'Mobile (TUNTEL)', '21641', 1, '2016-02-17 12:30:44', NULL, NULL),
(13052, 'Tunisia', 'Mobile (TUNTEL)', '2169', 1, '2016-02-17 12:30:44', NULL, NULL);
CREATE TABLE IF NOT EXISTS `cdr` (
`id` int(11) NOT NULL,
`orig_carrier_id` int(11) NOT NULL,
`orig_point_id` int(11) NOT NULL,
`term_carrier_id` int(11) NOT NULL,
`term_point_id` int(11) NOT NULL,
`clear_number` varchar(100) COLLATE latin1_general_ci NOT NULL,
`is_special` tinyint(1) NOT NULL DEFAULT '0',
`start_time` datetime NOT NULL,
`currency_sell` char(3) COLLATE latin1_general_ci NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=16385 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
INSERT INTO `cdr`
(`id`, `orig_carrier_id`, `orig_point_id`, `term_carrier_id`, `term_point_id`, `clear_number`, `is_special`, `start_time`, `currency_sell`) VALUES
(1, 3, 5, 0, 0, '21658502507', 0, '2016-10-17 00:02:04', 'USD'),
(2, 3, 5, 0, 0, '21658502507', 0, '2016-10-17 00:02:04', 'USD'),
(3, 3, 5, 0, 0, '21658502507', 0, '2016-10-17 00:03:56', 'USD'),
(4, 3, 5, 0, 0, '21658502507', 0, '2016-10-17 00:09:28', 'USD'),
(5, 3, 5, 0, 0, '21658502507', 0, '2016-10-17 00:16:35', 'USD');
IN
将值视为一个整体。无论你的SPLIT()
在做什么,即使它返回一个"csv",整个列表被认为是一个单一的值,例如,它将被解析/执行为等效的
WHERE foo IN ('12345,1234,...')
WHERE foo='12345,1234,...'
而不是这些
WHERE foo IN ('12345', '1234', '123', ...)
WHERE foo='12345' OR foo='1234' OR ...
你可以尝试使用MySQL的find_in_set()代替,它基本上做你想要的。
使用示例数据执行以下查询:
SELECT
cdr.orig_point_id
, count(cdr.*) attempts
, group_concat(distinct npe.region) regions
FROM cdr
INNER JOIN numbering_plan_external npe
ON cdr.clear_number like concat(npe.prefix,'%') COLLATE latin1_general_ci
AND npe.region <> ''
WHERE cdr.orig_carrier_id=3
AND cdr.currency_sell='USD'
AND cdr.start_time >= '2016-10-01'
AND cdr.start_time < '2016-11-01'
GROUP BY
cdr.orig_point_id
;
结果:| orig_point_id | attempts | regions |
|---------------|----------|----------------|
| 5 | 5 | Mobile (ORANGE)|
这些表之间的连接涉及比较前缀和clear_number的起始字符。但是,由于存在排序规则冲突,因此需要指定所使用的排序规则。使用LIKE并不是最有效的连接条件,它可能会导致性能问题,因为它不使用索引。然而,它确实证明了逻辑连接确实存在,并且您不需要拆分函数(顺便说一下,这对连接也不好)。
我把刚才问题的其余部分留作参考:
查询:
SELECT
orig_point_id
, count(*) attempts
FROM cdr
WHERE orig_carrier_id=3
AND currency_sell='USD'
AND start_time >= '2016-10-01'
AND start_time < '2016-11-01'
GROUP BY
orig_point_id
结果:
| orig_point_id | attempts |
|---------------|----------|
| 5 | 5 |
:
SELECT c.orig_point_id,
(SELECT attempts
FROM
(SELECT
orig_carrier_id,
orig_point_id,
term_point_id,
/* term_route, */
currency_sell,
is_special,
COUNT(*) AS attempts
FROM cdr
WHERE 1=1
AND start_time >= '2016-10-01 0:00:00'
AND start_time <= '2016-10-31 23:59:59'
GROUP BY orig_carrier_id, currency_sell) AS c0
WHERE c0.orig_carrier_id=3
AND c0.currency_sell="USD"
LIMIT 1) AS attempts
FROM cdr AS c
GROUP BY c.orig_carrier_id, c.currency_sell
结果:
| orig_point_id | attempts |
|---------------|----------|
| 5 | 5 |
希望你可以看到,你不需要那么复杂的查询,因为你现在做的。我怀疑,如果我们对"预期结果"有更多的了解,我们可能能够在没有split函数的情况下做到这一点。