diff options
| author | schneefux <schneefux+commit@schneefux.xyz> | 2017-05-21 13:25:22 +0200 |
|---|---|---|
| committer | schneefux <schneefux+commit@schneefux.xyz> | 2017-05-21 13:25:22 +0200 |
| commit | 8b0da6d25c9130c87455ffdbb26af5e75d44b5af (patch) | |
| tree | 8d847e20b57df34881c482c8eb21a4d5f88369f4 /recommend/data/get.sql | |
| parent | 5a0b7abcaac0d908da8cf9a51f16c94cc54fbd1e (diff) | |
| download | analyzer-release/2.4.0.tar.gz analyzer-release/2.4.0.zip | |
Diffstat (limited to 'recommend/data/get.sql')
| -rw-r--r-- | recommend/data/get.sql | 16 |
1 files changed, 16 insertions, 0 deletions
diff --git a/recommend/data/get.sql b/recommend/data/get.sql new file mode 100644 index 0000000..2213ab1 --- /dev/null +++ b/recommend/data/get.sql @@ -0,0 +1,16 @@ +SELECT +hero.name, +role.name, +(SELECT name from item WHERE id=(SELECT SUBSTRING_INDEX(items, ',', 1))) AS item_1, +(SELECT name from item WHERE id=(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(items, ',', 2), ',', -1))) AS item_2, +(SELECT name from item WHERE id=(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(items, ',', 3), ',', -1))) AS item_3, +(SELECT name from item WHERE id=(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(items, ',', 4), ',', -1))) AS item_4, +(SELECT name from item WHERE id=(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(items, ',', 5), ',', -1))) AS item_5, +(SELECT name from item WHERE id=(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(items, ',', 6), ',', -1))) AS item_6, +COUNT(*) AS cnt +FROM participant +JOIN participant_stats ON participant_stats.participant_api_id=participant.api_id +JOIN role ON participant.role_id=role.id +JOIN hero ON participant.hero_id=hero.id +GROUP BY hero.name, role.name, item_1, item_2, item_3, item_4, item_5, item_6 +ORDER BY cnt DESC |