mssql 难题求助

[smilyvm]

 

CREATE TABLE #t
(
    keyid VARCHAR(50),
    pms VARCHAR(200),
    pcode VARCHAR(200)
);
INSERT INTO #t
     ( keyid, pms, pcode )
SELECT '566763009150685184', '查看,编辑', '01'
UNION ALL
SELECT '566763009150685184', '查看,编辑', '11'
UNION ALL
SELECT '566763378266214400', '查看,新增,修改,删除', '0100'
UNION ALL
SELECT '566763378266214400', '查看,新增,修改,删除', '1000';


--期望查询结果

-- '566763009150685184', '查看,编辑', '11'
-- '566763378266214400', '查看,新增,修改,删除', '1100'

 

[smilyvm]

@太空漫步11 当二进制的0和1取或运算

[太空漫步11]

请问01+11=11, 0100+1000=1100是怎么得出来的？

[Hello World,]

定义一个自定义函数来处理：

图片中用到两种方案，第二种的自定义函数如下：

CREATE FUNCTION BinOR
(
    @BinString1 VARCHAR(100),
    @BinString2 VARCHAR(100)
)
RETURNS VARCHAR(100)
AS
    BEGIN
        DECLARE @Result VARCHAR(100);
        DECLARE @len INT;
        IF LEN(@BinString1) < LEN(@BinString2)
            BEGIN
                SET @Result = SUBSTRING(@BinString2, 1, LEN(@BinString2) - LEN(@BinString1));
                SET @BinString2 = SUBSTRING(@BinString2, LEN(@BinString2) - LEN(@BinString1) + 1, 100);
            END;
        ELSE
            BEGIN
                SET @Result = SUBSTRING(@BinString1, 1, LEN(@BinString1) - LEN(@BinString2));
                SET @BinString1 = SUBSTRING(@BinString1, LEN(@BinString1) - LEN(@BinString2) + 1, 100);
            END;
        SET @len = LEN(@BinString1);
        DECLARE @index INT = 1;
        WHILE @index <= @len
            BEGIN
                IF SUBSTRING(@BinString1, @index, 1) > SUBSTRING(@BinString2, @index, 1)
                    SET @Result = @Result + SUBSTRING(@BinString1, @index, 1);
                ELSE
                    SET @Result = @Result + SUBSTRING(@BinString2, @index, 1);
                SET @index = @index + 1;
            END;

        RETURN @Result;

    END;
GO