-- Создаем таблички
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
