SQL多记录十进制转二进制：高效实现100行转400行数据需求

问题描述 #

现有SQL表数据：

PK_No   Model1  Model2
PK-01     3       4
PK-02     5       6

需将十进制数值转换为二进制逐位展开的格式，每行对应二进制的一位（原表每行生成4行结果），预期输出：

PK_No   Model1  Model2
PK-01     1       0
PK-01     1       0   
PK-01     0       0
PK-01     0       1
PK-02     0       0
PK-02     1       0
PK-02     0       1
PK-02     1       1

此前尝试CROSS APPLY、Substring及字符串拆分方法，性能不佳，需处理100行原数据生成400行结果，寻求高效实现方案。

高效解决方案 #

核心思路：数字辅助表+位运算 #

位运算属于原生数值操作，比字符串处理的CPU、内存开销低得多；结合极小的数字辅助表（仅4行）做笛卡尔积，能快速完成逐位展开。

1. 生成数字辅助集

可以创建永久数字表（复用性强），或用CTE临时生成：

-- 方式1：创建永久数字表（仅需执行一次）
CREATE TABLE Numbers (N INT PRIMARY KEY);
INSERT INTO Numbers (N) VALUES (0), (1), (2), (3);

-- 方式2：CTE临时生成（无需创建表）
WITH Numbers AS (
    SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
)

2. 主查询实现转换

以SQL Server为例，通过位运算&判断对应二进制位是否为1，结合数字表的N值控制位的顺序：

WITH Numbers AS (
    SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
)
SELECT 
    t.PK_No,
    -- 提取Model1对应二进制位：3-N对应从高位到低位的位位置
    CASE WHEN (t.Model1 & POWER(2, 3 - n.N)) > 0 THEN 1 ELSE 0 END AS Model1,
    CASE WHEN (t.Model2 & POWER(2, 3 - n.N)) > 0 THEN 1 ELSE 0 END AS Model2
FROM YourTable t
CROSS JOIN Numbers n
ORDER BY t.PK_No, n.N;

适配不同SQL方言 #

• MySQL：将POWER替换为POW：

WITH Numbers AS (
    SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
)
SELECT 
    t.PK_No,
    CASE WHEN (t.Model1 & POW(2, 3 - n.N)) > 0 THEN 1 ELSE 0 END AS Model1,
    CASE WHEN (t.Model2 & POW(2, 3 - n.N)) > 0 THEN 1 ELSE 0 END AS Model2
FROM YourTable t
CROSS JOIN Numbers n
ORDER BY t.PK_No, n.N;

• PostgreSQL：用^替代POWER做幂运算：

WITH Numbers AS (
    SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
)
SELECT 
    t.PK_No,
    CASE WHEN (t.Model1 & (2 ^ (3 - n.N))) > 0 THEN 1 ELSE 0 END AS Model1,
    CASE WHEN (t.Model2 & (2 ^ (3 - n.N))) > 0 THEN 1 ELSE 0 END AS Model2
FROM YourTable t
CROSS JOIN Numbers n
ORDER BY t.PK_No, n.N;

性能说明 #

该方案完全基于数值运算，避免了字符串拆分、截取的额外开销，处理100行原数据生成400行结果几乎瞬时完成，远优于字符串类实现。

内容的提问来源于stack exchange，提问作者pramod zirale