fakhravari
دوشنبه 26 خرداد 1393, 22:21 عصر
داش علی شما نمونت ضمیمه کن مرسی.
خودم یه چیزی سر هم کردم خروجی درست میده اما روش حساب نمیکنم یکمی ناهماهنگ اما خروجی خوب میده
CREATE TABLE [dbo].[Constructor]( [ID] [int] IDENTITY(1,1) NOT NULL,
[ConstructorName] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Constructor] 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]
GO
SET IDENTITY_INSERT [dbo].[Constructor] ON
INSERT [dbo].[Constructor] ([ID], [ConstructorName]) VALUES (1, N'Intel')
INSERT [dbo].[Constructor] ([ID], [ConstructorName]) VALUES (2, N'Gigabyte')
INSERT [dbo].[Constructor] ([ID], [ConstructorName]) VALUES (3, N'Asus')
INSERT [dbo].[Constructor] ([ID], [ConstructorName]) VALUES (4, N'King Mastor')
INSERT [dbo].[Constructor] ([ID], [ConstructorName]) VALUES (5, N'AMD')
INSERT [dbo].[Constructor] ([ID], [ConstructorName]) VALUES (6, N'LG')
INSERT [dbo].[Constructor] ([ID], [ConstructorName]) VALUES (7, N'Samsoung')
INSERT [dbo].[Constructor] ([ID], [ConstructorName]) VALUES (8, N'NVIDIA')
SET IDENTITY_INSERT [dbo].[Constructor] OFF
/****** Object: Table [dbo].[Section] Script Date: 06/16/2014 21:53:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Section](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SectionName] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Section] 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]
GO
SET IDENTITY_INSERT [dbo].[Section] ON
INSERT [dbo].[Section] ([ID], [SectionName]) VALUES (1, N'MB')
INSERT [dbo].[Section] ([ID], [SectionName]) VALUES (2, N'VGA')
INSERT [dbo].[Section] ([ID], [SectionName]) VALUES (3, N'CPU')
INSERT [dbo].[Section] ([ID], [SectionName]) VALUES (4, N'RAM')
INSERT [dbo].[Section] ([ID], [SectionName]) VALUES (5, N'Sound Card')
INSERT [dbo].[Section] ([ID], [SectionName]) VALUES (6, N'LAN')
INSERT [dbo].[Section] ([ID], [SectionName]) VALUES (7, N'Manitor')
INSERT [dbo].[Section] ([ID], [SectionName]) VALUES (8, N'HDD')
SET IDENTITY_INSERT [dbo].[Section] OFF
/****** Object: Table [dbo].[Model] Script Date: 06/16/2014 21:53:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Model](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SectionID] [int] NOT NULL,
[ModelName] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Model] 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]
GO
SET IDENTITY_INSERT [dbo].[Model] ON
INSERT [dbo].[Model] ([ID], [SectionID], [ModelName]) VALUES (1, 4, N'DDR')
INSERT [dbo].[Model] ([ID], [SectionID], [ModelName]) VALUES (2, 4, N'DDR2')
INSERT [dbo].[Model] ([ID], [SectionID], [ModelName]) VALUES (3, 4, N'Normal')
INSERT [dbo].[Model] ([ID], [SectionID], [ModelName]) VALUES (4, 5, N'Relatek')
INSERT [dbo].[Model] ([ID], [SectionID], [ModelName]) VALUES (5, 5, N'Onboard')
INSERT [dbo].[Model] ([ID], [SectionID], [ModelName]) VALUES (6, 6, N'دارد')
INSERT [dbo].[Model] ([ID], [SectionID], [ModelName]) VALUES (7, 3, N'Core i7')
INSERT [dbo].[Model] ([ID], [SectionID], [ModelName]) VALUES (8, 7, N'Flatron')
INSERT [dbo].[Model] ([ID], [SectionID], [ModelName]) VALUES (9, 1, N'X M.B')
INSERT [dbo].[Model] ([ID], [SectionID], [ModelName]) VALUES (10, 2, N'GeForce GTS 450')
SET IDENTITY_INSERT [dbo].[Model] OFF
/****** Object: Table [dbo].[Capacity] Script Date: 06/16/2014 21:53:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Capacity](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SectionID] [int] NOT NULL,
[CapacityName] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Capacity] 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]
GO
SET IDENTITY_INSERT [dbo].[Capacity] ON
INSERT [dbo].[Capacity] ([ID], [SectionID], [CapacityName]) VALUES (1, 3, N'2.2')
INSERT [dbo].[Capacity] ([ID], [SectionID], [CapacityName]) VALUES (2, 7, N'17')
INSERT [dbo].[Capacity] ([ID], [SectionID], [CapacityName]) VALUES (3, 7, N'14')
INSERT [dbo].[Capacity] ([ID], [SectionID], [CapacityName]) VALUES (4, 4, N'128 MB')
INSERT [dbo].[Capacity] ([ID], [SectionID], [CapacityName]) VALUES (5, 4, N'256 MB')
INSERT [dbo].[Capacity] ([ID], [SectionID], [CapacityName]) VALUES (6, 4, N'512 MB')
INSERT [dbo].[Capacity] ([ID], [SectionID], [CapacityName]) VALUES (7, 4, N'1 GB')
INSERT [dbo].[Capacity] ([ID], [SectionID], [CapacityName]) VALUES (8, 3, N'2.5')
INSERT [dbo].[Capacity] ([ID], [SectionID], [CapacityName]) VALUES (9, 3, N'2')
INSERT [dbo].[Capacity] ([ID], [SectionID], [CapacityName]) VALUES (10, 8, N'300 GB')
INSERT [dbo].[Capacity] ([ID], [SectionID], [CapacityName]) VALUES (11, 2, N'1 GB')
INSERT [dbo].[Capacity] ([ID], [SectionID], [CapacityName]) VALUES (13, 2, N'128 MB')
INSERT [dbo].[Capacity] ([ID], [SectionID], [CapacityName]) VALUES (14, 2, N'256 MB')
INSERT [dbo].[Capacity] ([ID], [SectionID], [CapacityName]) VALUES (15, 2, N'512 MB')
SET IDENTITY_INSERT [dbo].[Capacity] OFF
/****** Object: Table [dbo].[System] Script Date: 06/16/2014 21:53:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[System](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ModelID] [int] NULL,
[ConstructorID] [int] NULL,
[CapacityID] [int] NULL,
[SysID] [nvarchar](10) NOT NULL,
CONSTRAINT [PK_System] 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]
GO
SET IDENTITY_INSERT [dbo].[System] ON
INSERT [dbo].[System] ([ID], [ModelID], [ConstructorID], [CapacityID], [SysID]) VALUES (1, 2, 1, 7, N'SYS001')
INSERT [dbo].[System] ([ID], [ModelID], [ConstructorID], [CapacityID], [SysID]) VALUES (2, 6, NULL, NULL, N'SYS001')
INSERT [dbo].[System] ([ID], [ModelID], [ConstructorID], [CapacityID], [SysID]) VALUES (5, 7, 5, 8, N'SYS001')
INSERT [dbo].[System] ([ID], [ModelID], [ConstructorID], [CapacityID], [SysID]) VALUES (6, 9, 2, NULL, N'SYS001')
INSERT [dbo].[System] ([ID], [ModelID], [ConstructorID], [CapacityID], [SysID]) VALUES (7, NULL, 4, 10, N'SYS001')
INSERT [dbo].[System] ([ID], [ModelID], [ConstructorID], [CapacityID], [SysID]) VALUES (8, 10, 8, 15, N'SYS001')
SET IDENTITY_INSERT [dbo].[System] OFF
/****** Object: View [dbo].[View_2] Script Date: 06/16/2014 21:53:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[View_2]
AS
SELECT TOP (100) PERCENT SectionName, ConstructorName, ModelName, CapacityName, SysID, ID
FROM (SELECT dbo.Section.SectionName, dbo.Constructor.ConstructorName, dbo.Model.ModelName, Capacity_1.CapacityName, dbo.System.SysID, dbo.System.ID
FROM dbo.Model INNER JOIN
dbo.Section ON dbo.Model.SectionID = dbo.Section.ID INNER JOIN
dbo.System ON dbo.Model.ID = dbo.System.ModelID LEFT OUTER JOIN
dbo.Capacity AS Capacity_1 ON Capacity_1.ID = dbo.System.CapacityID AND Capacity_1.SectionID = dbo.Section.ID LEFT OUTER JOIN
dbo.Constructor ON dbo.System.ConstructorID = dbo.Constructor.ID
UNION
SELECT Section_2.SectionName, Constructor_2.ConstructorName, '' AS ModelName, Capacity_1.CapacityName, System_2.SysID, System_2.ID
FROM dbo.Capacity AS Capacity_1 INNER JOIN
dbo.Section AS Section_2 ON Capacity_1.SectionID = Section_2.ID RIGHT OUTER JOIN
dbo.System AS System_2 ON Capacity_1.ID = System_2.CapacityID LEFT OUTER JOIN
dbo.Constructor AS Constructor_2 ON System_2.ConstructorID = Constructor_2.ID
WHERE (System_2.ID NOT IN
(SELECT System_1.ID
FROM dbo.Model AS Model_1 INNER JOIN
dbo.Section AS Section_1 ON Model_1.SectionID = Section_1.ID INNER JOIN
dbo.System AS System_1 ON Model_1.ID = System_1.ModelID LEFT OUTER JOIN
dbo.Capacity AS Capacity_1 ON Capacity_1.ID = System_1.CapacityID AND Capacity_1.SectionID = Section_1.ID LEFT OUTER JOIN
dbo.Constructor AS Constructor_1 ON System_1.ConstructorID = Constructor_1.ID))) AS U
ORDER BY SectionName
vBulletin® v4.2.5, Copyright ©2000-1403, Jelsoft Enterprises Ltd.