如何在連接子查詢中使用選擇中的值？

我試圖避免使用儲存過程或將其帶入程式碼中，但它們可能是我唯一的選擇。這是我的sql。有誰知道任何可以在沒有儲存過程的情況下實現這一點的 SQL 巫術？

SELECT first_name,
      last_name,
      ip_address,
      ip_information.isp,
      count(*) duplicate_records
FROM   customers
LEFT OUTER JOIN (select * from ip_info.ip_address_data
                    where ip_to > INET_ATON(ip_address) limit 1
               ) ip_information using(ip_address)
GROUP BY 1,2,3,4
ORDER BY 1,2,3,4;

我試圖從具有 ip 地址範圍而不是確切 ips 的表中獲取數據是 LEFT OUTER JOIN。

CREATE TABLE `customers` (
 `customer_log_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `customer_id` int(11) DEFAULT NULL,
 `first_name` varchar(255) DEFAULT NULL,
 `last_name` varchar(255) DEFAULT NULL,
 `ip_address` varchar(20) DEFAULT NULL,
 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`customer_log_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

CREATE TABLE `ip_address_data` (
   `ip_from` int(10) unsigned NOT NULL,
   `ip_to` int(10) unsigned NOT NULL DEFAULT '0',
   `country_code` char(2) DEFAULT NULL,
   `country_name` varchar(64) DEFAULT NULL,
   `region_name` varchar(128) DEFAULT NULL,
   `city_name` varchar(128) DEFAULT NULL,
   `latitude` double DEFAULT NULL,
   `longitude` double DEFAULT NULL,
   `zip_code` varchar(30) DEFAULT NULL,
   `time_zone` varchar(8) DEFAULT NULL,
   `isp` varchar(256) DEFAULT NULL,
   `domain` varchar(128) DEFAULT NULL,
   `net_speed` varchar(8) DEFAULT NULL,
   `idd_code` varchar(5) DEFAULT NULL,
   `area_code` varchar(30) DEFAULT NULL,
   PRIMARY KEY (`ip_to`),
   KEY `isp` (`isp`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

用文字解釋，我正在嘗試從我們的客戶那裡選擇所有記錄並確定他們來自哪個 ISP。主表是customers，isp數據在ip_info.ip_address_data表中。客戶表中的數據具有客戶上次用於登錄系統的 IP 地址。另一個表沒有列表中的每個 ip 地址，而是使用 ip_to 和 ip_from 列的 ips 範圍。通常有人會做這樣的查詢來獲取數據：

SELECT * FROM ip_info.ip_address_data
   WHERE ip_to   > INET_ATON('1.1.1.1')
     and ip_from < INET_ATON('1.1.1.1') 

但是由於我將 ip_to 作為主鍵，因此只需執行 ip_to > INET_ATON(‘1.1.1.1’) 並使用 limit 會更快，因此在找到第一行後它會停止返回記錄，而不必確保它是 < ip_from

在表中加入範圍肯定會使查詢更具挑戰性。

INSERT INTO `customers` (`customer_log_id`, `customer_id`, `first_name`, `last_name`, `ip_address`, `created_at`)
VALUES
(1, 1, 'John', 'Smith', '1.0.0.1', '2016-03-11 11:59:47'),
   (2, 1, 'John', 'Smith', '1.0.0.2', '2016-03-11 11:59:52'),
   (3, 1, 'John', 'Smith', '1.0.0.3', '2016-03-11 11:59:53'),
   (4, 1, 'John', 'Smith', '1.0.0.4', '2016-03-11 11:59:55'),
   (5, 1, 'John', 'Smith', '1.0.0.1', '2016-03-11 11:59:48'),
   (6, 1, 'John', 'Smith', '1.0.0.1', '2016-03-11 11:59:49');


INSERT INTO `ip_address_data` (`ip_from`, `ip_to`, `country_code`, `country_name`, `region_name`, `city_name`, `latitude`, `longitude`, `zip_code`, `time_zone`, `isp`, `domain`, `net_speed`, `idd_code`, `area_code`, `weather_station_code`, `weather_station_name`, `mcc`, `mnc`, `mobile_brand`, `elevation`, `usage_type`)
VALUES
(0, 16777215, '-', '-', '-', '-', 0, 0, '-', '-', 'Broadcast RFC1700', '-', '-', '-', '-', '-', '-', '-', '-', '-', 0, 'RSV'),
   (16777216, 16777471, 'AU', 'Australia', 'Queensland', 'Brisbane', -27.46794, 153.02809, '4000', '+10:00', 'Research Prefix for APNIC Labs', 'apnic.net', 'T1', '61', '07', 'ASXX0016', 'Brisbane', '-', '-', '-', 39, 'DCH'),
   (16777472, 16778239, 'CN', 'China', 'Fujian', 'Fuzhou', 26.06139, 119.30611, '350004', '+08:00', 'ChinaNet Fujian Province Network', 'chinatelecom.com.cn', 'DSL', '86', '0591', 'CHXX0031', 'Fuzhou', '460', '03', 'China Telecom', 12, 'ISP/MOB'),
   (16778240, 16778495, 'AU', 'Australia', 'Victoria', 'Melbourne', -37.814, 144.96332, '8010', '+11:00', 'Golden IT Pty Ltd', 'goldenit.net.au', 'DSL', '61', '03', 'ASXX0075', 'Melbourne', '-', '-', '-', 25, 'COM'),
   (16778496, 16779007, 'AU', 'Australia', 'New South Wales', 'Sydney', -33.86785, 151.20732, '2000', '+11:00', 'Golden IT Pty Ltd', 'goldenit.net.au', 'DSL', '61', '02', 'ASXX0112', 'Sydney', '-', '-', '-', 69, 'COM'),
   (16779008, 16779263, 'AU', 'Australia', 'Victoria', 'Melbourne', -37.814, 144.96332, '8010', '+11:00', 'Golden IT Pty Ltd', 'goldenit.net.au', 'DSL', '61', '03', 'ASXX0075', 'Melbourne', '-', '-', '-', 25, 'COM');

您可能有一個錯誤：它應該ip_to >=代替ip_to >– 來涵蓋ip_address與ip_to.

您必須確保 ; 中沒有重疊範圍ip_information。否則你偶爾會遇到奇怪的錯誤。

而且，正如李指出的，你確實需要

and ipd.ip_from <= INET_ATON(c1.ip_address)

否則，您可能會陷入困境。

但是，這些都沒有解決這個問題。FROM您需要一個“相關子查詢”，而/中的子查詢無法做到這一點JOIN。所以，讓我們做…

1. SELECT DISTINCT ip_address FROM customers ... 這是一種優化，因此我們不會重複查找相同的 ips。
2. 伸手ip_address_data去尋找一組ip_to值。
3. 現在回到兩個表以獲取其餘資訊。

像這樣的東西……（我省略了必要的 INET_ATON 呼叫——建議你讓你的數據保持一致。）

首先，步驟 1 和 2：

SELECT  c.ip_address, 
     ( SELECT  MIN(ip_to)
           FROM  ip_address_data
           WHERE  ip_to >= c.ip_address
           ORDER BY  ip_to
           LIMIT  1 
     ) AS ip_to
   FROM  
     ( SELECT  DISTINCT ip_address
           FROM  customers
           WHERE  ... 
     ) AS c;

看看這是否正確地傳遞了ip_address和對ip_to。是的，我們失去了客戶資訊，並且ip_from；第 3 步將解決這個問題。

SELECT  c2..., a2...
   FROM  ( as_above ) AS b
   JOIN  customers AS c2 USING(ip_address)
   JOIN  ip_address_data AS a2 USING(ip_to)
   WHERE a2.ip_from <= b.ip_address
   GROUP BY ...
   ORDER BY ...;

需要索引：

customers: INDEX(ip_address)

還有一件事…… IPv6 就在我們身邊；你的桌子不能處理這樣的。

引用自：https://dba.stackexchange.com/questions/132007