需要基于一个值复制行。

huangapple
go评论93阅读模式
英文:

Need to Replicate Rows based on a value

问题

使用现有的SQL语句，如果[Action Taken]的值等于"See Previous Actions"，我需要复制行。

"See Previous Actions" 意味着参考此案例中案件参与者接收的所有操作。基本上，每个[ViolationCategory]中的 "See Previous Actions" 应填充为此案例和参与者的其他类别相同的操作。

在我的示例中有两个参与者，如您所看到的 [CaseParticipantID]，其中一个填写了 [Action Taken]，而另一个没有。

我假设...数据可以使用案件编号和案件参与者ID与自身联接，但我不知道如何执行。

来自SQL语句的数据（视图仅限于易于阅读的相关列]：

案件编号 案件参与者ID 违规类别 执行操作
123 456 利益冲突 报酬
123 456 利益冲突 谴责
123 456 利益冲突 其他
123 789 利益冲突 口头谴责
123 456 偏袒 查看以前的操作

期望结果

案件编号 案件参与者ID 违规类别 执行操作
123 456 利益冲突 报酬
123 456 利益冲突 谴责
123 456 利益冲突 其他
123 789 利益冲突 口头谴责
123 456 偏袒 报酬
123 456 偏袒 谴责
123 456 偏袒 其他

我当前的SQL语句，筛选为只有一个案件的情况如下：

  1. SELECT
  2.      Cases.CaseNumber
  3.     ,Cases.DateOpened
  4.     ,Cases.DateClosed
  5.     ,Participant.CaseParticipantId
  6.     ,Participant.RoleInCase
  7.     ,Participant.RelToOrganization
  8.     ,Participant.Practice
  9.     ,Participant.City
  10.     ,Issues.Issue
  11.     ,Issues.IssueSubCategory
  12.     ,Issues.CaseIssueId
  13.     ,Issues.Outcome
  14.     ,ParticipantAlias.ParticipantName
  15.     ,CaseAction.ActionTaken
  16. FROM [Warehouse].[Table].[Case] as Cases
  17. LEFT JOIN [Warehouse].[Table].[CaseParticipant] as Participant
  18.     ON Cases.CaseNumber = Participant.CaseNumber
  20. LEFT JOIN [Warehouse].[Table].[CaseIssue] as Issues
  21.     ON Participant.CaseParticipantId = Issues.CaseParticipantId
  23. LEFT JOIN [Warehouse].[Table].[CaseAction] as CaseAction
  24.     ON Issues.CaseIssueId = CaseAction.CaseIssueId
  26. LEFT JOIN [Warehouse].[Table].[ParticipantAlias] as ParticipantAlias
  27.     ON Participant.ParticipantAliasId = ParticipantAlias.ParticipantAliasId
  29. WHERE [Cases].[CaseNumber] = '123'
英文:

Using an existing SQL statement, I need to duplicate rows if value in [Action Taken] equals "See Previous Actions".

“See Previous Actions” means refer back to all actions Case Participant received for this case.
Basically, each [ViolationCategory] with "See Previous Actions" should be filled in with the same actions as other Categories for this case and participant.

I have two participants in my example as you see by the [CaseParticipantID] one that has [Action Taken] filled in and the other one does not.

I assume...data can be unioned back to itself using Case Number and Case Participant ID, but I do not know how to do that.

Data from SQL Statement (View limited to only pertinent columns for easier reading]

Case Number CaseParticipantID ViolationCategory Action Taken
123 456 Conflict of Interest Compensation
123 456 Conflict of Interest Reprimand
123 456 Conflict of Interest Other
123 789 Conflict of Interest Verbal Reprimand
123 456 Favoritism See Previous Action

Desired Outcome

Case Number CaseParticipantID ViolationCategory Action Taken
123 456 Conflict of Interest Compensation
123 456 Conflict of Interest Reprimand
123 456 Conflict of Interest Other
123 789 Conflict of Interest Verbal Reprimand
123 456 Favoritism Compensation
123 456 Favoritism Reprimand
123 456 Favoritism Other

