单个查询中的多个 MAX 值

debugcn 发表于 Dev

瑞安·史密斯

作为转换过程的一部分，我正在获取这些数据并将其插入到另一个表中。不幸的是，我无法在源或目标位置修改表架构。

Schema如下（超级简化但数据类型不能修改，只是明显的强制转换）

CREATE TABLE [dbo].[TestScores](
    [id] [INT] NOT NULL,
    [EXAMNE_ID] [VARCHAR](40) NULL,
    [TestName] VARCHAR(30) NULL,
    [PASS_STA] [VARCHAR](5) NULL,
    [TST_DTE] [VARCHAR](8) NULL,
    [STD_SCOR] [VARCHAR](3) NULL,
) ON [PRIMARY]

我有以下源数据：（显然只是数据的快照，但对于一个人）

+----+-----------+-----------------+----------+----------+----------+
| id | EXAMNE_ID |    TestName     | PASS_STA | TST_DTE  | STD_SCOR |
+----+-----------+-----------------+----------+----------+----------+
|  1 |     00001 | Social Studies  | Fail     | 20160608 |        7 |
|  2 |     00001 | Science         | Fail     | 20160608 |        8 |
|  3 |     00001 | Reading         | Fail     | 20160608 |        2 |
|  4 |     00001 | Math            | Fail     | 20160608 |        8 |
|  5 |     00001 | Writing         | Fail     | 20160608 |        7 |
|  6 |     00001 | Social Studies  | Fail     | 20160608 |        7 |
|  7 |     00001 | Science         | Fail     | 20160608 |        8 |
|  8 |     00001 | Reading         | Fail     | 20160608 |        2 |
|  9 |     00001 | Math            | Fail     | 20160608 |        8 |
| 10 |     00001 | Writing         | Fail     | 20160608 |        7 |
| 11 |     00001 | Social Studies  | Fail     | 20160608 |        7 |
| 12 |     00001 | Science         | Fail     | 20160608 |        8 |
| 13 |     00001 | Reading         | Fail     | 20160608 |        2 |
| 14 |     00001 | Math            | Fail     | 20160608 |        8 |
| 15 |     00001 | Writing         | Fail     | 20160608 |        7 |
| 16 |     00001 | Social Studies  | Fail     | 20160608 |        7 |
| 17 |     00001 | Social Studies  | Fail     | 20160930 |       10 |
| 18 |     00001 | Science         | Fail     | 20160608 |        8 |
| 19 |     00001 | Reading         | Fail     | 20160608 |        2 |
| 20 |     00001 | Reading         | Fail     | 20160930 |        5 |
| 21 |     00001 | Math            | Fail     | 20160608 |        8 |
| 22 |     00001 | Writing         | Fail     | 20160608 |        7 |
| 23 |     00001 | Writing         | Fail     | 20160930 |       10 |
| 24 |     00001 | Social Studies  | Fail     | 20160608 |        7 |
| 25 |     00001 | Social Studies  | Fail     | 20160930 |       10 |
| 26 |     00001 | Science         | Fail     | 20160608 |        8 |
| 27 |     00001 | Reading         | Fail     | 20160608 |        2 |
| 28 |     00001 | Reading         | Fail     | 20160930 |        5 |
| 29 |     00001 | Math            | Fail     | 20160608 |        8 |
| 30 |     00001 | Writing         | Fail     | 20160608 |        7 |
| 31 |     00001 | Writing         | Fail     | 20160930 |       10 |
| 32 |     00001 | Social Studies  | Fail     | 20160608 |        7 |
| 33 |     00001 | Social Studies  | Fail     | 20160930 |       10 |
| 34 |     00001 | Science         | Fail     | 20160608 |        8 |
| 35 |     00001 | Reading         | Fail     | 20160608 |        2 |
| 36 |     00001 | Reading         | Fail     | 20160930 |        5 |
| 37 |     00001 | Math            | Fail     | 20160608 |        8 |
| 38 |     00001 | Writing         | Pass     | 20160608 |        7 |
| 39 |     00001 | Writing         | Pass     | 20160930 |       10 |
| 40 |     00001 | Social Studies  | Pass     | 20160608 |        7 |
| 41 |     00001 | Social Studies  | Pass     | 20160930 |       10 |
| 42 |     00001 | Science         | Pass     | 20160608 |        8 |
| 43 |     00001 | Reading         | Pass     | 20160608 |        2 |
| 44 |     00001 | Reading         | Pass     | 20160930 |        5 |
| 45 |     00001 | Reading         | Pass     | 20161202 |        9 |
| 46 |     00001 | Math            | Pass     | 20160608 |        8 |
+----+-----------+-----------------+----------+----------+----------+

