使用子查询插入到表中

Question

我正在处理一个我从来没有做过的查询，并且我一直在坚持如何继续。我想成为一个插入命令。

我想要做的伪代码是这样的：

For each player 
    insert into CurrentHand table by getting x number of cards needed 
     so all the players have 10 cards in their hands. 

所以，如果玩家1将获得7张新卡，如果他有在自己手中3张牌。如果他手上有5张牌，Play 2将获得5张新牌。

到目前为止，我已经看到了这个select语句，但感觉就像我使用了错误的方法。

DECLARE @MaxHandCount int 
SET @MaxHandCount = 10 

SELECT Player.PlayerId 
    , (SELECT COUNT(1) FROM CurrentHand WHERE PlayerId = Player.PlayerId AND IsUsed = 0) AS CurrentHandCount 
    , (@MaxHandCount - (SELECT COUNT(1) FROM CurrentHand WHERE PlayerId = Player.PlayerId AND IsUsed = 0)) AS NeededHandCount 
    , CardId 
FROM Player, AvailableCard 
WHERE Cardid IN (SELECT CardId FROM CurrentHand WHERE IsUsed = 0) 
ORDER BY PlayerId 

表结构如下：

Player 
    - PlayerId 

AvailableCard 
    - CardId 
    - CardValue 

CurrentHand 
    - PlayerId 
    - CardId 
    - IsUsed 

非常感谢。

Answer 1

这非常有趣。这是我“解决”所需牌的解决方案。请阅读代码中的表扬。这只是选择，但我相信你可以找出你自己的插入。检查出fiddle。

-- for each card in player's hand assign a sequence number 
with cte_currenthand as 
(
    select PlayerId, 
    rank() over(partition by PlayerId order by CardId) CardSeq 
    from CurrentHand 
    where IsUsed = 0 
) 
-- for each player generate a sequence 1..10 
, cte_maxhand as 
(
select p.PlayerId, x.seq 
from Player p 
cross join (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) x(seq) 
) 
-- get cards the players need and assign them new sequence numbers 
-- basically cte_maxhand minus cte_currenthand 
, cte_needed as 
(
    select mh.PlayerId, row_number() over(order by mh.seq) seq 
    from cte_maxhand mh 
    left join cte_currenthand ch 
    on ch.CardSeq = mh.seq 
    and ch.PlayerId = mh.PlayerId 
    where ch.CardSeq is null 
) 
-- generate a random sequence on remaining cards 
, cte_deal as 
(
    select CardId, row_number() over(order by CHECKSUM(NewId())) seq 
    from AvailableCard ac 
    where not exists (
    select * 
    from CurrentHand ch 
    where ch.CardId = ac.CardId 
    ) 
) 
-- deal the cards 
select n.PlayerId, d.CardId 
from cte_needed n 
inner join cte_deal d on d.seq = n.seq 

Answer 2

样本表结构：

CREATE TABLE [dbo].[cards](
    [player] [nvarchar](20) NOT NULL, 
    [number] [int] NOT NULL 
    ) 

样本数据：

insert into cards values ('p1',3) 
insert into cards values ('p2',5) 
insert into cards values ('p3',4) 
insert into cards values ('p4',2) 

插入：

insert into cards 
select player,10-sum(number) as number 
from cards 
group by player 

Answer 3

DECLARE @MaxHandCount int 
 
SET @MaxHandCount = 10 
 

 
SELECT Player.PlayerId 
 
    , Count(CardID) AS CurrentHandCount 
 
    , @MaxHandCount - Count(CardID) AS NeededHandCount 
 
    , CardId 
 
FROM Player 
 
join CurrentHead on Player.PlayerID = CurrentHead.PlayerID 
 
WHERE IsUsed = 0 
 
Group by Player.PlayerID 
 
ORDER BY PlayerId