SQL Server order desc and also show 6/6

Question

i在一个名为col1，col2，col3的表中有一个3列，其中包含整数值。有4000 行。有三个参数@first，@second，@third。我想获得根据此参数匹配的行。查询提供结果，但没有给我3/3，或者如果没有匹配，它没有显示出来，我也想使其下降。我的输出应该像这样

table :
col1  col2 col3
 3      4    5
 1      2    2
 7      7    9
 3      2    3
 1      8    9
 1      2    3
first = 1 second = 2 and third = 3 
    3 / 3    1 ( as sixth row contains 1, 2, 3)
    2 / 3    2  ( as second row and fourth row )
    1 / 3    1   (fifth row)
    0 / 3    2   (ist , third )
     (
       SELECT  count (CASE WHEN col1 = @First THEN 1 ELSE 0 END +
                  CASE WHEN col2= @second THEN 1 ELSE 0 END +
                    CASE WHEN col3 = @third THEN 1 ELSE 0 END
                    )AS "NUM_OF_MATCHES" ,
                CAST( CASE WHEN col1 = @First THEN 1 ELSE 0 END  +
                CASE WHEN col2= @sec/ond THEN 1 ELSE 0 END  +
                CASE WHEN col3 = @third THEN 1 ELSE 0 END
                 AS VARCHAR(10)) + '/ 3'
            AS "match"
    FrOM dbo.FormsDataRowFormat a
    where ( SELECT  CASE WHEN col1 = @First THEN 1 ELSE 0 END +
     CASE WHEN col2= @second THEN 1 ELSE 0 END +
      CASE WHEN col3 = @third THEN 1 ELSE 0 END AS "NUM_OF_MATCHES"  )  >= 1
    group by  ( CASE WHEN col1 = @First THEN 1 ELSE 0 END +
     CASE WHEN col2 = @second THEN 1 ELSE 0 END+
      CASE WHEN col3 = @third THEN 1 ELSE 0 END )        
        )

Answer 1

SELECT 
'0/3' AS Matches, 
SUM(CASE WHEN (CASE WHEN col1 = @first  THEN 1 ELSE 0 END + 
               CASE WHEN col2 = @second THEN 1 ELSE 0 END + 
               CASE WHEN col3 = @third  THEN 1 ELSE 0 END) = 0 THEN 1 ELSE 0 END) AS NumMatches
FROM FormsDataRowFormat
UNION
SELECT '1/3' AS Matches,
SUM(CASE WHEN (CASE WHEN col1 = @first  THEN 1 ELSE 0 END + 
               CASE WHEN col2 = @second THEN 1 ELSE 0 END + 
               CASE WHEN col3 = @third  THEN 1 ELSE 0 END) = 1 THEN 1 ELSE 0 END) AS NumMatches
FROM FormsDataRowFormat
UNION
SELECT '2/3' AS Matches,
SUM(CASE WHEN (CASE WHEN col1 = @first  THEN 1 ELSE 0 END + 
               CASE WHEN col2 = @second THEN 1 ELSE 0 END + 
               CASE WHEN col3 = @third  THEN 1 ELSE 0 END) = 2 THEN 1 ELSE 0 END) AS NumMatches
FROM FormsDataRowFormat
UNION
SELECT '3/3' AS Matches,
SUM(CASE WHEN (CASE WHEN col1 = @first  THEN 1 ELSE 0 END + 
               CASE WHEN col2 = @second THEN 1 ELSE 0 END + 
               CASE WHEN col3 = @third  THEN 1 ELSE 0 END) = 3 THEN 1 ELSE 0 END) AS NumMatches
FROM FormsDataRowFormat

对于输入数据：

/--------------------
| col1 | col2 | col3 |
|------+------+------|
|   8  |   7  |   1  |
|   5  |   7  |   1  |
|   4  |   4  |   4  |
|   4  |   5  |   2  |
|   4  |   6  |   2  |
|   2  |   2  |   3  |
|   5  |   2  |   1  |
|   2  |   7  |   7  |
|   2  |   1  |   3  |
|   1  |   2  |   3  |
--------------------/

with：

@first = 1
@second = 2
@third = 3

给出输出：

/----------------------
| Matches | NumMatches |    
|---------+------------|
|   0/3   |      6     |
|   1/3   |      2     |
|   2/3   |      1     |
|   3/3   |      1     |
----------------------/

编辑 - 动态SQL解决方案：

按照您的评论，澄清列的数量是动态的（3至6个包含在内），下面是一个动态的SQL解决方案，它将处理任何此类列数。

请注意，动态SQL可能不是解决此问题的最佳解决方案，尤其是如果它将在非常大的数据集上重复多次和/或运行。但是，这是我知道实现这一目标的唯一方法，因此我想包括它（很高兴看到其他用户的更好解决方案！）

。