我的最终结果目标数据应如下所示：

+----+-----------+-----------------+----------+----------+----------+
| id | EXAMNE_ID |    TestName     | PASS_STA | TST_DTE  | STD_SCOR |
+----+-----------+-----------------+----------+----------+----------+
| 39 |     00001 | Writing         | Pass     | 20160930 |       10 |
| 41 |     00001 | Social Studies  | Pass     | 20160930 |       10 |
| 42 |     00001 | Science         | Pass     | 20160608 |        8 |  
| 45 |     00001 | Reading         | Pass     | 20161202 |        9 |
| 46 |     00001 | Math            | Pass     | 20160608 |        8 |
+----+-----------+-----------------+----------+----------+----------+

我知道我提供了大量额外数据，因为我实际上只是在寻找 PASS_STA 为“通过”的结果，但我想提供更多完整的图片。

本质上发生的逻辑是，对于给定的人，当考试通过时，取出 TST_DTE 和 STD_SCOR 列的最大值。我正在尝试使用 max 的多个变体，但它正朝着错误的方向前进。

目前这个查询没有给我我正在寻找的东西。

SELECT DISTINCT 
EXAMNE_ID, PASS_STA, TestName, MAX(TST_DTE) AS 'TST_DTE', MAX(STD_SCOR) AS 'STD_SCOR' 
FROM dbo.TestScores  
GROUP BY EXAMNE_ID, PASS_STA, TestName, STD_SCOR 
HAVING PASS_STA = 'Pass'

我的一部分是认为我需要嵌套查询，因为我正在寻找两个最大值（加上一个是日期 - 好吧，日期，因为它在 varchar 中），另一个是数字（但同样，存储在 varchar 中）或者以某种方式 RANK 功能？

我宁愿不必为五个主题领域中的每一个都做汇总 CASE 陈述。实际上总共有几十个主题领域，我在这里展示的只是这个测试类型有五个。所以虽然我认为汇总案例陈述可能有效，但它可能太麻烦了。

有任何想法吗？

提前致谢

SqlZim

除了数据类型问题之外，您需要std_scor从您的组中删除，并将您的移动having到where：

考虑tst_dte到您的日期格式，不需要转换为date或int，但无论如何我都包含了转换。

select 
   examne_id
 , pass_sta
 , TestName
 , max(convert(date,tst_dte)) as tst_dte
 , max(convert(int,std_scor)) as std_scor
from dbo.TestScores
group by 
   examne_id
 , pass_sta
 , TestName
having pass_sta = 'Pass'

reextester 演示：http ://rextester.com/BDI16678

+-----------+----------+----------------+------------+----------+
| examne_id | pass_sta |    TestName    |  tst_dte   | std_scor |
+-----------+----------+----------------+------------+----------+
|         1 | Pass     | Math           | 2016-06-08 |        8 |
|         1 | Pass     | Reading        | 2016-12-02 |        9 |
|         1 | Pass     | Science        | 2016-06-08 |        8 |
|         1 | Pass     | Social Studies | 2016-09-30 |       10 |
|         1 | Pass     | Writing        | 2016-09-30 |       10 |
+-----------+----------+----------------+------------+----------+

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。