T-SQL til MySQL - avanceret
HejJeg har fået udviklet dette script til MSSQL. Jeg skal bruge det i MySQL, men hverken ham der har lavet det eller undertegnede kan omskrive det til MySQL. Blandt andet pga RANK funktionen. Jeg vil gerne bede om hjælp til at få skrevet det om.
/* ==Scripting Parameters==
Source Server Version : SQL Server 2016 (13.0.1742)
Source Database Engine Edition : Microsoft SQL Server Enterprise Edition
Source Database Engine Type : Standalone SQL Server
Target Server Version : SQL Server 2016
Target Database Engine Edition : Microsoft SQL Server Enterprise Edition
Target Database Engine Type : Standalone SQL Server
*/
USE [KRC]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[PointArtTotal] AS
SELECT teamsum.BaadNr, teamsum.TeamNavn, SUM(teamsum.Point) AS [Point], RANK() OVER(ORDER BY SUM(teamsum.Point) DESC) AS [PlaceringAlle]
FROM ( SELECT i.ID, t.TeamNavn, Dag, a.ArtNavn, i.Vaegt, CAST((i.Vaegt/a.Sats*100) AS DECIMAL(10,0)) AS [Point], t.BaadNr
FROM dbo.indvejning i,
dbo.arter a,
dbo.teams t,
( SELECT BaadNr, ArtID, max(vaegt) as maxvaegt
FROM dbo.indvejning
GROUP BY BaadNr, ArtID) maxIndvejning
WHERE i.baadnr = t.baadnr
AND i.artid = a.id
AND i.Baadnr = maxIndvejning.Baadnr
AND i.Vaegt = maxIndvejning.maxvaegt
AND i.ArtID = maxIndvejning.ArtID
) teamsum
GROUP BY teamsum.BaadNr, teamsum.teamnavn
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[PointFladeTotal] AS
SELECT teamsum.BaadNr, teamsum.TeamNavn, SUM(teamsum.Point) AS [Point], RANK() OVER(ORDER BY SUM(teamsum.Point) DESC) AS [PlaceringAlle]
FROM ( SELECT i.ID, t.TeamNavn, Dag, a.ArtNavn, i.Vaegt, CAST((i.Vaegt/a.Sats*100) AS DECIMAL(10,0)) AS [Point], t.BaadNr
FROM dbo.indvejning i,
dbo.arter a,
dbo.teams t,
( SELECT BaadNr, ArtID, max(vaegt) as maxvaegt
FROM dbo.indvejning
GROUP BY BaadNr, ArtID) maxIndvejning
WHERE i.baadnr = t.baadnr
AND i.artid = a.id
AND a.ArtNavn IN( 'Ising','Rødspætte','Skrubbe')
AND i.Baadnr = maxIndvejning.Baadnr
AND i.Vaegt = maxIndvejning.maxvaegt
AND i.ArtID = maxIndvejning.ArtID
) teamsum
GROUP BY teamsum.BaadNr, teamsum.teamnavn
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[MaxPointTeam] AS
SELECT t.BaadNr, t.TeamNavn, CAST(MAX(i.Vaegt/a.Sats*100) AS DECIMAL(3,0)) AS [MaxPoint]
FROM [dbo].[arter] a,
[dbo].[indvejning] i,
[dbo].[teams] t
WHERE i.Baadnr = t.BaadNr
AND i.ArtID = a.ID
GROUP BY t.BaadNr, t.TeamNavn
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Placering i Flade
CREATE VIEW [dbo].[PlaceringFladeEndelig] AS
SELECT ft.BaadNr, ft.TeamNavn, ft.Point, ft.PlaceringAlle AS [PlaceringAlleFlade],RANK() OVER(ORDER BY ft.PlaceringAlle ASC, mp.MaxPoint DESC) AS [PlaceringFladeEndelig]
FROM dbo.PointFladeTotal ft,
dbo.MaxPointTeam mp
WHERE NOT EXISTS( SELECT f.*
FROM dbo.PointArtTotal a,
dbo.PointFladeTotal f
WHERE a.teamnavn = f.teamnavn
AND a.PlaceringAlle < f.PlaceringAlle
AND f.teamnavn = ft.teamnavn
)
AND ft.BaadNr = mp.BaadNr
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Placering i Art
CREATE VIEW [dbo].[PlaceringArtEndelig] AS
SELECT at.BaadNr, at.TeamNavn, at.Point, at.PlaceringAlle AS [PlaceringAlleArt], RANK() OVER(ORDER BY at.PlaceringAlle ASC, mp.MaxPoint DESC) AS [PlaceringArtEndelig]
FROM dbo.PointArtTotal at,
dbo.MaxPointTeam mp
WHERE NOT EXISTS( SELECT a.*
FROM dbo.PointArtTotal a,
dbo.PlaceringFladeEndelig f
WHERE a.teamnavn = f.teamnavn
AND f.[PlaceringFladeEndelig] < a.PlaceringAlle
AND a.teamnavn = at.teamnavn
)
AND mp.BaadNr = at.BaadNr
GO
