代码之家 › 专栏 › 技术社区 › Hannover Fist

查找每行的最大列名和值

sql-server-2012 sql-server sql

Hannover Fist · 技术社区 · 7 年前

我需要找到得分最低的问题。数据中有“是”和“否”列,用于计算总分。我不仅需要知道最低分数,还需要知道哪个问题的分数最低。分数都存储在一个记录中。

最好的办法是什么?我试过用旋转台,但弄得一团糟。

以下是一些示例数据:

SELECT 1 AS Score_ID, 28.0 AS YesPtsGivenI, 2.0 AS NoPtsGivenI, 30.0 AS YesPtsGivenII, 0.0 AS NoPtsGivenII, 29.0 AS YesPtsGivenIII, 0.0 AS NoPtsGivenIII, 25.0 AS YesPtsGivenIV, 3.0 AS NoPtsGivenIV, 30.0 AS YesPtsGivenV, 0.0 AS NoPtsGivenV, 29.0 AS YesPtsGivenVI, 0.0 AS NoPtsGivenVI 
INTO #FS 
UNION 
SELECT 2 AS Score_ID, 27.0 AS YesPtsGivenI, 3.0 AS NoPtsGivenI, 29.0 AS YesPtsGivenII, 1.0 AS NoPtsGivenII, 28.0 AS YesPtsGivenIII, 0.0 AS NoPtsGivenIII, 28.0 AS YesPtsGivenIV, 0.0 AS NoPtsGivenIV, 30.0 AS YesPtsGivenV, 0.0 AS NoPtsGivenV, 29.0 AS YesPtsGivenVI, 0.0 AS NoPtsGivenVI  
UNION 
SELECT 3 AS Score_ID, 28.0 AS YesPtsGivenI, 2.0 AS NoPtsGivenI, 30.0 AS YesPtsGivenII, 0.0 AS NoPtsGivenII, 27.0 AS YesPtsGivenIII, 2.0 AS NoPtsGivenIII, 28.0 AS YesPtsGivenIV, 0.0 AS NoPtsGivenIV, 28.0 AS YesPtsGivenV, 2.0 AS NoPtsGivenV, 28.0 AS YesPtsGivenVI, 1.0 AS NoPtsGivenVI 
UNION 
SELECT 4 AS Score_ID, 30.0 AS YesPtsGivenI, 0.0 AS NoPtsGivenI, 29.0 AS YesPtsGivenII, 1.0 AS NoPtsGivenII, 29.0 AS YesPtsGivenIII, 0.0 AS NoPtsGivenIII, 28.0 AS YesPtsGivenIV, 0.0 AS NoPtsGivenIV, 30.0 AS YesPtsGivenV, 0.0 AS NoPtsGivenV, 28.0 AS YesPtsGivenVI, 1.0 AS NoPtsGivenVI 
UNION 
SELECT 5 AS Score_ID, 29.0 AS YesPtsGivenI, 1.0 AS NoPtsGivenI, 30.0 AS YesPtsGivenII, 0.0 AS NoPtsGivenII, 28.0 AS YesPtsGivenIII, 1.0 AS NoPtsGivenIII, 28.0 AS YesPtsGivenIV, 0.0 AS NoPtsGivenIV, 29.0 AS YesPtsGivenV, 1.0 AS NoPtsGivenV, 29.0 AS YesPtsGivenVI, 0.0 AS NoPtsGivenVI 
UNION 
SELECT 6 AS Score_ID, 30.0 AS YesPtsGivenI, 0.0 AS NoPtsGivenI, 28.0 AS YesPtsGivenII, 2.0 AS NoPtsGivenII, 29.0 AS YesPtsGivenIII, 0.0 AS NoPtsGivenIII, 27.0 AS YesPtsGivenIV, 1.0 AS NoPtsGivenIV, 30.0 AS YesPtsGivenV, 0.0 AS NoPtsGivenV, 29.0 AS YesPtsGivenVI, 0.0 AS NoPtsGivenVI 
UNION 
SELECT 7 AS Score_ID, 39.0 AS YesPtsGivenI, 0.0 AS NoPtsGivenI, 30.0 AS YesPtsGivenII, 0.0 AS NoPtsGivenII, 29.0 AS YesPtsGivenIII, 0.0 AS NoPtsGivenIII, 26.0 AS YesPtsGivenIV, 2.0 AS NoPtsGivenIV, 30.0 AS YesPtsGivenV, 0.0 AS NoPtsGivenV, 29.0 AS YesPtsGivenVI, 0.0 AS NoPtsGivenVI

