时间戳

首页 > 解决方案 > sql中的组合列表

sql - sql中的组合列表

问题描述

id1 id2
一个 1
一个 2
乙 3
C 4

这是表格，我想要使用以下代码实现的组合列表

with combinationTable as (
 select DENSE_RANK() over (Order By id1) as rn ,id2, id1 from test
)
select c1.id2,c2.id2,c3.id2
from combinationTable c1, combinationTable c2, combinationTable c3
where
 c1.rn < c2.rn and
 c2.rn < c3.rn
order by c1.rn,c2.rn,c3.rn

但是我需要优化代码而不是硬编码，如果有 4 或 5 个不同的 id1，那么应该有 4 或 5 列 id2 和 4 或 5 次组合表的交叉连接。我们如何以优化的方式根据 id1 列的不同计数选择列和交叉连接

标签： sql

解决方案

要使用动态列重新创建查询，我可以考虑使用Prepared Statement的方法：

SET @cols = NULL;
SET @tables = NULL;
SET @conditions = NULL;
SET @sql = NULL;

SET @cols := (WITH RECURSIVE wo AS (
SELECT 1 idx, COUNT(DISTINCT id1) mxct FROM mytable 
UNION ALL
SELECT idx+1, mxct FROM wo WHERE idx+1 <= mxct)
SELECT GROUP_CONCAT(CONCAT('c',idx,'.id2')) FROM wo);

SET @tables := (WITH RECURSIVE wo AS (
SELECT 1 idx, COUNT(DISTINCT id1) mxct FROM mytable 
UNION ALL
SELECT idx+1, mxct FROM wo WHERE idx+1 <= mxct)
SELECT GROUP_CONCAT(CONCAT('combinationTable c',idx) SEPARATOR ' CROSS JOIN ') FROM wo);

SET @conditions := (SELECT GROUP_CONCAT(conds SEPARATOR ' AND ')
FROM
(WITH RECURSIVE wo AS (
SELECT 1 idx, COUNT(DISTINCT id1) mxct FROM mytable 
UNION ALL
SELECT idx+1, mxct FROM wo WHERE idx+1 <= mxct)
SELECT CONCAT('c',idx,'.rn < c',LAG(idx) OVER (ORDER BY idx DESC),'.rn') conds
FROM wo 
ORDER BY idx) A
WHERE conds IS NOT NULL);

SET @sql := (
CONCAT('WITH combinationTable AS (
 SELECT DENSE_RANK() OVER (ORDER BY id1) AS rn ,id2, id1 FROM mytable
)
SELECT ',@cols,'
FROM ',@tables,'
WHERE ',@conditions,';'));

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

最终查询将类似于您上面的查询，但临时表和列由当前表中的总行动态生成。所有这些都设置为自己的变量。您可以在演示小提琴中看到更多变量值

推荐阅读