vSRO Çözüldü Guild rank sorgusu
- Konuyu Başlatan nes1963
- Başlangıç tarihi
ben de bu şekilde yaptım şuan düzgün çalışıyor ama donate rankı çözemedim
![SRO[2026-01-05 14-17-33]_02.webp](https://www.vsro.org/data/attachments/35/35972-143fa8d4b94272ff9477f093e059f8bb.jpg?hash=Vxyx-QaJWa "SRO[2026-01-05 14-17-33]_02.webp"){kind=small}
WITH EquippedItems AS (
SELECT
ch.GuildID,
ch.CharID,
inv.ItemID
FROM SRO_VT_SHARD.._Char ch
JOIN SRO_VT_SHARD.._Inventory inv
ON inv.CharID = ch.CharID
WHERE ch.GuildID > 0
AND inv.Slot BETWEEN 0 AND 12
AND inv.Slot NOT IN (7, 8)
AND inv.ItemID > 0
),
ItemPointRows AS (
SELECT
ei.GuildID,
ei.CharID,
CAST(
CEILING(ISNULL(oit.HR_U, 0) / 3.0)
+ CEILING(ISNULL(oit.ER_U, 0) * 2.0)
+ CEILING(ISNULL(oit.PAR_U, 0) * 2.0)
+ ISNULL(it.OptLevel, 0)
+ ISNULL(adv.SumAdv, 0)
AS INT) AS ItemPointsRow
FROM EquippedItems ei
JOIN SRO_VT_SHARD.._Items it
ON it.ID64 = ei.ItemID
JOIN SRO_VT_SHARD.._RefObjCommon obj
ON obj.ID = it.RefItemID
JOIN SRO_VT_SHARD.._RefObjItem oit
ON oit.ID = obj.Link
OUTER APPLY (
SELECT SUM(CASE WHEN bwo.nOptValue > 0 THEN bwo.nOptValue ELSE 0 END) AS SumAdv
FROM SRO_VT_SHARD.._BindingOptionWithItem bwo
WHERE bwo.nItemDBID = ei.ItemID
AND bwo.bOptType = 2
) adv
),
CharItemPoints AS (
SELECT
GuildID,
CharID,
SUM(ItemPointsRow) AS ItemPoints
FROM ItemPointRows
GROUP BY GuildID, CharID
),
GuildPoints AS (
SELECT
GuildID,
SUM(ItemPoints) AS GuildItemPoints,
COUNT(*) AS MemberCountScored
FROM CharItemPoints
GROUP BY GuildID
)
SELECT TOP (20)
gu.Name AS Guild_Name,
gp.GuildItemPoints,
gp.MemberCountScored
FROM GuildPoints gp
JOIN SRO_VT_SHARD.._Guild gu
ON gu.ID = gp.GuildID
WHERE gu.Name IS NOT NULL
AND gu.Name <> ''
AND gu.Name <> 'dummy'
ORDER BY gp.GuildItemPoints DESC, gp.MemberCountScored DESC, gu.Name ASC;
SELECT
ch.GuildID,
ch.CharID,
inv.ItemID
FROM SRO_VT_SHARD.._Char ch
JOIN SRO_VT_SHARD.._Inventory inv
ON inv.CharID = ch.CharID
WHERE ch.GuildID > 0
AND inv.Slot BETWEEN 0 AND 12
AND inv.Slot NOT IN (7, 8)
AND inv.ItemID > 0
),
ItemPointRows AS (
SELECT
ei.GuildID,
ei.CharID,
CAST(
CEILING(ISNULL(oit.HR_U, 0) / 3.0)
+ CEILING(ISNULL(oit.ER_U, 0) * 2.0)
+ CEILING(ISNULL(oit.PAR_U, 0) * 2.0)
+ ISNULL(it.OptLevel, 0)
+ ISNULL(adv.SumAdv, 0)
AS INT) AS ItemPointsRow
FROM EquippedItems ei
JOIN SRO_VT_SHARD.._Items it
ON it.ID64 = ei.ItemID
JOIN SRO_VT_SHARD.._RefObjCommon obj
ON obj.ID = it.RefItemID
JOIN SRO_VT_SHARD.._RefObjItem oit
ON oit.ID = obj.Link
OUTER APPLY (
SELECT SUM(CASE WHEN bwo.nOptValue > 0 THEN bwo.nOptValue ELSE 0 END) AS SumAdv
FROM SRO_VT_SHARD.._BindingOptionWithItem bwo
WHERE bwo.nItemDBID = ei.ItemID
AND bwo.bOptType = 2
) adv
),
CharItemPoints AS (
SELECT
GuildID,
CharID,
SUM(ItemPointsRow) AS ItemPoints
FROM ItemPointRows
GROUP BY GuildID, CharID
),
GuildPoints AS (
SELECT
GuildID,
SUM(ItemPoints) AS GuildItemPoints,
COUNT(*) AS MemberCountScored
FROM CharItemPoints
GROUP BY GuildID
)
SELECT TOP (20)
gu.Name AS Guild_Name,
gp.GuildItemPoints,
gp.MemberCountScored
FROM GuildPoints gp
JOIN SRO_VT_SHARD.._Guild gu
ON gu.ID = gp.GuildID
WHERE gu.Name IS NOT NULL
AND gu.Name <> ''
AND gu.Name <> 'dummy'
ORDER BY gp.GuildItemPoints DESC, gp.MemberCountScored DESC, gu.Name ASC;
Ekli dosyalar
![SRO[2026-01-06 02-02-09]_18.webp](/data/attachments/36/36150-a012718fc439825a45aa4d6624533ed4.jpg?hash=3h7cZpD9Ni){kind=small}
Oyla
0
kontrol edip denedim bu sorgu da askıda kalıyor maalesef
tek üzüldüğüm nokta 2 gündür bunu çözememiş olmam
Üst üste post gönderildiği için tek mesajda birleştirildi:
tek üzüldüğüm nokta 2 gündür bunu çözememiş olmam
Oyla
0
Boşmu gösteriyorkontrol edip denedim bu sorgu da askıda kalıyor maalesef
Üst üste post gönderildiği için tek mesajda birleştirildi:
tek üzüldüğüm nokta 2 gündür bunu çözememiş olmam
Oyla
0
