如何查找TSQL分组的最小值

我找到了第一笔交易（分钟），但当我添加"Winners"列时，我会得到他们第一次获胜的一行和他们第一次失败的一行。我只需要第一排，包括他们是赢是输。我曾尝试汇总获奖者专栏，但没有成功。如果可能的话，我宁愿不进行子查询。提前感谢您查看此信息。

SELECT 
MIN(dbo.ADT.Time) AS FirstShowWager, 
dbo.AD.Account,  dbo.AD.FirstName, 
dbo.AD.LastName, dbo.ADW.Winners  
FROM     
dbo.BLAH
WHERE       
(dbo.ADT.RunDate = CONVERT(DATETIME, '2014-04-12
00:00:00', 102)) AND (dbo.ADW.Pool = N'shw') 
GROUP BY
dbo.AD.Account, 
dbo.AD.FirstName,
dbo.AD.LastName,
dbo.AD.RunDate, 
dbo.ADW.Winners 
ORDER BY 
dbo.AD.Account

select sorted.* 
from 
(
    SELECT dbo.ADT.Time AS FirstShowWager, 
           dbo.AD.Account,  dbo.AD.FirstName, 
           dbo.AD.LastName, dbo.ADW.Winners,  
           ROW_NUMBER ( ) OVER (partition by dbo.AD.Account, 
                                             dbo.AD.FirstName,
                                             dbo.AD.LastName,
                                             dbo.AD.RunDate
                                    order by dbo.ADT.Time) as rowNum
      FROM dbo.AD
     WHERE dbo.ADT.RunDate = CONVERT(DATETIME, '2014-04-1200:00:00', 102) 
       AND dbo.ADW.Pool = N'shw'
) as sorted 
where rowNum = 1

ROW_NUMBER

听起来你不在乎winners列的值，通过对winners进行分组，你会得到多行，一行为null，另一行为非null值。如果你不在乎他们赢了多少，而只是在乎他们赢还是输，你可以这样做，

SELECT 
MIN(dbo.ADT.Time) AS FirstShowWager, 
dbo.AD.Account,  dbo.AD.FirstName, 
dbo.AD.LastName, CASE WHEN dbo.ADW.Winners IS NULL THEN 0 ELSE 1 END
FROM     
dbo.BLAH
WHERE       
(dbo.ADT.RunDate = CONVERT(DATETIME, '2014-04-12
00:00:00', 102)) AND (dbo.ADW.Pool = N'shw') 
GROUP BY
dbo.AD.Account, 
dbo.AD.FirstName,
dbo.AD.LastName,
dbo.AD.RunDate, 
dbo.ADW.Winners 
ORDER BY 
dbo.AD.Account

在select语句中添加此case语句而不是winners列，并按分组

 Case ( winners is NULL then 'Lose' else 'Win' end )

通常，这是通过一个派生表来完成的，该表选择您想要的记录，然后连接回所有分组字段的原始表。

您可以在内部查询中找到MIN，然后根据ID将其加入上的ADW表，以确定他们是否是赢家。

SELECT b.*, ADW.winner 
FROM dbo.ADW ADW INNER JOIN (SELECT MIN(ADT.RunTime) AS FirstShowWager, 
                             AD.Account,  AD.FirstName, 
                             AD.Lastnamne, AD.ADID
                             FROM dbo.AD AD INNER JOIN dbo.ADT ADT AD.adid = ADT.ADID 
                             GROUP BY AD.Account, AD.Firstname, AD.Lastnamne, AD.ADID) b
    ON ADW.ADID = b.ADID

假设：在从ADT到AD表。从ADW到AD表。

相关内容

最新更新

热门标签：