DECLARE @numColumns INT
SELECT @numColumns = COUNT(*) FROM sysobjects o JOIN syscolumns c ON o.id = c.id WHERE o.name = 'FormsDataRowFormat'
DECLARE @sql NVARCHAR(MAX)
SET @sql =
'SELECT ' +
'''0/' + CONVERT(VARCHAR,@numColumns) + ''' AS Matches, ' +
'SUM(CASE WHEN (CASE WHEN col1 = ' + CONVERT(VARCHAR,@first) + '  THEN 1 ELSE 0 END + ' +
               'CASE WHEN col2 = ' + CONVERT(VARCHAR,@second) + ' THEN 1 ELSE 0 END + ' +
               'CASE WHEN col3 = ' + CONVERT(VARCHAR,@third) + '  THEN 1 ELSE 0 END' +
                CASE WHEN @numColumns >= 4 THEN ' + CASE WHEN col4 = ' + CONVERT(VARCHAR,@fourth) + '  THEN 1 ELSE 0 END' ELSE '' END +
                CASE WHEN @numColumns >= 5 THEN ' + CASE WHEN col5 = ' + CONVERT(VARCHAR,@fifth) + '  THEN 1 ELSE 0 END' ELSE '' END +
                CASE WHEN @numColumns  = 6 THEN ' + CASE WHEN col6 = ' + CONVERT(VARCHAR,@sixth) + '  THEN 1 ELSE 0 END' ELSE '' END +
               ') = 0 THEN 1 ELSE 0 END) AS NumMatches ' +
'FROM FormsDataRowFormat ' +
'UNION ' +
'SELECT ''1/' + CONVERT(VARCHAR,@numColumns) + ''' AS Matches, ' +
'SUM(CASE WHEN (CASE WHEN col1 = ' + CONVERT(VARCHAR,@first) + '  THEN 1 ELSE 0 END + ' +
               'CASE WHEN col2 = ' + CONVERT(VARCHAR,@second) + ' THEN 1 ELSE 0 END + ' +
               'CASE WHEN col3 = ' + CONVERT(VARCHAR,@third) + '  THEN 1 ELSE 0 END' +
                CASE WHEN @numColumns >= 4 THEN ' + CASE WHEN col4 = ' + CONVERT(VARCHAR,@fourth) + '  THEN 1 ELSE 0 END' ELSE '' END +
                CASE WHEN @numColumns >= 5 THEN ' + CASE WHEN col5 = ' + CONVERT(VARCHAR,@fifth) + '  THEN 1 ELSE 0 END' ELSE '' END +
                CASE WHEN @numColumns  = 6 THEN ' + CASE WHEN col6 = ' + CONVERT(VARCHAR,@sixth) + '  THEN 1 ELSE 0 END' ELSE '' END +
               ') = 1 THEN 1 ELSE 0 END) AS NumMatches ' +
'FROM FormsDataRowFormat ' +
'UNION ' +
'SELECT ''2/' + CONVERT(VARCHAR,@numColumns) + ''' AS Matches, ' +
'SUM(CASE WHEN (CASE WHEN col1 = ' + CONVERT(VARCHAR,@first) + '  THEN 1 ELSE 0 END + ' +
               'CASE WHEN col2 = ' + CONVERT(VARCHAR,@second) + ' THEN 1 ELSE 0 END + ' +
               'CASE WHEN col3 = ' + CONVERT(VARCHAR,@third) + '  THEN 1 ELSE 0 END' +
                CASE WHEN @numColumns >= 4 THEN ' + CASE WHEN col4 = ' + CONVERT(VARCHAR,@fourth) + '  THEN 1 ELSE 0 END' ELSE '' END +
                CASE WHEN @numColumns >= 5 THEN ' + CASE WHEN col5 = ' + CONVERT(VARCHAR,@fifth) + '  THEN 1 ELSE 0 END' ELSE '' END +
                CASE WHEN @numColumns  = 6 THEN ' + CASE WHEN col6 = ' + CONVERT(VARCHAR,@sixth) + '  THEN 1 ELSE 0 END' ELSE '' END +
               ') = 2 THEN 1 ELSE 0 END) AS NumMatches ' +
