SQL:如何取行中最小的数?

Question

如果有三个游戏回合，我如何创建一个逻辑，我可以找到每回合的最低分数?

数据集:

<表类> 轮 player_1 player_2 player_3 tbody><<tr>13428212429585371空87

行构造函数是实现所需功能的最简单方法。

/p>

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, _round INT, player_1   INT, player_2 INT, player_3 INT);
INSERT INTO @tbl (_round, player_1, player_2, player_3) VALUES
(1, 34, 28, 21),
(2, 42, 95, 85),
(3, 71, NULL, 87);
-- DDL and sample data population, end
SELECT _round, MIN(c) AS [lowest points per round]
FROM @tbl
CROSS APPLY (VALUES
(player_1),
(player_2),
(player_3)) AS t(c)
GROUP BY _round;

+--------+-------------------------+
| _round | lowest points per round |
+--------+-------------------------+
|      1 |                      21 |
|      2 |                      42 |
|      3 |                      71 |
+--------+-------------------------+

在你的问题中，你忽略了两个甚至三个玩家分享最差分数的可能性。
这是一个完整的解决方案，利用交叉应用，unpivot和string_agg。

select  * 
from    game 
cross apply 

(select  min(score)                                             as worst_score
,string_agg(player, ',') within group (order by player) as worst_players

from   (select    top 1 with ties 
player, score 

from     (select null as dummy) t 
unpivot (score for player in (player_1, player_2, player_3)) u

order by  score
) u
) u

<表类>轮player_1player_2player_3worstrongcoreworst_playerstbody><<tr>134282121player_3242958542player_13718771player_1411道明>1111player_1, player_3522222222player_1, player_2, player_3

您可以使用VALUES(表值构造函数)，如下所示:

CREATE TABLE #test (
num_round INT,
player_1 INT,
player_2 INT,
player_3 INT
)
INSERT INTO #test
(num_round, player_1, player_2, player_3)
VALUES
(1, 34, 28, 21),
(2, 42, 95, 85),
(3, 71, NULL,87)

SELECT 
num_round,
(select MIN(v) from (values (player_1), (player_2), (player_3)) as value(v) ) as min_score
FROM #test  

您可以通过使用UNPIVOT和窗口函数来实现这一点。我使用一个临时表来创建这些值。您将引用您自己的表。UNPIVOT函数没有得到充分利用。如果我是你，我会从CTE中选择，这样你就能明白发生了什么。

CREATE TABLE #scores (
num_round INT,
player_1 INT,
player_2 INT,
player_3 INT
)
INSERT INTO #scores
(num_round, player_1, player_2, player_3)
VALUES
(1, 34, 28, 21),
(2, 42, 95, 85),
(3, 71, NULL,87)
;WITH cte AS
(
SELECT num_round, player_score
FROM
(
SELECT num_round, player_1, player_2, player_3
FROM #scores
) AS x
UNPIVOT 
(
player_score FOR scores IN (player_1, player_2, player_3)
) AS up
)
SELECT DISTINCT
num_round, 
MIN(player_score) OVER (PARTITION BY num_round) AS minimum_score
FROM cte

这段代码可能适合你。

SELECT _round, CASE WHEN player_1<player_2 AND player_1<player_3 THEN player_1
WHEN player_2<player_1 AND player_2<player_3 THEN player_2
ELSE    player_3 end as LowestValue
FROM @tbl