多表综合查询之 -- 生物(怪物)掉落

生物(怪物)掉落；
已知 “生物entry” 查询 其 较详细的掉落信息（掉落物品信息、掉率、数量、掉落条件等）。

PS：想查询较详细的掉落信息会涉及多个数据表；
creature_loot_template ：生物(怪物)掉落表
reference_loot_template ：参考(辅助)掉落表
item_template ：物品表（查询物品名、等级等信息）
conditions ：条件表（查询物品受限掉落条件信息）

查询环境：XYWOW 3.3.5 / AyaseCore 2018.12.12 ，Navicat Premium V11

举例；
      如想 “36597-巫妖王”（10人普通） 的掉落信息，即 “怪物id” = 36597 ；
1、用 “Navicat” 连接并打开 “world” 数据库
2、按快捷键 “F6” 打开一个新的“命令行介面”（在 “查询” → “新建查询” → “查询编辑器” 窗口下复制也可以）
3、复制下面的查询语句至打开的“命令行介面”中，先别执行（别按 “Enter” 键）
4、将查询语句开头的“SET @cloot=36597” 的数字部分修改为想要查询的那个 “怪物id”（比如 36597），然后再执行(运行)

生物(怪物)掉落 查询语句；

/* 注：先设置“@cloot=怪物id”接着复制下面所有内容并粘贴至 Navicat 的“命令行介面”中执行。*/

SET @cloot=36597;

SELECT @cloot;
SELECT Distinct
creature_loot_template.entry,
creature_template.name AS 名称,
creature_template.maxlevel AS 等级,
creature_template.difficulty_entry_1 AS 5H或25PT,
creature_template.difficulty_entry_2 AS 10H,
creature_template.difficulty_entry_3 AS 25H,
creature_template.lootid AS 掉落id
FROM creature_loot_template,creature_template,creature
WHERE creature_loot_template.entry=@cloot AND creature_template.entry IN(creature_loot_template.entry) AND creature.id=@cloot;

SELECT
creature_loot_template.entry,
creature_loot_template.item AS 物品编号,
item_template.name AS 物品名,
item_template.itemlevel AS 物品等级,
creature_loot_template.ChanceOrQuestChance AS 掉率,
creature_loot_template.GroupId AS 掉落组,
creature_loot_template.mincountOrRef AS 最小掉落数,
creature_loot_template.MaxCount AS 最大掉落数
FROM creature_loot_template,item_template
WHERE creature_loot_template.entry=@cloot AND creature_loot_template.mincountOrRef>0 AND item_template.entry IN(creature_loot_template.item) ORDER BY creature_loot_template.GroupId ASC,creature_loot_template.ChanceOrQuestChance DESC;

SELECT Distinct
SourceTypeOrReferenceId AS 掉落条件来源类型,
SourceGroup AS 受限掉落id,
SourceEntry AS 受限物品id,
ConditionTypeOrReference AS CT条件类型,
ConditionValue1 AS 参数1,
ConditionValue2 AS 参数2,
ConditionValue3 AS 参数3,
IF(NegativeCondition=0,"是","否") AS 否定条件,
Comment AS "(ST<=12)受限掉落_注释"
FROM conditions  
WHERE conditions.SourceTypeOrReferenceId<=12 AND conditions.SourceGroup=@cloot ORDER BY conditions.ConditionTypeOrReference ASC;

SELECT
entry,item," ---- "," -- ",ChanceOrQuestChance AS 掉率,
GroupId 掉落组,
mincountOrRef AS 参考掉落id,
MaxCount AS 最大掉落数
FROM creature_loot_template
WHERE creature_loot_template.entry=@cloot AND creature_loot_template.mincountOrRef<0 ORDER BY creature_loot_template.mincountOrRef DESC,creature_loot_template.GroupId ASC;

SELECT
reference_loot_template.entry AS 参考掉落id,
reference_loot_template.item AS 物品编号,
item_template.name AS 物品名,
item_template.itemlevel AS 物品等级,
reference_loot_template.ChanceOrQuestChance AS 掉率,
reference_loot_template.GroupId AS 掉落组,
reference_loot_template.mincountOrRef AS 最小掉落数,
reference_loot_template.MaxCount AS 最大掉落数
FROM creature_loot_template,reference_loot_template,item_template
WHERE creature_loot_template.entry =@cloot AND creature_loot_template.mincountOrRef<0 AND reference_loot_template.entry IN(ABS(creature_loot_template.mincountOrRef))AND reference_loot_template.mincountOrRef>0 AND item_template.entry IN(reference_loot_template.item) ORDER BY reference_loot_template.entry ASC,reference_loot_template.GroupId ASC,reference_loot_template.mincountOrRef DESC;

SELECT
reference_loot_template.entry AS 参考掉落id,
reference_loot_template.item," ---- "," -- ",
reference_loot_template.ChanceOrQuestChance AS 掉率,
reference_loot_template.GroupId AS 掉落组,
reference_loot_template.mincountOrRef AS 参考掉落id,
reference_loot_template.MaxCount AS 最大掉落数
FROM creature_loot_template,reference_loot_template
WHERE creature_loot_template.entry =@cloot AND creature_loot_template.mincountOrRef<0 AND reference_loot_template.entry IN(ABS(creature_loot_template.mincountOrRef)) AND reference_loot_template.mincountOrRef<0 ORDER BY reference_loot_template.entry ASC,reference_loot_template.GroupId ASC,reference_loot_template.mincountOrRef DESC;


 以上的查询语句，可能会有漏洞或不严谨的地方、效率也不高，^_^ 勉强还能用，有意见随时可以提