以下是我的分数查询:

SELECT 
FS.YesPtsGivenI / (FS.YesPtsGivenI + FS.NoPtsGivenI) AS Q1, 
FS.YesPtsGivenII / (FS.YesPtsGivenII + FS.NoPtsGivenII) AS Q2, 
FS.YesPtsGivenIII / (FS.YesPtsGivenIII + FS.NoPtsGivenIII) AS Q3, 
FS.YesPtsGivenIV / (FS.YesPtsGivenIV + FS.NoPtsGivenIV) AS Q4, 
FS.YesPtsGivenV / (FS.YesPtsGivenV + FS.NoPtsGivenV) AS Q5, 
FS.YesPtsGivenVI / (FS.YesPtsGivenVI + FS.NoPtsGivenVI) AS Q6 
FROM #FS FS

我需要从上面的结果中确定表中每一行哪一个问题的分数最低。

2 回复 | 直到 7 年前

Gordon Linoff 7 年前

我无法真正理解您的查询或样本数据,因为我不太清楚“分数”是多少。但这个问题最简单的答案是 apply .我可以这样推测:

select fs.*, v.*
from #fs fs cross apply
     (select top (1) val, which
      from (values (FS.YesPtsGivenI / (FS.YesPtsGivenI + FS.NoPtsGivenI), 'Q1'), 
                   (FS.YesPtsGivenII / (FS.YesPtsGivenII + FS.NoPtsGivenII), 'Q2'), 
                   (FS.YesPtsGivenIII / (FS.YesPtsGivenIII + FS.NoPtsGivenIII), 'Q3'), 
                   (FS.YesPtsGivenIV / (FS.YesPtsGivenIV + FS.NoPtsGivenIV), 'Q4'), 
                   (FS.YesPtsGivenV / (FS.YesPtsGivenV + FS.NoPtsGivenV), 'Q5'), 
                   (FS.YesPtsGivenVI / (FS.YesPtsGivenVI + FS.NoPtsGivenVI), 'Q6')
           ) v(val, which)
       order by val desc
      ) v;

Eric Brandt 7 年前

这可能有点太聪明了,但它完成了任务。

使用基本查询,我取消了对结果集的排序,使排序更容易,然后应用 SELECT TOP 1 WITH TIES 每个人得分最低的诀窍 Score_ID .

SELECT TOP 1 WITH TIES
    Score_ID,
    QName,
    QScore
FROM
    (
        SELECT Score_ID,
        FS.YesPtsGivenI / (FS.YesPtsGivenI + FS.NoPtsGivenI) AS Q1, 
        FS.YesPtsGivenII / (FS.YesPtsGivenII + FS.NoPtsGivenII) AS Q2, 
        FS.YesPtsGivenIII / (FS.YesPtsGivenIII + FS.NoPtsGivenIII) AS Q3, 
        FS.YesPtsGivenIV / (FS.YesPtsGivenIV + FS.NoPtsGivenIV) AS Q4, 
        FS.YesPtsGivenV / (FS.YesPtsGivenV + FS.NoPtsGivenV) AS Q5, 
        FS.YesPtsGivenVI / (FS.YesPtsGivenVI + FS.NoPtsGivenVI) AS Q6 
        FROM #FS FS 
    ) AS q
UNPIVOT
    (
        QScore
        FOR QName IN (Q1, Q2, Q3, Q4, Q5, Q6)
    ) unp
ORDER BY RANK() OVER (PARTITION BY Score_ID ORDER BY QScore ASC)

结果:

+----------+-------+----------+
| Score_ID | QName |  QScore  |
+----------+-------+----------+
|        1 | Q4    | 0.892857 |
|        2 | Q1    | 0.900000 |
|        3 | Q3    | 0.931034 |
|        4 | Q6    | 0.965517 |
|        5 | Q3    | 0.965517 |
|        6 | Q2    | 0.933333 |
|        7 | Q4    | 0.928571 |
+----------+-------+----------+