时间戳

首页 > 解决方案 > 从 sql 请求创建工作表

sql - 从 sql 请求创建工作表

问题描述

我正在请求从游戏数据库中获取值，以帮助开发人员根据每 10 个级别使用的符文和符文升级进行平衡更改，因此为此，我为每 10 个级别创建了一个请求，并更改了 var到 runeID（总共有 60 个 rune，所以我这样做了 60 次），并且对于每个结果，我手动填写了一个谷歌表。我想知道是否可以在一个请求中直接创建所有这些请求，所以我可以复制列并将它们全部过去，这将使我赢得很多时间，即使可能使所有符文都相同时间，所以一个简单的复制粘贴和所有数据都在那里

这是我用来每 10 个级别获取符文值的代码，从级别 1 到 130，在 130 之后都在同一个请求中

declare @runeID varchar(100)
set @runeID=22001

select counT(i.characterid) as 'user level 1 to 10', avg(i.maxUpgrade) as 'average level' from items i inner join characters c on i.characterId=c.characterId 
        where itemId=@runeID and level>0 and level<11 and attached>0
select counT(i.characterid) as 'user level 11 to 20', avg(i.maxUpgrade) as 'average level' from items i inner join characters c on i.characterId=c.characterId 
        where itemId=@runeID and level>10 and level<21 and attached>0
select counT(i.characterid) as 'user level 21 to 30', avg(i.maxUpgrade) as 'average level' from items i inner join characters c on i.characterId=c.characterId 
        where itemId=@runeID and level>20 and level<31 and attached>0
select counT(i.characterid) as 'user level 31 to 40', avg(i.maxUpgrade) as 'average level' from items i inner join characters c on i.characterId=c.characterId 
        where itemId=@runeID and level>30 and level<41 and attached>0
select counT(i.characterid) as 'user level 41 to 50', avg(i.maxUpgrade) as 'average level' from items i inner join characters c on i.characterId=c.characterId 
        where itemId=@runeID and level>40 and level<51 and attached>0
select counT(i.characterid) as 'user level 51 to 60', avg(i.maxUpgrade) as 'average level' from items i inner join characters c on i.characterId=c.characterId 
        where itemId=@runeID and level>50 and level<61 and attached>0
select counT(i.characterid) as 'user level 61 to 70', avg(i.maxUpgrade) as 'average level' from items i inner join characters c on i.characterId=c.characterId 
        where itemId=@runeID and level>60 and level<71 and attached>0
select counT(i.characterid) as 'user level 71 to 80', avg(i.maxUpgrade) as 'average level' from items i inner join characters c on i.characterId=c.characterId 
        where itemId=@runeID and level>70 and level<81 and attached>0
select counT(i.characterid) as 'user level 81 to 90', avg(i.maxUpgrade) as 'average level' from items i inner join characters c on i.characterId=c.characterId 
        where itemId=@runeID and level>80 and level<91 and attached>0
select counT(i.characterid) as 'user level 91 to 100', avg(i.maxUpgrade) as 'average level' from items i inner join characters c on i.characterId=c.characterId 
        where itemId=@runeID and level>90 and level<101 and attached>0
select counT(i.characterid) as 'user level 101 to 110', avg(i.maxUpgrade) as 'average level' from items i inner join characters c on i.characterId=c.characterId 
        where itemId=@runeID and level>100 and level<111 and attached>0
select counT(i.characterid) as 'user level 111 to 120', avg(i.maxUpgrade) as 'average level' from items i inner join characters c on i.characterId=c.characterId 
        where itemId=@runeID and level>110 and level<121 and attached>0
select counT(i.characterid) as 'user level 121 to 130', avg(i.maxUpgrade) as 'average level' from items i inner join characters c on i.characterId=c.characterId 
        where itemId=@runeID and level>120 and level<131 and attached>0
select counT(i.characterid) as 'user level 131+', avg(i.maxUpgrade) as 'average level' from items i inner join characters c on i.characterId=c.characterId 
        where itemId=@runeID and level>130 and attached>0

所以现在这个查询给了我 14 个结果（我只能通过 2 个信息复制它们 2 个信息，每 10 个级别的使用次数和平均级别），所以总共 28 个信息需要手动填充 1 个符文。所以再次，我在这里看看是否有可能至少能够将所有这 28 条信息放在一个 2 列表中，我可以直接复制所有 28 条信息，甚至可以输入更大的信息所有符文ID都是一个数组，它用所有符文做一个巨大的桌子，每个符文2列

标签： sqlsql-server

解决方案

你能做这个吗？

select floor((level - 1) / 10) * 10 as range_start,
       count(*) as num_user,
       avg(i.maxUpgrade) as avg_level
from items i inner join
     characters c
     on i.characterId = c.characterId
where attached > 0
group by floor((level - 1) / 10) * 10
order by nmin(level);

这不会将 130 以上的所有内容放在同一个组中，但这是对查询的简单调整。

推荐阅读