'FROM FormsDataRowFormat ' +
'UNION ' +
'SELECT ''3/' + CONVERT(VARCHAR,@numColumns) + ''' AS Matches, ' +
'SUM(CASE WHEN (CASE WHEN col1 = ' + CONVERT(VARCHAR,@first) + '  THEN 1 ELSE 0 END + ' +
               'CASE WHEN col2 = ' + CONVERT(VARCHAR,@second) + ' THEN 1 ELSE 0 END + ' +
               'CASE WHEN col3 = ' + CONVERT(VARCHAR,@third) + '  THEN 1 ELSE 0 END' +
                CASE WHEN @numColumns >= 4 THEN ' + CASE WHEN col4 = ' + CONVERT(VARCHAR,@fourth) + '  THEN 1 ELSE 0 END' ELSE '' END +
                CASE WHEN @numColumns >= 5 THEN ' + CASE WHEN col5 = ' + CONVERT(VARCHAR,@fifth) + '  THEN 1 ELSE 0 END' ELSE '' END +
                CASE WHEN @numColumns  = 6 THEN ' + CASE WHEN col6 = ' + CONVERT(VARCHAR,@sixth) + '  THEN 1 ELSE 0 END' ELSE '' END +
               ') = 3 THEN 1 ELSE 0 END) AS NumMatches ' +
'FROM FormsDataRowFormat ' +
CASE WHEN @numColumns >= 4 THEN 
    'UNION ' +
    'SELECT ''4/' + CONVERT(VARCHAR,@numColumns) + ''' AS Matches, ' +
    'SUM(CASE WHEN (CASE WHEN col1 = ' + CONVERT(VARCHAR,@first) + '  THEN 1 ELSE 0 END + ' +
                   'CASE WHEN col2 = ' + CONVERT(VARCHAR,@second) + ' THEN 1 ELSE 0 END + ' +
                   'CASE WHEN col3 = ' + CONVERT(VARCHAR,@third) + '  THEN 1 ELSE 0 END' +
                    CASE WHEN @numColumns >= 4 THEN ' + CASE WHEN col4 = ' + CONVERT(VARCHAR,@fourth) + '  THEN 1 ELSE 0 END' ELSE '' END +
                    CASE WHEN @numColumns >= 5 THEN ' + CASE WHEN col5 = ' + CONVERT(VARCHAR,@fifth) + '  THEN 1 ELSE 0 END' ELSE '' END +
                    CASE WHEN @numColumns  = 6 THEN ' + CASE WHEN col6 = ' + CONVERT(VARCHAR,@sixth) + '  THEN 1 ELSE 0 END' ELSE '' END +
                   ') = 4 THEN 1 ELSE 0 END) AS NumMatches ' +
'FROM FormsDataRowFormat ' ELSE '' END +
CASE WHEN @numColumns >= 5 THEN 
    'UNION ' +
    'SELECT ''5/' + CONVERT(VARCHAR,@numColumns) + ''' AS Matches, ' +
    'SUM(CASE WHEN (CASE WHEN col1 = ' + CONVERT(VARCHAR,@first) + '  THEN 1 ELSE 0 END + ' +
                   'CASE WHEN col2 = ' + CONVERT(VARCHAR,@second) + ' THEN 1 ELSE 0 END + ' +
                   'CASE WHEN col3 = ' + CONVERT(VARCHAR,@third) + '  THEN 1 ELSE 0 END' +
                    CASE WHEN @numColumns >= 4 THEN ' + CASE WHEN col4 = ' + CONVERT(VARCHAR,@fourth) + '  THEN 1 ELSE 0 END' ELSE '' END +
                    CASE WHEN @numColumns >= 5 THEN ' + CASE WHEN col5 = ' + CONVERT(VARCHAR,@fifth) + '  THEN 1 ELSE 0 END' ELSE '' END +
                    CASE WHEN @numColumns  = 6 THEN ' + CASE WHEN col6 = ' + CONVERT(VARCHAR,@sixth) + '  THEN 1 ELSE 0 END' ELSE '' END +
                   ') = 5 THEN 1 ELSE 0 END) AS NumMatches ' +
'FROM FormsDataRowFormat ' ELSE '' END +
CASE WHEN @numColumns = 6 THEN 
    'UNION ' +
    'SELECT ''6/' + CONVERT(VARCHAR,@numColumns) + ''' AS Matches, ' +
    'SUM(CASE WHEN (CASE WHEN col1 = ' + CONVERT(VARCHAR,@first) + '  THEN 1 ELSE 0 END + ' +
                   'CASE WHEN col2 = ' + CONVERT(VARCHAR,@second) + ' THEN 1 ELSE 0 END + ' +
                   'CASE WHEN col3 = ' + CONVERT(VARCHAR,@third) + '  THEN 1 ELSE 0 END' +
                    CASE WHEN @numColumns >= 4 THEN ' + CASE WHEN col4 = ' + CONVERT(VARCHAR,@fourth) + '  THEN 1 ELSE 0 END' ELSE '' END +
                    CASE WHEN @numColumns >= 5 THEN ' + CASE WHEN col5 = ' + CONVERT(VARCHAR,@fifth) + '  THEN 1 ELSE 0 END' ELSE '' END +
                    CASE WHEN @numColumns  = 6 THEN ' + CASE WHEN col6 = ' + CONVERT(VARCHAR,@sixth) + '  THEN 1 ELSE 0 END' ELSE '' END +
                   ') = 6 THEN 1 ELSE 0 END) AS NumMatches ' +
'FROM FormsDataRowFormat' ELSE '' END 
EXEC sp_executesql @sql