Как написать sql запрос в котором возможно придется транспонировать данные в таблице?

Question

[Карпов Иван]

В БД одной mmorpg есть три таблицы:
Игроки: players:
NUMBER id,
VARCHAR2 email

Персонажи: CHARACTERS:
NUMBER id,
NUMBER player_id,
NUMBER class_id
VARCHAR2 character_name
NUMBER character_level

Классы персонажей: classes:
NUMBER id,
VARCHAR2 class_name

Как найти количество игроков, у которых есть персонаж класса "Priest"?

3). Пусть в предыдущей mmorpg один игрок не может иметь более одного персонажа каждого класса. Список классов, допустим, известен: Priest, Mage, Warrior, Rogue.
Нужно сделать выборку email'ов игроков и имён их персонажей с разбивкой по классам, например

email Priest Mage Warrior Rogue
------------------------------------------------------------------------
[e-mail] Zinin Nafig Polzie
[e-mail] Neo Demon Ironman Joker

Answer 1

[igruschkafox]

-- Создаем таблички

USE [Test]

CREATE TABLE [players]
(id INT IDENTITY(1,1),
email NVARCHAR(100))

CREATE TABLE [classes]
(id INT IDENTITY(1,1),
class_name NVARCHAR(100))

CREATE TABLE [CHARACTERS]
(
id INT IDENTITY(1,1),
player_id INT ,
class_id INT,
character_name nvarchar(100),
character_level INT)


-- Заполняем данными

USE [Test]
GO

TRUNCATE TABLE [dbo].[players]
INSERT INTO [dbo].[players] ([email]) VALUES      ('Вася@mail.ru')
INSERT INTO [dbo].[players] ([email]) VALUES      ('Петя@mail.ru')
INSERT INTO [dbo].[players] ([email]) VALUES      ('Задрот@mail.ru')
INSERT INTO [dbo].[players] ([email]) VALUES      ('Бот@mail.ru')


TRUNCATE TABLE [dbo].[classes]
INSERT INTO [dbo].[classes] ([class_name]) VALUES ('Priest')
INSERT INTO [dbo].[classes] ([class_name]) VALUES ('Mage')
INSERT INTO [dbo].[classes] ([class_name]) VALUES ('Warrior')
INSERT INTO [dbo].[classes] ([class_name]) VALUES ('Rogue')


TRUNCATE TABLE [dbo].[CHARACTERS]

-- Первому игроку создаем одного персонажа
INSERT INTO [dbo].[CHARACTERS] ([player_id],[class_id],[character_name],[character_level])VALUES(1,1,'Вася_Priest',12)

-- Второму игроку создаем двух персонажей
INSERT INTO [dbo].[CHARACTERS]([player_id],[class_id],[character_name],[character_level]) VALUES(2,1,'Петя_Priest',10)
INSERT INTO [dbo].[CHARACTERS]([player_id],[class_id],[character_name],[character_level]) VALUES(2,2,'Петя_Mage',11)

-- Третий игрок - три персонажа
INSERT INTO [dbo].[CHARACTERS]([player_id],[class_id],[character_name],[character_level]) VALUES(3,1,'Задрот_Priest',21)
INSERT INTO [dbo].[CHARACTERS]([player_id],[class_id],[character_name],[character_level]) VALUES(3,2,'Задрот_Mage',22)
INSERT INTO [dbo].[CHARACTERS]([player_id],[class_id],[character_name],[character_level]) VALUES(3,3,'Задрот_Warrior',23)

-- Четвертый игрок
INSERT INTO [dbo].[CHARACTERS]([player_id],[class_id],[character_name],[character_level]) VALUES(4,3,'Бот_Warrior',71)
INSERT INTO [dbo].[CHARACTERS]([player_id],[class_id],[character_name],[character_level]) VALUES(4,2,'Бот_Mage',72)
INSERT INTO [dbo].[CHARACTERS]([player_id],[class_id],[character_name],[character_level]) VALUES(4,3,'Бот_Warrior2',73)
INSERT INTO [dbo].[CHARACTERS]([player_id],[class_id],[character_name],[character_level]) VALUES(4,4,'Бот_Rogue',74)
INSERT INTO [dbo].[CHARACTERS]([player_id],[class_id],[character_name],[character_level]) VALUES(4,4,'Бот_Rogue2',85)



-- 1.Находим количество игроков у которых есть персонаж Priest

