笛卡尔集

 

想要一个根据两个数字展开的笛卡尔集，下面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