mysql - SQL 查询计算两个文本列中匹配的字符数

Question

我需要计算两个文本列中有多少个字符相等(相同大小，在同一个表中)。例如:

RowNum: Template:         Answers:
------- ---------         --------
   1    ABCDEABCDEABCDE   ABCDAABCDBABCDC
   2    EDAEDAEDAEDAEDA   EDBEDBEDBEDBEDB

SELECT SOME_COUNT_FUNCTION(模板、答案)应返回:

RowNum: Result:
------- -------
   1       12
   2       10

数据库是MySQL。

Answer 1

不完全是 MySQL，但这里有一些可以在 SQL Server 中运行的东西。也许它会翻译过来。

DROP TABLE IF EXISTS #tmp
CREATE TABLE #tmp (
    [RowNum] INT IDENTITY(1,1) PRIMARY KEY,
    [Template] NVARCHAR(20),
    [Answer] NVARCHAR(20),
    [Result] INT
)

INSERT INTO #tmp
VALUES ('ABCDEABCDEABCDE','ABCDAABCDBABCDC', NULL),
('EDAEDAEDAEDAEDA','EDBEDBEDBEDBEDB', NULL)
--SELECT * FROM #tmp

DECLARE @current_template NVARCHAR(50) -- Variable to hold the current template
,   @current_answer NVARCHAR(50) -- Variable to hold the current answer
,   @template_char CHAR(1) -- Char for template letter
,   @answer_char CHAR(1) -- Char for answer letter
,   @word_index INT -- Index (position) within each word
,   @match_counter INT -- Match counter for each word
,   @max_iter INT = (SELECT TOP 1 RowNum FROM #tmp ORDER BY RowNum DESC) -- Max iterations
,   @row_idx INT = (SELECT TOP 1 RowNum FROM #tmp) -- Minimum RowNum as initial row index value.

WHILE (@row_idx <= @max_iter)
    BEGIN
        SET @match_counter = 0 -- Reset match counter for each row
        SET @word_index = 1 -- Reset word index for each row
        SET @current_template = (SELECT [Template] FROM #tmp WHERE RowNum = @row_idx)
        SET @current_answer = (SELECT [Answer] FROM #tmp WHERE RowNum = @row_idx)
        WHILE (@word_index <= LEN(@current_template))
            BEGIN
                SET @template_char = SUBSTRING(@current_template, @word_index, 1)
                SET @answer_char = SUBSTRING(@current_answer, @word_index, 1)
                IF (@answer_char = @template_char)
                    BEGIN
                        SET @match_counter += 1
                    END
                SET @word_index += 1
            END

    UPDATE #tmp
    SET Result = @match_counter
    WHERE RowNum = @row_idx

    SET @row_idx += 1
    END

从临时表中获取值:

SELECT * FROM #tmp

输出:

RowNum  Template        Answer          Result
1       ABCDEABCDEABCDE ABCDAABCDBABCDC 12
2       EDAEDAEDAEDAEDA EDBEDBEDBEDBEDB 10