代码之家 › 专栏 › 技术社区 › user8512043

SQL查询中的多个子查询

sql-server sql

user8512043 · 技术社区 · 8 年前

我正在创建一个将行转换为列的示例查询,如下所示:

Person_Id   Total Earned Leave  Earned Leave Enjoyed    Remaining Earned Leave  Total Casual Leave  Casual Leave Enjoyed    Remaining Casual Leave
1001        20                  10                      10                      20                  4                       16

以上是我获得的输出,并使用以下查询使用了多个子查询:

SELECT DISTINCT m.Person_Id, (SELECT k.Leave_Allocation FROM LeaveDetails k WHERE k.Leave_Name = 'Earn Leave' 
AND k.Person_Id = 1001 AND k.[Year] = '2017') AS 'Total Earned Leave',

(SELECT o.Leave_Enjoy FROM LeaveDetails o WHERE o.Leave_Name = 'Earn Leave' 
AND o.Person_Id = 1001 AND o.[Year] = '2017') AS 'Earned Leave Enjoyed',

(SELECT p.Leave_Remain FROM LeaveDetails p WHERE p.Leave_Name = 'Earn Leave' 
AND p.Person_Id = 1001 AND p.[Year] = '2017') AS 'Remaining Earned Leave',

(SELECT k.Leave_Allocation FROM LeaveDetails k WHERE k.Leave_Name = 'Casual Leave' 
AND k.Person_Id = 1001 AND k.[Year] = '2017') AS 'Total Casual Leave',

(SELECT o.Leave_Enjoy FROM LeaveDetails o WHERE o.Leave_Name = 'Casual Leave' 
AND o.Person_Id = 1001 AND o.[Year] = '2017') AS 'Casual Leave Enjoyed',

(SELECT p.Leave_Remain FROM LeaveDetails p WHERE p.Leave_Name = 'Casual Leave' 
AND p.Person_Id = 1001 AND p.[Year] = '2017') AS 'Remaining Casual Leave'

FROM LeaveDetails m WHERE m.Person_Id = 1001 AND m.[Year] = '2017'

我不确定我在这里是否会有性能问题,因为会有大量的数据,并且正在争论这是否会比支点或 运行时表创建 . 我只是想确定,对于我正在努力实现的目标来说,这是否是一个更好的选择。您可以使用 SQL Server , MySQL 或 Oracle 对于更好的性能问题-谢谢。

样本表和数据:

CREATE TABLE [dbo].[LeaveDetails](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Person_Id] [nvarchar](20) NULL,
    [Leave_Name] [nvarchar](40) NULL,
    [Leave_Allocation] [float] NULL,
    [Leave_Enjoy] [float] NULL,
    [Leave_Remain] [float] NULL,
    [Details] [nvarchar](100) NULL,
    [Year] [nvarchar](10) NULL,
    [Status] [bit] NULL
)

INSERT [dbo].[LeaveDetails] ([Id], [Person_Id], [Leave_Name], [Leave_Allocation], [Leave_Enjoy], [Leave_Remain], [Details], [Year], [Status]) VALUES (1, N'1001', N'Earn Leave', 20, 10, 10, NULL, N'2017', 1)
INSERT [dbo].[LeaveDetails] ([Id], [Person_Id], [Leave_Name], [Leave_Allocation], [Leave_Enjoy], [Leave_Remain], [Details], [Year], [Status]) VALUES (2, N'1001', N'Casual Leave', 20, 4, 16, NULL, N'2017', 1)

2 回复 | 直到 8 年前

Gordon Linoff 8 年前

使用条件聚合:

SELECT m.Person_Id,
       MAX(CASE WHEN m.Leave_Name = 'Earn Leave' THEN k.Leave_Allocation END) as [Total Earned Leave],
       MAX(CASE WHEN m.Leave_Name = 'Earn Leave' THEN m.Leave_Enjoy END) as [Earned Leave Enjoyed],
       MAX(CASE WHEN m.Leave_Name = 'Earn Leave' THEN m.Leave_Remain END) as [Remaining Earned Leave],
       MAX(CASE WHEN m.Leave_Name = 'Casual Leave' THEN k.Leave_Allocation END) as [Total Casual Leave],
       MAX(CASE WHEN m.Leave_Name = 'Casual Leave' THEN k.Leave_Remain END) as [Casual Leave Enjoyed],
       MAX(CASE WHEN m.Leave_Name = 'Casual Leave' THEN k.Leave_Remain END) as [Remaining Casual Leave]
FROM LeaveDetails m
WHERE m.Person_Id = 1001 AND m.[Year] = '2017'
GROUP BY m.Person_ID;

注意:我不主张在列别名中使用特殊字符(例如空格)。如果需要,请使用正确的转义字符(方括号)。仅对字符串和日期常量使用单引号。

jwolf 8 年前

PIVOT可以工作,但看起来这只是一行,您希望将其透视到列输出,并且列名称是明确已知的。如果是这种情况,您可以将单列结果合并在一起:

SELECT 'Person_ID' as col_name, Person_Id as col_value FROM LeaveDetails WHERE Person_Id = 1001 AND [Year] = '2017'
UNION
SELECT 'Leave_Enjoy' as col_name, Leave_Enjoy as col_value FROM LeaveDetails WHERE Person_Id = 1001 AND [Year] = '2017'
UNION
...

它写起来简单得多,读起来更干净,而且应该运行得更快一些——每列仍然有一个表扫描。该表是否根据Person\u ID和年份编制索引?

若速度是一个问题,您可以创建一行的临时表:

SELECT * into #ld_temp FROM LeaveDetails WHERE Person_Id = 1001 AND [Year] = '2017'

然后从选择/联合代码中的临时表中选择:

SELECT 'Person_ID' as col_name, Person_Id as col_value FROM #ld_temp
UNION
SELECT 'Leave_Enjoy' as col_name, Leave_Enjoy as col_value FROM #ld_temp
UNION
...

现在,您只需对大表进行一次扫描。

我希望这有帮助。