灰色的兩張圖是資料源，白色的圖是查詢結果。
一直弄不出正確的結果，以下是我的sql。
求一份能運行處正確結果的sql。
select e.GroupID,c.FirstName,c.LastName,c.Job,e.CompanyName,c.ExternalID,c.Count from(
select b.FirstName,b.LastName,b.Job, count(*) as [Count] ,max(b.ExternalID) as ExternalID, min(a.v_id) as c_id from
(select ROW_NUMBER() over(order by(select(null))) as v_id,* from a) a
inner join
(select ROW_NUMBER() over(order by(select(null))) as v_id,* from b) b
on a.GroupID = b.GroupID group by b.FirstName,b.LastName,b.Job
) as c
inner join
(select ROW_NUMBER() over(order by(select(null))) as c_id,* from a) e
on c.c_id = e.c_id
order by [Count] asc,CompanyName asc,FirstName desc
CREATE TABLE [dbo].[a](
[GroupID] [int] NULL,
[CompanyName] [nvarchar](50) NULL,
)
CREATE TABLE [dbo].[b](
[GroupID] [int] NULL,
[Year] [nvarchar](50) NULL,
[VenderID] [nvarchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[Job] [nvarchar](50) NULL,
[ExternalID] [nvarchar](50) NULL,
[Region] [nvarchar](50) NULL,
)
INSERT [dbo].[a] ([GroupID], [CompanyName]) VALUES (35, N'Shipping & Co.')
INSERT [dbo].[a] ([GroupID], [CompanyName]) VALUES (36, N'Johnson and Sons')
INSERT [dbo].[a] ([GroupID], [CompanyName]) VALUES (39, N'News Corp.')
INSERT [dbo].[a] ([GroupID], [CompanyName]) VALUES (40, N'FireConsulting')
INSERT [dbo].[a] ([GroupID], [CompanyName]) VALUES (5, N'WaterBus Enterprise')
INSERT [dbo].[a] ([GroupID], [CompanyName]) VALUES (6, N'Alloy LLC')
INSERT [dbo].[a] ([GroupID], [CompanyName]) VALUES (27, N'Machinx')
GO
INSERT [dbo].[b] ([ID], [GroupID], [Year], [VenderID], [FirstName], [LastName], [Job], [ExternalID], [Region]) VALUES (1, 35, N'2018', N'102', N'Daniel', N'Knolle', N'Manager', N'39765', N'West')
INSERT [dbo].[b] ([ID], [GroupID], [Year], [VenderID], [FirstName], [LastName], [Job], [ExternalID], [Region]) VALUES (2, 35, N'2018', N'1988', N'Arnold', N'Sully', N'Manager', N'48507', N'West')
INSERT [dbo].[b] ([ID], [GroupID], [Year], [VenderID], [FirstName], [LastName], [Job], [ExternalID], [Region]) VALUES (3, 35, N'2018', N'1988', N'Arnold', N'Sully', N'Manager', N'48507', N'West')
INSERT [dbo].[b] ([ID], [GroupID], [Year], [VenderID], [FirstName], [LastName], [Job], [ExternalID], [Region]) VALUES (4, 36, N'2018', N'102', N'Daniel', N'Knolle', N'Manager', N'8219', N'West')
INSERT [dbo].[b] ([ID], [GroupID], [Year], [VenderID], [FirstName], [LastName], [Job], [ExternalID], [Region]) VALUES (5, 36, N'2018', N'1988', N'Arnold', N'Sully', N'Manager', N'48507', N'West')
INSERT [dbo].[b] ([ID], [GroupID], [Year], [VenderID], [FirstName], [LastName], [Job], [ExternalID], [Region]) VALUES (6, 39, N'2018', N'102', N'Daniel', N'Knolle', N'Manager', N'8219', N'West')
INSERT [dbo].[b] ([ID], [GroupID], [Year], [VenderID], [FirstName], [LastName], [Job], [ExternalID], [Region]) VALUES (7, 39, N'2018', N'102', N'Daniel', N'Knolle', N'Manager', N'39765', N'West')
INSERT [dbo].[b] ([ID], [GroupID], [Year], [VenderID], [FirstName], [LastName], [Job], [ExternalID], [Region]) VALUES (8, 39, N'2018', N'650', N'Lisa', N'Roberts', N'Manager', N'1860', N'West')
INSERT [dbo].[b] ([ID], [GroupID], [Year], [VenderID], [FirstName], [LastName], [Job], [ExternalID], [Region]) VALUES (9, 39, N'2018', N'650', N'Lisa', N'Roberts', N'Manager', N'5397', N'Central')
INSERT [dbo].[b] ([ID], [GroupID], [Year], [VenderID], [FirstName], [LastName], [Job], [ExternalID], [Region]) VALUES (10, 39, N'2018', N'1988', N'Arnold', N'Sully', N'Manager', N'48507', N'West')
INSERT [dbo].[b] ([ID], [GroupID], [Year], [VenderID], [FirstName], [LastName], [Job], [ExternalID], [Region]) VALUES (11, 39, N'2018', N'12', N'Mary', N'Dial', N'Manager', N'1860', N'East')
INSERT [dbo].[b] ([ID], [GroupID], [Year], [VenderID], [FirstName], [LastName], [Job], [ExternalID], [Region]) VALUES (12, 40, N'2019', N'102', N'Daniel', N'Knolle', N'Manager', N'8219', N'East')
INSERT [dbo].[b] ([ID], [GroupID], [Year], [VenderID], [FirstName], [LastName], [Job], [ExternalID], [Region]) VALUES (13, 40, N'2019', N'102', N'Daniel', N'Knolle', N'Manager', N'39765', N'West')
INSERT [dbo].[b] ([ID], [GroupID], [Year], [VenderID], [FirstName], [LastName], [Job], [ExternalID], [Region]) VALUES (14, 40, N'2019', N'650', N'Lisa', N'Roberts', N'Manager', N'1860', N'West')
INSERT [dbo].[b] ([ID], [GroupID], [Year], [VenderID], [FirstName], [LastName], [Job], [ExternalID], [Region]) VALUES (15, 40, N'2019', N'1988', N'Arnold', N'Sully', N'Manager', N'39765', N'West')
INSERT [dbo].[b] ([ID], [GroupID], [Year], [VenderID], [FirstName], [LastName], [Job], [ExternalID], [Region]) VALUES (16, 40, N'2019', N'1988', N'Arnold', N'Sully', N'Manager', N'48607', N'West')
INSERT [dbo].[b] ([ID], [GroupID], [Year], [VenderID], [FirstName], [LastName], [Job], [ExternalID], [Region]) VALUES (17, 5, N'2012', N'478', N'Dennis', N'S', N'Contractor', N'24122', N'North')
INSERT [dbo].[b] ([ID], [GroupID], [Year], [VenderID], [FirstName], [LastName], [Job], [ExternalID], [Region]) VALUES (18, 6, N'2012', N'478', N'Dennis', N'S', N'Contractor', N'10272', N'North')
INSERT [dbo].[b] ([ID], [GroupID], [Year], [VenderID], [FirstName], [LastName], [Job], [ExternalID], [Region]) VALUES (19, 6, N'2012', N'478', N'Larrry', N'Weis', N'Contractor', N'4219', N'North')
INSERT [dbo].[b] ([ID], [GroupID], [Year], [VenderID], [FirstName], [LastName], [Job], [ExternalID], [Region]) VALUES (20, 6, N'2012', N'478', N'Larrry', N'Weis', N'Contractor', N'10272', N'North')
INSERT [dbo].[b] ([ID], [GroupID], [Year], [VenderID], [FirstName], [LastName], [Job], [ExternalID], [Region]) VALUES (21, 27, N'2009', N'12', N'Mary', N'Dial', N'Manager', N'1860', N'East')
GO
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/269310.html
標籤：應用實例