SQLize Online / PHPize Online  /  SQLtest Online

-- Создание таблицы Players CREATE TABLE Players ( PlayerID INT PRIMARY KEY IDENTITY, LastName NVARCHAR(50), FirstName NVARCHAR(50), DateOfBirth DATE, ClubsPlayedFor NVARCHAR(MAX), -- JSON строка для хранения названий клубов и сезонов GoalsScored INT DEFAULT 0 ); GO -- Вставка данных в таблицу INSERT INTO Players (LastName, FirstName, DateOfBirth, ClubsPlayedFor, GoalsScored) VALUES ('Ivanov', 'Ivan', '1990-01-15', '{"clubs": ["Spartak", "Zenit"], "seasons": ["2017-18", "2018-19"]}', 10), ('Petrov', 'Pyotr', '1989-02-25', '{"clubs": ["Dynamo", "Lokomotiv"], "seasons": ["2016-17", "2017-18"]}', 12), ('Sidorov', 'Sergey', '1991-03-30', '{"clubs": ["CSKA", "Rubin"], "seasons": ["2014-15", "2015-16"]}', 14), ('Fedorov', 'Fyodor', '1992-04-05', '{"clubs": ["Krasnodar", "Akhat"], "seasons": ["2013-14", "2014-15"]}', 11), ('Kozlov', 'Konstantin', '1993-06-07', '{"clubs": ["Rostov", "Ural"], "seasons": ["2012-13", "2013-14"]}', 9); GO -- Проверка содержимого таблицы SELECT * FROM Players; GO -- Сортировка списка игроков по дате рождения SELECT * FROM Players ORDER BY DateOfBirth ASC; GO -- Создание функции для извлечения клубов и сезонов CREATE FUNCTION dbo.GetClubsAndSeasons(@json NVARCHAR(MAX)) RETURNS @result TABLE (Club NVARCHAR(100), Season NVARCHAR(20)) AS BEGIN DECLARE @i INT = 1; DECLARE @len INT = LEN(@json); WHILE (@i <= @len) BEGIN IF SUBSTRING(@json, @i, 1) = '"' BEGIN SET @i += 1; DECLARE @start INT = @i; WHILE SUBSTRING(@json, @i, 1) <> '"' AND @i <= @len SET @i += 1; INSERT INTO @result VALUES(SUBSTRING(@json, @start, @i - @start)); END SET @i += 1; END RETURN; END; GO -- Создание процедуры для поиска игроков, игравших против указанного игрока CREATE PROCEDURE FindOpponentsByPlayerLastName @lastName NVARCHAR(50) AS BEGIN DECLARE @clubs TABLE (Club NVARCHAR(100), Season NVARCHAR(20)); DECLARE @playerId INT; SELECT @playerId = PlayerID FROM Players WHERE LastName = @lastName; INSERT INTO @clubs SELECT Club, Season FROM Players p CROSS APPLY dbo.GetClubsAndSeasons(p.ClubsPlayedFor) c WHERE p.PlayerID = @playerId; SELECT DISTINCT p.* FROM Players p JOIN @clubs c ON EXISTS( SELECT 1 FROM dbo.GetClubsAndSeasons(p.ClubsPlayedFor) cp WHERE cp.Club = c.Club AND cp.Season = c.Season AND p.PlayerID != @playerId ); END; GO -- Вызов процедуры для поиска игроков, игравших против вводимого игрока EXEC FindOpponentsByPlayerLastName 'Ivanov'; GO -- Подсчёт количества игроков, игравших в разных сезонах в одном клубе WITH ClubSeasonCounts AS ( SELECT PlayerID, Club, COUNT(DISTINCT Season) AS SeasonCount FROM Players CROSS APPLY dbo.GetClubsAndSeasons(ClubsPlayedFor) GROUP BY PlayerID, Club ) SELECT COUNT(*) FROM ClubSeasonCounts WHERE SeasonCount > 1; GO