一道SQL算法

最新推荐文章于 2021-05-19 22:39:05 发布

xuming9

最新推荐文章于 2021-05-19 22:39:05 发布

阅读量121

点赞数

分类专栏： DataBase 文章标签：算法 SQL C C++ C#

DataBase 专栏收录该内容

6 篇文章 0 订阅

订阅专栏


/********************************
select * from A;             
ID       YS
-------------------------
01       A
02       AB
03       CAB
04       ABCD
select * from B;
DM       YS
-------------------------
A          白
B          黑
C          红
D          蓝

想得到结果：
-------------------------
01       白
02       白黑
03       红白黑
04       白黑红蓝
************************************/


CREATE TABLE A (ID  VARCHAR(2),YS  VARCHAR(5));

INSERT INTO A VALUES  ('01','A');
INSERT INTO A VALUES  ('02','AB');
INSERT INTO A VALUES  ('03','CAB');
INSERT INTO A VALUES  ('04','ABCD');

CREATE TABLE B (DM VARCHAR(1),YS  VARCHAR(2));
INSERT INTO B VALUES ('A','白');
INSERT INTO B VALUES ('B','黑');
INSERT INTO B VALUES ('C','红');
INSERT INTO B VALUES ('D','蓝');

select * from a

select * from b

---使用游标
DECLARE @ID  VARCHAR(2)
DECLARE @YS  VARCHAR(5)
DECLARE @i INT
DECLARE @B_TMP VARCHAR(5)
DECLARE @A_COLOR VARCHAR(15)
DECLARE myCursor CURSOR

FOR 
    SELECT * FROM A
OPEN myCursor
FETCH NEXT FROM myCursor INTO @ID,@YS

WHILE (@@FETCH_STATUS <> -1)
BEGIN
    SET @i=1
    SET @B_TMP=''
    SET @A_COLOR=''
    WHILE @i<=LEN(@YS)
    BEGIN
        SELECT @B_TMP=YS FROM B WHERE DM=SUBSTRING(@YS,@i,1)
        SET @A_COLOR=@A_COLOR+@B_TMP
        SET @i=@i+1
    END
    PRINT @ID+'     '+@A_COLOR
    FETCH NEXT FROM myCursor INTO @ID,@YS
END

CLOSE myCursor
DEALLOCATE myCursor