My current SQL Statement, filtered down to just one case is:

  1. SELECT
  2.      Cases.CaseNumber
  3.     ,Cases.DateOpened
  4.     ,Cases.DateClosed
  5.     ,Participant.CaseParticipantId
  6.     ,Participant.RoleInCase
  7.     ,Participant.RelToOrganization
  8.     ,Participant.Practice
  9.     ,Participant.City
  10.     ,Issues.Issue
  11.     ,Issues.IssueSubCategory
  12.     ,Issues.CaseIssueId
  13.     ,Issues.Outcome
  14.     ,ParticipantAlias.ParticipantName
  15.     ,CaseAction.ActionTaken
  16. FROM [Warehouse].[Table].[Case] as Cases
  17. LEFT JOIN [Warehouse].[Table].[CaseParticipant] as Participant
  18.     ON Cases.CaseNumber = Participant.CaseNumber
  20. LEFT JOIN [Warehouse].[Table].[CaseIssue] as Issues
  21.     ON Participant.CaseParticipantId = Issues.CaseParticipantId
  23. LEFT JOIN [Warehouse].[Table].[CaseAction] as CaseAction
  24.     ON Issues.CaseIssueId = CaseAction.CaseIssueId
  26. LEFT JOIN [Warehouse].[Table].[ParticipantAlias] as ParticipantAlias
  27.     ON Participant.ParticipantAliasId = ParticipantAlias.ParticipantAliasId
  29. WHERE [Cases].[CaseNumber] = '123'
  31. </details>
  34. # 答案1
  35. **得分**: 0
  37. 根据您的查询，我无法确定应该是视图还是存储过程，但我建议使用后者。以下是使用存储过程实现预期输出的示例。输出如下：
  39. ```sql
  40. USE [Warehouse]
  41. GO
  43. SET ANSI_NULLS ON
  44. GO
  45. SET QUOTED_IDENTIFIER ON
  46. GO
  48. CREATE PROCEDURE [dbo].[SP_GetCaseResult]
  49. 	-- 添加存储过程的参数
  50. 	@caseNumber as int
  51. AS
  52. BEGIN
  53. 	SET NOCOUNT ON;
  55. 	DROP TABLE IF EXISTS #Temp_table
  56. 	-- 1. 将原始查询结果存储到临时表
  57. 	SELECT
  58. 	Cases.CaseNumber
  59. 	,Cases.DateOpened
  60. 	,Cases.DateClosed
  61. 	,Participant.CaseParticipantId
  62. 	,Participant.CaseParticipantKey
  63. 	,Participant.ParticipantAliasId
  64. 	,Participant.RoleInCase
  65. 	,Participant.RelToOrganization
  66. 	,Participant.Practice
  67. 	,Participant.City
  68. 	,Issues.Issue
  69. 	,Issues.IssueSubCategory
  70. 	,Issues.CaseIssueId
  71. 	,Issues.Outcome
  72. 	,ParticipantAlias.ParticipantName
  73. 	,CaseAction.ActionTaken
  74. 	INTO #Temp_table
  75. 	FROM [Warehouse].[Table].[Case] as Cases
  76. 	LEFT JOIN
  77. 	[Warehouse].[Table].[CaseParticipant] as Participant
  78. 	ON Cases.CaseNumber = Participant.CaseNumber
  79. 	LEFT JOIN
  80. 	[Warehouse].[Table].[CaseIssue] as Issues
  81. 	ON Participant.CaseParticipantId = Issues.CaseParticipantId
  82. 	LEFT JOIN
  83. 	[Warehouse].[Table].[CaseAction] as CaseAction
  84. 	ON Issues.CaseIssueId = CaseAction.CaseIssueId
  85. 	LEFT JOIN
  86. 	[Warehouse].[Table].[ParticipantAlias] as ParticipantAlias
  87. 	ON Participant.ParticipantAliasId = ParticipantAlias.ParticipantAliasId
  88. 	WHERE [Cases].[CaseNumber] = @caseNumber
  90. 	-- 2. 从临时表中查询，排除 "查看以前的操作"
  91. 	SELECT * FROM #Temp_table t1 WHERE t1.ActionTaken <> '查看以前的操作'
  93. 	-- "联接"两个结果
  94. 	UNION ALL
  96. 	-- 3. 从临时表中查询，联接两个带和不带 "查看以前的操作" 的表，并获取所需的输出
  97. 	SELECT
  98. 		 t1.CaseNumber
  99. 		,t1.DateOpened
  100. 		,t1.DateClosed
  101. 		,t1.CaseParticipantId
  102. 		,t1.CaseParticipantKey
  103. 		,t1.ParticipantAliasId
  104. 		,t1.RoleInCase
  105. 		,t1.RelToOrganization
  106. 		,t1.Practice
  107. 		,t1.City
  108. 		,t1.Issue
  109. 		,t1.IssueSubCategory
  110. 		,t1.CaseIssueId
  111. 		,t1.Outcome
  112. 		,t1.ParticipantName
  113. 		,t2.ActionTaken
  114. 	FROM #Temp_table t2
  115. 	INNER JOIN #Temp_table t1 ON t2.CaseNumber = t1.CaseNumber AND t2.CaseParticipantId = t1.CaseParticipantId
  116. 	WHERE t1.ActionTaken = '查看以前的操作' AND t2.ActionTaken <> '查看以前的操作';
  118. END
  119. GO

或者，如果您真的希望不使用存储过程，以下是一个简单查询的示例：

  1. DECLARE @caseNumber int = '123'
  3. SELECT DISTINCT 
  4.      t1.CaseNumber
  5.     ,t1.DateOpened
  6.     ,t1.DateClosed
  7.     ,t1.CaseParticipantId
  8.     ,t1.CaseParticipantKey
  9.     ,t1.ParticipantAliasId
  10.     ,t1.RoleInCase
  11.     ,t1.RelToOrganization
  12.     ,t1.Practice
  13.     ,t1.City
  14.     ,t1.Issue
  15.     ,t1.IssueSubCategory
  16.     ,t1.CaseIssueId
  17.     ,t1.Outcome
  18.     ,t1.ParticipantName
  19.     ,t2.ActionTaken
  20. FROM (
  21.     SELECT
  22.     Cases.CaseNumber
  23.     ,Cases.DateOpened
  24.     ,Cases.DateClosed
  25.     ,Participant.CaseParticipantId
  26.     ,Participant.CaseParticipantKey
  27.     ,Participant.ParticipantAliasId
  28.     ,Participant.RoleInCase
  29.     ,Participant.RelToOrganization
  30.     ,Participant.Practice
  31.     ,Participant.City
  32.     ,Issues.Issue
  33.     ,Issues.IssueSubCategory
  34.     ,Issues.CaseIssueId
  35.     ,Issues.Outcome
  36.     ,ParticipantAlias.ParticipantName
  37.     ,CaseAction.ActionTaken
  38.     FROM [Warehouse].[Table].[Case] as Cases
  39.     LEFT JOIN
  40.     [Warehouse].[Table].[CaseParticipant] as Participant
  41.     ON Cases.CaseNumber = Participant.CaseNumber
  42.     LEFT JOIN
  43.     [Warehouse].[Table].[CaseIssue] as Issues
  44.     ON Participant.CaseParticipantId = Issues.CaseParticipantId
  45.     LEFT JOIN
  46.     [Warehouse].[Table].[CaseAction] as CaseAction
  47.     ON Issues.CaseIssueId = CaseAction.CaseIssueId
  48.     LEFT JOIN
  49.     [Warehouse].[Table].[ParticipantAlias] as ParticipantAlias
  50.     ON Participant.ParticipantAliasId = ParticipantAlias.ParticipantAliasId
  51.     WHERE [Cases].[CaseNumber] = @caseNumber --123
  52. ) t1
  53. FULL OUTER JOIN (
  54.     SELECT
  55.     Cases.CaseNumber
  56.     ,Cases.DateOpened
  57.     ,Cases.DateClosed
  58.     ,Participant.CaseParticipantId
  59.     ,Participant.CaseParticipantKey
  60.     ,Participant.ParticipantAliasId
  61.     ,Participant.RoleInCase
  62.     ,Participant.RelToOrganization
  63.     ,Participant.Practice
  64.     ,Participant.City
  65.     ,Issues.Issue
  66.     ,Issues.IssueSubCategory
  67.     ,Issues.CaseIssueId
  68.     ,Issues.Outcome
  69.     ,ParticipantAlias.ParticipantName
  70.     ,CaseAction.ActionTaken
  71.     FROM [Warehouse].[Table].[Case] as Cases
  72.     LEFT JOIN
  73.     [Warehouse].[Table].[CaseParticipant] as Participant
  74.     ON Cases.CaseNumber = Participant.CaseNumber
  75.     LEFT JOIN
  76.     [Warehouse].[Table].[CaseIssue] as Issues
  77.     ON Participant.CaseParticipantId = Issues.CaseParticipantId
  78.     LEFT JOIN
  79.     [Warehouse].[Table].[CaseAction] as CaseAction
  80.     ON Issues.CaseIssueId = CaseAction.CaseIssueId
  81.     LEFT JOIN
  82.     [Warehouse].[Table].[ParticipantAlias] as ParticipantAlias
  83.     ON Participant.ParticipantAliasId = ParticipantAlias.ParticipantAliasId
  84.     WHERE [Cases].[CaseNumber] = @caseNumber --123 
  85. ) t2 
  86.     ON t2.CaseNumber = t1.CaseNumber AND t2.CaseParticipantId = t1.CaseParticipantId
  87.     WHERE t2.ActionTaken <> '查看以前的操作';
英文:

Based on your query, I cannot tell whether it should be a view or procedure, but I'd suggest making the latter.
Here is an example of how to achieve the expected output with a stored procedure.
The output:
Stored Procedure Sample Output

  1. USE [Warehouse]
  2. GO
  3. SET ANSI_NULLS ON
  4. GO
  5. SET QUOTED_IDENTIFIER ON
  6. GO
  7. CREATE PROCEDURE [dbo].[SP_GetCaseResult]
  8. -- Add the parameters for the stored procedure here
  9. @caseNumber as int
  10. AS
  11. BEGIN
  12. SET NOCOUNT ON;
  13. DROP TABLE IF EXISTS #Temp_table
  14. -- 1. Original query into temp table
  15. SELECT
  16. Cases.CaseNumber
  17. ,Cases.DateOpened
  18. ,Cases.DateClosed
  19. ,Participant.CaseParticipantId
  20. ,Participant.CaseParticipantKey
  21. ,Participant.ParticipantAliasId
  22. ,Participant.RoleInCase
  23. ,Participant.RelToOrganization
  24. ,Participant.Practice
  25. ,Participant.City
  26. ,Issues.Issue
  27. ,Issues.IssueSubCategory
  28. ,Issues.CaseIssueId
  29. ,Issues.Outcome
  30. ,ParticipantAlias.ParticipantName
  31. ,CaseAction.ActionTaken
  32. INTO #Temp_table
  33. FROM [Warehouse].[Table].[Case] as Cases
  34. LEFT JOIN
  35. [Warehouse].[Table].[CaseParticipant] as Participant
  36. ON Cases.CaseNumber = Participant.CaseNumber
  37. LEFT JOIN
  38. [Warehouse].[Table].[CaseIssue] as Issues
  39. ON Participant.CaseParticipantId = Issues.CaseParticipantId
  40. LEFT JOIN
  41. [Warehouse].[Table].[CaseAction] as CaseAction
  42. ON Issues.CaseIssueId = CaseAction.CaseIssueId
  43. LEFT JOIN
  44. [Warehouse].[Table].[ParticipantAlias] as ParticipantAlias
  45. ON Participant.ParticipantAliasId = ParticipantAlias.ParticipantAliasId
  46. WHERE [Cases].[CaseNumber] = @caseNumber
  47. -- 2. Query from temptable without &quot;See previous actions&quot;
  48. SELECT * FROM #Temp_table t1 WHERE t1.ActionTaken &lt;&gt; &#39;See Previous Actions&#39;
  49. --  &quot;Joining&quot; the two results
  50. UNION ALL
  51. -- 3. Query from temptable with joining the two table with and without &quot;See previous actions&quot; and getting needed output.
  52. SELECT
  53. t1.CaseNumber
  54. ,t1.DateOpened
  55. ,t1.DateClosed
  56. ,t1.CaseParticipantId
  57. ,t1.CaseParticipantKey
  58. ,t1.ParticipantAliasId
  59. ,t1.RoleInCase
  60. ,t1.RelToOrganization
  61. ,t1.Practice
  62. ,t1.City
  63. ,t1.Issue
  64. ,t1.IssueSubCategory
  65. ,t1.CaseIssueId
  66. ,t1.Outcome
  67. ,t1.ParticipantName
  68. ,t2.ActionTaken
  69. FROM #Temp_table t2
  70. INNER JOIN #Temp_table t1 ON t2.CaseNumber = t1.CaseNumber AND t2.CaseParticipantId = t1.CaseParticipantId
  71. WHERE t1.ActionTaken = &#39;See Previous Actions&#39; AND t2.ActionTaken &lt;&gt; &#39;See Previous Actions&#39;
  72. END
  73. GO

OR If you really want it without stored-procedure: Here is an example for it:

Example of simple query

  1. DECLARE @caseNumber int = &#39;123&#39;
  2. SELECT DISTINCT 
  3. t1.CaseNumber
  4. ,t1.DateOpened
  5. ,t1.DateClosed
  6. ,t1.CaseParticipantId
  7. ,t1.CaseParticipantKey
  8. ,t1.ParticipantAliasId
  9. ,t1.RoleInCase
  10. ,t1.RelToOrganization
  11. ,t1.Practice
  12. ,t1.City
  13. ,t1.Issue
  14. ,t1.IssueSubCategory
  15. ,t1.CaseIssueId
  16. ,t1.Outcome
  17. ,t1.ParticipantName
  18. ,t2.ActionTaken
  19. FROM (
  20. SELECT
  21. Cases.CaseNumber
  22. ,Cases.DateOpened
  23. ,Cases.DateClosed
  24. ,Participant.CaseParticipantId
  25. ,Participant.CaseParticipantKey
  26. ,Participant.ParticipantAliasId
  27. ,Participant.RoleInCase
  28. ,Participant.RelToOrganization
  29. ,Participant.Practice
  30. ,Participant.City
  31. ,Issues.Issue
  32. ,Issues.IssueSubCategory
  33. ,Issues.CaseIssueId
  34. ,Issues.Outcome
  35. ,ParticipantAlias.ParticipantName
  36. ,CaseAction.ActionTaken
  37. FROM [Warehouse].[Table].[Case] as Cases
  38. LEFT JOIN
  39. [Warehouse].[Table].[CaseParticipant] as Participant
  40. ON Cases.CaseNumber = Participant.CaseNumber
  41. LEFT JOIN
  42. [Warehouse].[Table].[CaseIssue] as Issues
  43. ON Participant.CaseParticipantId = Issues.CaseParticipantId
  44. LEFT JOIN
  45. [Warehouse].[Table].[CaseAction] as CaseAction
  46. ON Issues.CaseIssueId = CaseAction.CaseIssueId
  47. LEFT JOIN
  48. [Warehouse].[Table].[ParticipantAlias] as ParticipantAlias
  49. ON Participant.ParticipantAliasId = ParticipantAlias.ParticipantAliasId
  50. WHERE [Cases].[CaseNumber] = @caseNumber --123
  51. ) t1
  52. FULL OUTER JOIN (
  53. SELECT
  54. Cases.CaseNumber
  55. ,Cases.DateOpened
  56. ,Cases.DateClosed
  57. ,Participant.CaseParticipantId
  58. ,Participant.CaseParticipantKey
  59. ,Participant.ParticipantAliasId
  60. ,Participant.RoleInCase
  61. ,Participant.RelToOrganization
  62. ,Participant.Practice
  63. ,Participant.City
  64. ,Issues.Issue
  65. ,Issues.IssueSubCategory
  66. ,Issues.CaseIssueId
  67. ,Issues.Outcome
  68. ,ParticipantAlias.ParticipantName
  69. ,CaseAction.ActionTaken
  70. FROM [Warehouse].[Table].[Case] as Cases
  71. LEFT JOIN
  72. [Warehouse].[Table].[CaseParticipant] as Participant
  73. ON Cases.CaseNumber = Participant.CaseNumber
  74. LEFT JOIN
  75. [Warehouse].[Table].[CaseIssue] as Issues
  76. ON Participant.CaseParticipantId = Issues.CaseParticipantId
  77. LEFT JOIN
  78. [Warehouse].[Table].[CaseAction] as CaseAction
  79. ON Issues.CaseIssueId = CaseAction.CaseIssueId
  80. LEFT JOIN
  81. [Warehouse].[Table].[ParticipantAlias] as ParticipantAlias
  82. ON Participant.ParticipantAliasId = ParticipantAlias.ParticipantAliasId
  83. WHERE [Cases].[CaseNumber] = @caseNumber --123 
  84. ) t2 
  85. ON t2.CaseNumber = t1.CaseNumber AND t2.CaseParticipantId = t1.CaseParticipantId
  86. WHERE t2.ActionTaken &lt;&gt; &#39;See Previous Actions&#39;

答案2

得分: 0

以下是 UNION 示例的翻译部分：

然而，从性能考虑，如果您的表包含数十万条记录，那么存储过程是“最快”的，因为它只查询主要数据一次！（之后临时表正在使用）。

在另外两个示例中，您查询表的次数越多（其中包含大量数据），性能就会越差。如果表中没有太多数据，性能差异不大。
所以您可以选择使用哪种方法来获取所期望的输出。

UNION 示例结果

  1. DECLARE @caseNumber int = '123'
  2. SELECT
  3. 	Cases.CaseNumber
  4. 	,Cases.DateOpened
  5. 	,Cases.DateClosed
  6. 	,Participant.CaseParticipantId
  7. 	,Participant.CaseParticipantKey
  8. 	,Participant.ParticipantAliasId
  9. 	,Participant.RoleInCase
  10. 	,Participant.RelToOrganization
  11. 	,Participant.Practice
  12. 	,Participant.City
  13. 	,Issues.Issue
  14. 	,Issues.IssueSubCategory
  15. 	,Issues.CaseIssueId
  16. 	,Issues.Outcome
  17. 	,ParticipantAlias.ParticipantName
  18. 	,CaseAction.ActionTaken
  19. FROM [Warehouse].[Table].[Case] as Cases
  20. LEFT JOIN
  21. 	[Warehouse].[Table].[CaseParticipant] as Participant
  22. 	ON Cases.CaseNumber = Participant.CaseNumber
  23. LEFT JOIN
  24. 	[Warehouse].[Table].[CaseIssue] as Issues
  25. 	ON Participant.CaseParticipantId = Issues.CaseParticipantId
  26. LEFT JOIN
  27. 	[Warehouse].[Table].[CaseAction] as CaseAction
  28. 	ON Issues.CaseIssueId = CaseAction.CaseIssueId
  29. LEFT JOIN
  30. 	[Warehouse].[Table].[ParticipantAlias] as ParticipantAlias
  31. 	ON Participant.ParticipantAliasId = ParticipantAlias.ParticipantAliasId
  32. WHERE [Cases].[CaseNumber] = @caseNumber AND [CaseAction].ActionTaken &lt;&gt; 'See Previous Actions';
  34. UNION ALL
  36. SELECT DISTINCT 
  37. 	 t1.CaseNumber
  38. 	,t1.DateOpened
  39. 	,t1.DateClosed
  40. 	,t1.CaseParticipantId
  41. 	,t1.CaseParticipantKey
  42. 	,t1.ParticipantAliasId
  43. 	,t1.RoleInCase
  44. 	,t1.RelToOrganization
  45. 	,t1.Practice
  46. 	,t1.City
  47. 	,t1.Issue
  48. 	,t1.IssueSubCategory
  49. 	,t1.CaseIssueId
  50. 	,t1.Outcome
  51. 	,t1.ParticipantName
  52. 	,t2.ActionTaken
  53. FROM (
  54. 	SELECT
  55. 	Cases.CaseNumber
  56. 	,Cases.DateOpened
  57. 	,Cases.DateClosed
  58. 	,Participant.CaseParticipantId
  59. 	,Participant.CaseParticipantKey
  60. 	,Participant.ParticipantAliasId
  61. 	,Participant.RoleInCase
  62. 	,Participant.RelToOrganization
  63. 	,Participant.Practice
  64. 	,Participant.City
  65. 	,Issues.Issue
  66. 	,Issues.IssueSubCategory
  67. 	,Issues.CaseIssueId
  68. 	,Issues.Outcome
  69. 	,ParticipantAlias.ParticipantName
  70. 	,CaseAction.ActionTaken
  71. 	FROM [Warehouse].[Table].[Case] as Cases
  72. 	LEFT JOIN
  73. 	[Warehouse].[Table].[CaseParticipant] as Participant
  74. 	ON Cases.CaseNumber = Participant.CaseNumber
  75. 	LEFT JOIN
  76. 	[Warehouse].[Table].[CaseIssue] as Issues
  77. 	ON Participant.CaseParticipantId = Issues.CaseParticipantId
  78. 	LEFT JOIN
  79. 	[Warehouse].[Table].[CaseAction] as CaseAction
  80. 	ON Issues.CaseIssueId = CaseAction.CaseIssueId
  81. 	LEFT JOIN
  82. 	[Warehouse].[Table].[ParticipantAlias] as ParticipantAlias
  83. 	ON Participant.ParticipantAliasId = ParticipantAlias.ParticipantAliasId
  84. 	WHERE [Cases].[CaseNumber] = @caseNumber AND [CaseAction].ActionTaken = 'See Previous Actions'
  85. 	) t1
  86. 	INNER JOIN (
  87. 		SELECT
  88. 			Cases.CaseNumber
  89. 			,Cases.DateOpened
  90. 			,Cases.DateClosed
  91. 			,Participant.CaseParticipantId
  92. 			,Participant.CaseParticipantKey
  93. 			,Participant.ParticipantAliasId
  94. 			,Participant.RoleInCase
  95. 			,Participant.RelToOrganization
  96. 			,Participant.Practice
  97. 			,Participant.City
  98. 			,Issues.Issue
  99. 			,Issues.IssueSubCategory
  100. 			,Issues.CaseIssueId
  101. 			,Issues.Outcome
  102. 			,ParticipantAlias.ParticipantName
  103. 			,CaseAction.ActionTaken
  104. 		FROM [Warehouse].[Table].[Case] as Cases
  105. 		LEFT JOIN
  106. 			[Warehouse].[Table].[CaseParticipant] as Participant
  107. 			ON Cases.CaseNumber = Participant.CaseNumber
  108. 		LEFT JOIN
  109. 			[Warehouse].[Table].[CaseIssue] as Issues
  110. 			ON Participant.CaseParticipantId = Issues.CaseParticipantId
  111. 		LEFT JOIN
  112. 			[Warehouse].[Table].[CaseAction] as CaseAction
  113. 			ON Issues.CaseIssueId = CaseAction.CaseIssueId
  114. 		LEFT JOIN
  115. 			[Warehouse].[Table].[ParticipantAlias] as ParticipantAlias
  116. 			ON Participant.ParticipantAliasId = ParticipantAlias.ParticipantAliasId
  117. 		WHERE [Cases].[CaseNumber] = @caseNumber AND [CaseAction].ActionTaken &lt;&gt; 'See Previous Actions'
  118. 	) t2 
  119. 	ON t2.CaseNumber = t1.CaseNumber AND t2.CaseParticipantId = t1.CaseParticipantId
英文:

Here is the UNION sample also:

However considering performance, if your tables contain hundreds of thousands of records, then the stored procedure is the "fastest" as it only queries the main data once ! (afterwards the temporary tables are in use).

In the other 2 examples, the more you query the tables (which contains many data) the less the performance will be. There won't be much difference if the tables don't contain many data inside.
So it's up to you what way method you'd choose getting the expected output.

UNION example result

  1. DECLARE @caseNumber int = &#39;123&#39;
  2. SELECT
  3. Cases.CaseNumber
  4. ,Cases.DateOpened
  5. ,Cases.DateClosed
  6. ,Participant.CaseParticipantId
  7. ,Participant.CaseParticipantKey
  8. ,Participant.ParticipantAliasId
  9. ,Participant.RoleInCase
  10. ,Participant.RelToOrganization
  11. ,Participant.Practice
  12. ,Participant.City
  13. ,Issues.Issue
  14. ,Issues.IssueSubCategory
  15. ,Issues.CaseIssueId
  16. ,Issues.Outcome
  17. ,ParticipantAlias.ParticipantName
  18. ,CaseAction.ActionTaken
  19. FROM [Warehouse].[Table].[Case] as Cases
  20. LEFT JOIN
  21. [Warehouse].[Table].[CaseParticipant] as Participant
  22. ON Cases.CaseNumber = Participant.CaseNumber
  23. LEFT JOIN
  24. [Warehouse].[Table].[CaseIssue] as Issues
  25. ON Participant.CaseParticipantId = Issues.CaseParticipantId
  26. LEFT JOIN
  27. [Warehouse].[Table].[CaseAction] as CaseAction
  28. ON Issues.CaseIssueId = CaseAction.CaseIssueId
  29. LEFT JOIN
  30. [Warehouse].[Table].[ParticipantAlias] as ParticipantAlias
  31. ON Participant.ParticipantAliasId = ParticipantAlias.ParticipantAliasId
  32. WHERE [Cases].[CaseNumber] = @caseNumber AND [CaseAction].ActionTaken &lt;&gt; &#39;See Previous Actions&#39;
  33. UNION ALL
  34. SELECT DISTINCT 
  35. t1.CaseNumber
  36. ,t1.DateOpened
  37. ,t1.DateClosed
  38. ,t1.CaseParticipantId
  39. ,t1.CaseParticipantKey
  40. ,t1.ParticipantAliasId
  41. ,t1.RoleInCase
  42. ,t1.RelToOrganization
  43. ,t1.Practice
  44. ,t1.City
  45. ,t1.Issue
  46. ,t1.IssueSubCategory
  47. ,t1.CaseIssueId
  48. ,t1.Outcome
  49. ,t1.ParticipantName
  50. ,t2.ActionTaken
  51. FROM (
  52. SELECT
  53. Cases.CaseNumber
  54. ,Cases.DateOpened
  55. ,Cases.DateClosed
  56. ,Participant.CaseParticipantId
  57. ,Participant.CaseParticipantKey
  58. ,Participant.ParticipantAliasId
  59. ,Participant.RoleInCase
  60. ,Participant.RelToOrganization
  61. ,Participant.Practice
  62. ,Participant.City
  63. ,Issues.Issue
  64. ,Issues.IssueSubCategory
  65. ,Issues.CaseIssueId
  66. ,Issues.Outcome
  67. ,ParticipantAlias.ParticipantName
  68. ,CaseAction.ActionTaken
  69. FROM [Warehouse].[Table].[Case] as Cases
  70. LEFT JOIN
  71. [Warehouse].[Table].[CaseParticipant] as Participant
  72. ON Cases.CaseNumber = Participant.CaseNumber
  73. LEFT JOIN
  74. [Warehouse].[Table].[CaseIssue] as Issues
  75. ON Participant.CaseParticipantId = Issues.CaseParticipantId
  76. LEFT JOIN
  77. [Warehouse].[Table].[CaseAction] as CaseAction
  78. ON Issues.CaseIssueId = CaseAction.CaseIssueId
  79. LEFT JOIN
  80. [Warehouse].[Table].[ParticipantAlias] as ParticipantAlias
  81. ON Participant.ParticipantAliasId = ParticipantAlias.ParticipantAliasId
  82. WHERE [Cases].[CaseNumber] = @caseNumber AND [CaseAction].ActionTaken = &#39;See Previous Actions&#39;
  83. ) t1
  84. INNER JOIN (
  85. SELECT
  86. Cases.CaseNumber
  87. ,Cases.DateOpened
  88. ,Cases.DateClosed
  89. ,Participant.CaseParticipantId
  90. ,Participant.CaseParticipantKey
  91. ,Participant.ParticipantAliasId
  92. ,Participant.RoleInCase
  93. ,Participant.RelToOrganization
  94. ,Participant.Practice
  95. ,Participant.City
  96. ,Issues.Issue
  97. ,Issues.IssueSubCategory
  98. ,Issues.CaseIssueId
  99. ,Issues.Outcome
  100. ,ParticipantAlias.ParticipantName
  101. ,CaseAction.ActionTaken
  102. FROM [Warehouse].[Table].[Case] as Cases
  103. LEFT JOIN
  104. [Warehouse].[Table].[CaseParticipant] as Participant
  105. ON Cases.CaseNumber = Participant.CaseNumber
  106. LEFT JOIN
  107. [Warehouse].[Table].[CaseIssue] as Issues
  108. ON Participant.CaseParticipantId = Issues.CaseParticipantId
  109. LEFT JOIN
  110. [Warehouse].[Table].[CaseAction] as CaseAction
  111. ON Issues.CaseIssueId = CaseAction.CaseIssueId
  112. LEFT JOIN
  113. [Warehouse].[Table].[ParticipantAlias] as ParticipantAlias
  114. ON Participant.ParticipantAliasId = ParticipantAlias.ParticipantAliasId
  115. WHERE [Cases].[CaseNumber] = @caseNumber AND [CaseAction].ActionTaken &lt;&gt; &#39;See Previous Actions&#39;
  116. ) t2 
  117. ON t2.CaseNumber = t1.CaseNumber AND t2.CaseParticipantId = t1.CaseParticipantId

huangapple
  • 本文由 发表于 2023年2月19日 03:57:33
  • 转载请务必保留本文链接：https://go.coder-hub.com/75496033.html
  • sql
  • sql-server
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定