想要一個根據兩個數字展開的笛卡爾集，下面sql是最初想到的最笨的辦法，把每一個數字和第二個數字的數列求并集

比如2，6兩個數字的笛卡爾集

遍歷2遍，分別是

1，1

1，2

1，3

1，4

1，6

1，6

和

2，1

2，2

2，3

2，4

2，5

2，6

然后 union兩個集合

CREATE PROCEDURE lcx_demo17 (
    IN channeltype LONG,
    IN deviceid LONG,
    IN address INT,
    IN channel INT
)
BEGIN

    DECLARE i INT ;
    
    SET i = 1;

    SET @strsql = ' SELECT num as address, num as channel FROM t_number WHERE num <= 0 ';

    WHILE i <= address DO

        SET @strsql = CONCAT(@strsql, ' UNION All (SELECT ', i, ' as address, c.num as channel ');
        SET @strsql = CONCAT(@strsql, ' FROM ');
            SET @strsql = CONCAT(@strsql, ' ( ');
                SET @strsql = CONCAT(@strsql, ' SELECT n.num, a.channel FROM t_number n ');
                SET @strsql = CONCAT(@strsql, ' LEFT JOIN (');
                    SET @strsql = CONCAT(@strsql, ' SELECT channel FROM tip_s_analoginputinfo ');
                    SET @strsql = CONCAT(@strsql, ' WHERE channeltype = ', channeltype, ' AND address485 = ', i, ' AND Device_id = ', deviceid);
                SET @strsql = CONCAT(@strsql, ' ) a ON n.num = a.channel ');
                SET @strsql = CONCAT(@strsql, ' WHERE n.num <= ', channel);
            SET @strsql = CONCAT(@strsql, ' ) c');
        SET @strsql = CONCAT(@strsql, ' WHERE c.channel IS NULL) ');
--         SET @strsql = CONCAT(@strsql, ' ORDER BY c.num * 1) ');
    
        SET i = i + 1;

    END WHILE;

SET @strsql = CONCAT(@strsql, ' ORDER BY address, channel ');

--     select @strsql;

    PREPARE stmt FROM @strsql;
    EXECUTE stmt ;
    DEALLOCATE PREPARE stmt;

    
END;

其實有一個很簡單的方法，就是利用表連接查詢時，on 字段始終為 true 時，第一個集合的每一行都會與第二個集合的全部行關聯，產生笛卡爾集，見下面的sql

下面的兩種方式，一種帶子查詢，經過測試發現帶子查詢的 sql 效率相對低一些

SELECT a.num, b.num FROM t_number a 
LEFT JOIN t_number b ON 1=1
WHERE a.num <= 900 AND b.num <= 900
ORDER BY a.num, b.num

SELECT a.num, b.ch FROM t_number a 
LEFT JOIN
(SELECT num as ch FROM t_number WHERE num <= 16) b ON 1=1
WHERE a.num <= 4
ORDER BY a.num, b.ch

對比下面兩個sql，發現not in 的效率 高于合并兩個表后查差集，但這里有個前提，這個前提就是這次的測試中，not in 查出來的集合較小，如果not in 里select返回的集合較大，可能會有不同的效率對比結果

-- 0.002 （4，64）
-- 0.391 （400， 600）
SELECT c.address485, c.channel, COUNT(*) FROM 
(
SELECT a.num as address485, b.num as channel FROM t_number a 
LEFT JOIN t_number b ON 1=1
WHERE a.num <= 4 AND b.num <= 64 

UNION ALL

SELECT address485, channel FROM tip_s_analoginputinfo WHERE channeltype = 10224 AND Device_id = 5 
) c
GROUP BY c.address485,c.channel
HAVING COUNT(*) = 1
ORDER BY c.address485, c.channel

-- 0.002 （4，64）
-- 0.278 （400， 600）
SELECT a.num, b.num FROM t_number a 
LEFT JOIN t_number b ON 1=1
WHERE a.num <= 4 AND b.num <= 64 AND CONCAT(a.num,"-",b.num) NOT IN (
 SELECT CONCAT(address485,"-",channel) FROM tip_s_analoginputinfo WHERE channeltype = 10224 AND Device_id = 5)
ORDER BY a.num, b.num

總結

以上是生活随笔為你收集整理的笛卡尔集的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。