gpt4 book ai didi

sql-server - 在枢轴中执行操作

转载 作者:行者123 更新时间:2023-12-03 08:00:55 26 4
gpt4 key购买 nike

我正在尝试实现PIVOT,但是在执行基本操作时遇到了麻烦。

当前表:

week_no  username  days      pick_count duration
------------------------------------------------
Week 50 Beck W Wednesday 227 7978
Week 50 Beck W Friday 320 7481
Week 50 Beck W Friday 282 5718
Week 50 Cockram D Thursday 165 10478
Week 50 Cowell P Thursday 145 14403
Week 50 Cowell P Thursday 159 7450
Week 50 Cowell P Friday 217 13101
...

预期结果:
week_no username  monday tuesday wednesday thursday friday saturday sunday
--------------------------------------------------------------------------
Week 50 Beck W NULL NULL 102 NULL 164 NULL NULL
Week 50 Cockram D NULL NULL NULL 56 NULL NULL NULL
Week 50 Cowell P NULL NULL NULL 50 59 NULL NULL
...

预期结果应计算如下: pick_count * 3600 / duration。那就是我遇到问题时的计算。当 PIVOT像这样
SELECT 
*
FROM
(
SELECT
week_no,
username,
days,
pick_count,
duration
FROM
table
) AS src
PIVOT
(
SUM(pick_count) * 3600 / SUM(duration) FOR days IN (monday, tuesday, wednesday, thursday, friday, saturday, sunday)
) AS pvt

我得到 Incorrect syntax near '*'

最佳答案

PIVOT不是很灵活。

您可以只使用旧样式的交叉表方法。

SELECT week_no,
username,
SUM(CASE WHEN days = 'monday' then pick_count end) * 3600
/ SUM(CASE WHEN days = 'monday' then duration end) as monday,
SUM(CASE WHEN days = 'tuesday' then pick_count end) * 3600
/ SUM(CASE WHEN days = 'tuesday' then duration end) as tuesday
/*TODO: Add other five days*/
FROM YourTable
GROUP BY week_no,
username

关于sql-server - 在枢轴中执行操作,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/34511019/

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