SELECT 
tmp.[Название класса] AS 'Название класса'
,COUNT (DISTINCT tmp.[ID игрока]) AS 'Количество игроков с искомым классом'
FROM (
SELECT cl.class_name AS 'Название класса'
,pl.id AS 'ID игрока'
, count (cl.id) OVER (PARTITION BY pl.id ORDER BY pl.id ) AS 'Количество персонажей у игрока'
 FROM players pl
JOIN CHARACTERS ch ON pl.id=ch.player_id
JOIN classes cl ON cl.id=ch.class_id

WHERE cl.class_name LIKE 'Priest' -- если закоментить эту строку то можно определить сколько игроков каждого класса 
) tmp

GROUP BY tmp.[Название класса]


-- 2. Выборка персонажей по емейлу

SELECT DISTINCT pl.email,
(
			SELECT 
			cl1.[class_name]  + ' ' AS [text()]
			FROM players pl1
			JOIN CHARACTERS ch1 ON pl1.id=ch1.player_id
			JOIN classes cl1 ON cl1.id=ch1.class_id
			WHERE pl.id=pl1.id
            For XML PATH ('')
        ) AS 'Классы игрока'
			FROM players pl
JOIN CHARACTERS ch ON pl.id=ch.player_id
JOIN classes cl ON cl.id=ch.class_id

Answer 2

[Дмитрий Ковальский]

В идеале у вас должен быть кешированный словарь классов с их идентификаторами и будет делаться банальнейший SELECT с WHERE по class_id. Если нету, то делается JOIN вида

SELECT id, player_id, class_id ,character_name ,character_level FROM Characters as c
INNER JOIN classes as cl ON cl.id = c.class_id
WHERE condition

Для третьего пункта в идеале нужна темповая таблица, хранящая необходимые классы и делается JOIN типа

SELECT id, player_id, class_id ,character_name ,character_level FROM Characters as c
INNER JOIN classes as cl ON cl.id = c.class_id
INNER JOIN #temp as t ON cl.class_name = t.value
WHERE condition

Answer 3

[Карпов Иван]

Если без временных таблиц? Придерживаясь структуры, таблицы на выходе именно указанной в задании,чтобы классы были отдельными столбцами для каждого емеила. Как это сделать?

Answer 4

[Марат]

В таком виде не подойдет ?

mysql> select email, group_concat(concat(character_name, ':', 
class_name, ' ',character_level,' lvl') separator ', ') as Chars 
from characters,players,classes 
where player_id=players.id and class_id=classes.id group by player_id;
+--------+--------------------------------------------+
| email  | Chars                                      |
+--------+--------------------------------------------+
| [e-mail] | Merlin:wizard 70 lvl, Conan:warrior 80 lvl |
| [e-mail] | Azariel:warrior 50 lvl, Bilbo:thief 20 lvl |
+--------+--------------------------------------------+
2 rows in set (0.00 sec)

в ORacle вместо group_concat используйте LISTAGG

Если надо отдельно в каждой колонке выводить по отдельному классу, то измените select добавив через запятую выражение аналогично выводу для Сhars, но с использованием, например , CASE где сравниваете через WHEN с требуемым классом.

Answer 5

[nozzy]

Попробуйте с PIVOT, не проверял:

select *
from
(
	select 
		p.email,
		cr.character_name,
		cs.class_name
	from players p
	inner join characters cr on cr.player_id = p.id
	inner join classes cs on cs.id = cr.class_id
)
pivot
(      
 MAX(character_name)
	for class_name in ('Priest' as 	"Priest", 
								'Mage' as 	"Mage", 
								'Warrior' as "Warrior", 
								'Rogue' as "Rogue")
)

И с DECODE:

select 
    p.email,
 decode(class_name, 'Priest', character_name, null)  as "Priest",
 decode(class_name, 'Mage', character_name, null)  as "Mage",
 decode(class_name, 'Warrior', character_name, null)  as "Warrior",
 decode(class_name, 'Rogue', character_name, null)  as "Rogue"
  from players p
  inner join characters cr on cr.player_id = p.id
  inner join classes cs on cs.id = cr.class_id

Answer 6

[ApeCoder]

1) Как найти количество игроков, у которых есть персонаж класса "Priest"?

Тупо переписать эту фразу на SQL :

Select * from player where exists (select * from character inter join class on character class_id = class_id where character. Player_id=player.id)

2) тут надо знать каким образом известны классы это могут быть их имена или настроенная таблица с их id варианты - либо pivot либо n подзпапросов либо просто сгруппировать таблицу characters по игрокам и классам и транспонировать при визуализации