gpt4 book ai didi

使用值列表中随机但唯一的值更新字段的 SQL 命令

转载 作者:行者123 更新时间:2023-12-04 14:16:30 26 4
gpt4 key购买 nike

考虑下面的表结构

表:团队

+-------+-------------+----------+
| id | companyid | teamcolor|
+-------+-------------+----------+
| 1 | 1 | null |
| 2 | 2 | null |
| 3 | 2 | null |
| 4 | 2 | null |
| 5 | 3 | null |
| 6 | 4 | null |
+-------+-------------+----------+

可用颜色列表:

SELECT * FROM (VALUES('#f44336'), ('#E91E63'), ('#E91E63'), ('#9C27B0'), ('#673AB7'), ('#3F51B5'), ('#2196F3'), ('#03A9F4'), ('#00BCD4'), ('#009688'), ('#4CAF50'), ('#8BC34A'), ('#CDDC39'), ('#FFEB3B')) N(Colour)

我需要一个 SQL 更新来使用颜色列表中的随机颜色初始化 Team 表的 teamcolor 字段。 companyid 的颜色也必须是唯一的。

期望的结果

+-------+-------------+----------+
| id | companyid | teamcolor|
+-------+-------------+----------+
| 1 | 1 | '#f44336'|
| 2 | 2 | '#E91E63'|
| 3 | 2 | '#03A9F4'|
| 4 | 2 | '#8BC34A'|
| 5 | 3 | '#f44336'|
| 6 | 4 | '#FFEB3B'|
+-------+-------------+----------+

我已经做了什么

UPDATE T
SET TeamColour = C.Colour
FROM Team T
CROSS APPLY (
SELECT TOP 1 Colour
FROM (
VALUES
('#f44336')
,('#E91E63')
,('#E91E63')

-- many rows
-- ...
-- many rows

,('#BF360C')
,('#3E2723')
,('#212121')
,('#263238')
) N(Colour)
WHERE T.ID = T.ID
ORDER BY NEWID()
) C

UPDATE T
SET TeamColour = C.Colour
FROM [Team] T
CROSS APPLY (
SELECT TOP 1 *
FROM [Team] T1
,(
VALUES
('#f44336')
,('#E91E63')
,('#E91E63')

-- many rows
-- ...
-- many rows

,('#3E2723')
,('#212121')
,('#263238')
) N(Colour)
WHERE NOT EXISTS (
SELECT *
FROM [Team] T2
WHERE T2.Company_ID = T1.Company_ID
AND T2.TeamColour = Colour
)
AND T.ID = T1.ID
ORDER BY NEWID()
) C

但这不会根据需要通过 companyid 创建唯一的颜色实例。

最佳答案

你可以使用它。

DECLARE @Team TABLE (id INT, companyid INT, teamcolor VARCHAR(10))
INSERT INTO @Team VALUES
(1 , 1 , null ),
(2 , 2 , null ),
(3 , 2 , null ),
(4 , 2 , null ),
(5 , 3 , null ),
(6 , 4 , null )



;WITH CTE_Team AS
(
SELECT *,
ROW_NUMBER() OVER(ORDER BY id) RNK
FROM @Team
)
, RdnColor AS (
SELECT N.*
, ROW_NUMBER() OVER( ORDER BY NEWID()) ID
, COUNT(*) OVER() CNT
FROM (VALUES('#f44336'), ('#E91E63'), ('#E91E64'), ('#9C27B0'), ('#673AB7'), ('#3F51B5'), ('#2196F3'), ('#03A9F4'), ('#00BCD4'), ('#009688'), ('#4CAF50'), ('#8BC34A'), ('#CDDC39'), ('#FFEB3B')
) N(Colour)
)
UPDATE T
SET teamcolor = C.Colour
FROM CTE_Team T
INNER JOIN RdnColor C ON (T.RNK % C.CNT) + 1 = C.ID

select * from @Team

结果:

id          companyid   teamcolor
----------- ----------- ----------
1 1 #9C27B0
2 2 #00BCD4
3 2 #FFEB3B
4 2 #673AB7
5 3 #03A9F4
6 4 #CDDC39

关于使用值列表中随机但唯一的值更新字段的 SQL 命令,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/49123906/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com