SQL-fråga med CROSS APPLY
APPLY (msdn) är ett nytt SQL-kommando i SQL Server från och med version 2005 och finns i två olika varianter, CROSS APPLY och OUTER APPLY.
Kan inte säga att jag förstått dessa funktionerna helt, men jag har hittat ett bra användningsområde för CROSS APPLY. Nämligen att kunna bygga upp VIEWs med data från en relationstabell och sammanfoga dessa i en ny kolumn separerade med valfritt tecken. Detta kallas tydligen "aggregate concatenation".
Läste även att denna metod går emot huvudideén för hur en relationsdatabas är tänkt att fungera, så har du ett behov att göra såna här SQL-frågor kan det vara snart att tänka om ifall det går att göra på ett bättre sätt. Eller kanske hämta ut datan separat och bygga ihop vyn i buisness-lagret.
Kommer här visa hur det kan fungera genom ett exempel, här först databasdiagrammet.
Sen ett skript (skapat med SubSonic) som bygger upp hela databasen:
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Products]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Products](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ProductName] [nvarchar](150) COLLATE Finnish_Swedish_CI_AS NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
/****** Object: Table [dbo].[Colors] Script Date: 07/07/2008 16:58:21 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Colors]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Colors](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ColorName] [nvarchar](150) COLLATE Finnish_Swedish_CI_AS NULL,
CONSTRAINT [PK_Colors] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
/****** Object: Table [dbo].[ProductColor] Script Date: 07/07/2008 16:58:21 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProductColor]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ProductColor](
[ProductId] [int] NOT NULL,
[ColorId] [int] NOT NULL,
CONSTRAINT [PK_ProductColor] PRIMARY KEY CLUSTERED
(
[ProductId] ASC,
[ColorId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
/****** Object: View [dbo].[ListOfColorsView] Script Date: 07/07/2008 16:58:21 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[ListOfColorsView]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW dbo.ListOfColorsView
AS
SELECT DISTINCT ProductName, ListOfColors = LEFT(o.list, LEN(o.list) - 1)
FROM Products CROSS APPLY
(SELECT CONVERT(VARCHAR(12), ColorName) + '','' AS [text()]
FROM dbo.Colors c INNER JOIN
dbo.ProductColor ON c.Id = dbo.ProductColor.ColorId
WHERE (dbo.ProductColor.ProductId = dbo.Products.Id)
ORDER BY c.ColorName FOR XML PATH('''')) o(list)
'
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ProductColor_Colors]') AND parent_object_id = OBJECT_ID(N'[dbo].[ProductColor]'))
ALTER TABLE [dbo].[ProductColor] WITH CHECK ADD CONSTRAINT [FK_ProductColor_Colors] FOREIGN KEY([ColorId])
REFERENCES [Colors] ([Id])
ALTER TABLE [dbo].[ProductColor] CHECK CONSTRAINT [FK_ProductColor_Colors]
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ProductColor_Products]') AND parent_object_id = OBJECT_ID(N'[dbo].[ProductColor]'))
ALTER TABLE [dbo].[ProductColor] WITH CHECK ADD CONSTRAINT [FK_ProductColor_Products] FOREIGN KEY([ProductId])
REFERENCES [Products] ([Id])
ALTER TABLE [dbo].[ProductColor] CHECK CONSTRAINT [FK_ProductColor_Products]
Och lite testdata:
GO
ALTER TABLE [Colors] DISABLE TRIGGER ALL
GO
SET IDENTITY_INSERT [Colors] ON
PRINT 'Begin inserting data in Colors'
INSERT INTO [Colors] ([Id], [ColorName])
VALUES(1, 'Red')
INSERT INTO [Colors] ([Id], [ColorName])
VALUES(2, 'Blue')
INSERT INTO [Colors] ([Id], [ColorName])
VALUES(3, 'Green')
SET IDENTITY_INSERT [Colors] OFF
ALTER TABLE [Colors] CHECK CONSTRAINT ALL
GO
ALTER TABLE [Colors] ENABLE TRIGGER ALL
GO
ALTER TABLE [Products] NOCHECK CONSTRAINT ALL
GO
ALTER TABLE [Products] DISABLE TRIGGER ALL
GO
SET IDENTITY_INSERT [Products] ON
PRINT 'Begin inserting data in Products'
INSERT INTO [Products] ([Id], [ProductName])
VALUES(1, 'Test product 1')
INSERT INTO [Products] ([Id], [ProductName])
VALUES(2, 'Test product 2 - No Colors')
SET IDENTITY_INSERT [Products] OFF
ALTER TABLE [Products] CHECK CONSTRAINT ALL
GO
ALTER TABLE [Products] ENABLE TRIGGER ALL
GO
ALTER TABLE [ProductColor] NOCHECK CONSTRAINT ALL
GO
ALTER TABLE [ProductColor] DISABLE TRIGGER ALL
GO
PRINT 'Begin inserting data in ProductColor'
INSERT INTO [ProductColor] ([ProductId], [ColorId])
VALUES(1, 1)
INSERT INTO [ProductColor] ([ProductId], [ColorId])
VALUES(1, 2)
INSERT INTO [ProductColor] ([ProductId], [ColorId])
VALUES(1, 3)
ALTER TABLE [ProductColor] CHECK CONSTRAINT ALL
GO
ALTER TABLE [ProductColor] ENABLE TRIGGER ALL
GO
Det mest intressanta är ju själva vyn som separerar datan från relationstabellen med ett kommatecken mellan varje värde.
FROM Products CROSS APPLY
(SELECT CONVERT(VARCHAR(12), ColorName) + ',' AS [text()]
FROM dbo.Colors c INNER JOIN
dbo.ProductColor ON c.Id = dbo.ProductColor.ColorId
WHERE (dbo.ProductColor.ProductId = dbo.Products.Id)
ORDER BY c.ColorName FOR XML PATH('')) o(list)
Svaret från denna vyn ser ut så här:
Test product 1 Blue,Green,Red
Test product 2 - No Colors NULL
Mer läsning:
http://zulfiqar.typepad.com/zulfiqars_web/2005/04/tsql_concatenat.html
http://blog.sqlauthority.com/2008/01/04/sql-server-2005-cross-apply/
Comments
Alex Wrote:
29:e Juli 2008
Tags