SQL Server连接

Question

我目前正在处理的查询返回了所需的结果，但问题在于我必须为每个额外要添加的列创建一个具有不同TaskCounters的附加连接(这显然不是一个理性的决定)。

这个查询应该在SQLServer和Access DB上运行，因此我不需要任何特殊的函数(比如Pivot、CTE等)，这些函数在Access中不受支持。我需要修改这个查询，以尽可能减少连接的数量。

tblConsultations是主表，其中根据访问类型记录了特定儿童的条目(即如果我们有4次访问，我们有4个Consultations的条目)。结果列应显示那些4个访问的值，并在没有对某个访问的值时显示NULL。

我想消除从同一表中添加每个额外列时需要的附加连接

查询如下：

SELECT Cast(SUBSTRING(tc2.ChildCounter, 7, LEN(tc2.ChildCounter)) AS NUMERIC) AS pkChildID
,tc2.VisitType
,tblQuitOffered.Result AS KWA_QuitOffered
,tblQuitReferral.Result As KWA_QuitReferral
FROM tblConsultations tc2
INNER JOIN tblChild tc ON tc2.ChildCounter = tc.ChildCounter
LEFT  JOIN tblDelivery td ON td.ChildCounter = tc.ChildCounter
LEFT JOIN (
SELECT ttr.ResultCounter
    ,ttr.ChildCounter
    ,tkt.VisitType
    ,ttr.Result
    ,ttr.TaskCounter
FROM tblTaskResults ttr
INNER JOIN tlkpKeyTasks tkt ON tkt.TaskCounter = ttr.TaskCounter
    AND tkt.TaskCounter IN (
        '001410'
        ,'001463'
        ,'001431'
        )

) AS tblQuitOffered ON tc2.VisitType = tblQuitOffered.VisitType
AND tblQuitOffered.ChildCounter = tc2.ChildCounter
LEFT JOIN (
SELECT  ttr.ChildCounter
    ,tkt.VisitType
    ,ttr.Result
FROM tblTaskResults ttr
INNER JOIN tlkpKeyTasks tkt ON tkt.TaskCounter = ttr.TaskCounter
    AND tkt.TaskCounter IN (
        '001411'
        ,'001464'
        ,'001432'
        )
) AS tblQuitReferral ON tc2.VisitType = tblQuitReferral.VisitType
AND tblQuitReferral.ChildCounter = tc2.ChildCounter
WHERE tc2.VisitType in (1, 2, 3, 4)
AND tc2.ConsDate BETWEEN '20130127' and '20130228'
ORDER BY tc2.ChildCounter,tc2.VisitType

结果如下所示：

pkChildID   VisitType     KWA_QuitOffered   KWA_QuitReferral
2224        1             No                No
2224        3             NULL              NULL
2224        4             NULL              NULL
2225        1             No                Yes
2225        2             Yes               Yes
2225        3             Yes               Yes
2225        4             NULL              NULL

- AMS

---

我认为你不会找到一个仅适用于SQL Server和MS Access的令人满意的答案。正如bluefeet所提到的，SQL语法本身就是不同的。你可能需要针对每个环境使用不同的解决方案。 - BellevueBob

@BellevueBob 我不同意，AMS 需要的只是 GROUP BY 子句。但让我感到困惑的是在确定 QuitOffered 和 QuitReferral 时使用 TaskCounter。 - Robert Co

@BellevueBob - 我相信我找到了答案 :D - Hogan

Answer 1

如果我理解正确，您可以在一个派生表中包含所有想要的TaskCounter值，然后使用CASE语句来分配列值：

SELECT Cast(SUBSTRING(tc2.ChildCounter, 7, LEN(tc2.ChildCounter)) 
            AS NUMERIC) AS pkChildID
      ,tc2.VisitType

      ,MAX(CASE WHEN tktResults.TaskCounter IN (
                '001410'
               ,'001463'
               ,'001431'
               ) THEN tktResults.Result END
          ) AS KWA_QuitOffered

      ,MAX(CASE WHEN tktResults.TaskCounter IN (
                '001411'
               ,'001464'
               ,'001432'
               ) THEN tktResults.Result END
          ) AS KWA_QuitReferral

FROM  tblConsultations tc2
INNER JOIN tblChild tc 
  ON  tc2.ChildCounter = tc.ChildCounter
LEFT  JOIN tblDelivery td
  ON  td.ChildCounter = tc.ChildCounter

LEFT JOIN (
   SELECT ttr.ResultCounter
         ,ttr.ChildCounter
         ,tkt.VisitType
         ,ttr.Result
         ,ttr.TaskCounter
   FROM tblTaskResults ttr
   INNER JOIN tlkpKeyTasks tkt 
   ON tkt.TaskCounter = ttr.TaskCounter
       AND tkt.TaskCounter IN (
            '001410'
           ,'001463'
           ,'001431'
           ,'001411'
           ,'001464'
           ,'001432'
           )
   ) AS tktResults 
ON     tktResults.VisitType    = tc2.VisitType
   AND tktResults.ChildCounter = tc2.ChildCounter

WHERE tc2.VisitType in (1, 2, 3, 4)
  AND tc2.ConsDate BETWEEN '20130127' and '20130228'
GROUP BY 1, 2
ORDER BY tc2.ChildCounter,tc2.VisitType

事实上，您不需要将其作为派生表（LEFT JOIN (...) 部分），但我假设您这样做是出于性能考虑。随着您基于不同的 TaskCounter 值添加列，只需将它们添加到连接条件中即可。

编辑：修订使用带有 MAX 函数的 GROUP BY 子句返回按 pkChildID 和 VisitType 唯一的行。

我在 GROUP BY 子句中使用了 "有序列表" 样式；如果不支持，您可能需要像这样指定：

GROUP BY Cast(SUBSTRING(tc2.ChildCounter, 7, LEN(tc2.ChildCounter)) AS NUMERIC)
        ,tc2.VisitType

我还注意到您正在按一个不在SELECT子句中的列进行排序。我不确定原因，但将其保留不变。

- BellevueBob

---

Ms Access 中没有 CASE。 - Taryn

@bluefeet 显然不行；但我觉得可以使用VBA。我错过了包括MS Access的可移植解决方案的部分。我自己不太常用Access来进行“真正”的工作。 - BellevueBob

嗨BellevueBob，你的回答非常接近我的要求，只是现在我看到每个pkChildID有2条记录，而不是一条。进一步说明，之前结果集中有7条记录（2225儿童的4种访问类型和2224儿童的3种访问类型），但现在它向我展示了11条记录。 - AMS

区别在于之前我以以下方式获取结果集： pkChildID VisitType KWA_QuitOffered KWA_QuitReferral 2224 1 No No 但现在变成了 pkChildID VisitType KWA_QuitOffered KWA_QuitReferral 2224 1 No NULL 2224 1 NULL No 请帮忙。 - AMS

只需要使用带有 MAX 函数的 GROUP BY 表达式即可。我会更新答案。 - BellevueBob

Answer 2

我正在发布一个类似于BellevueBob发布的答案。

我认为你可以使用嵌套的iif()代替'case when'

- Fuad

---

一次网络搜索还向我展示了SWITCH函数，它在Access中可能有效，但遗憾的是，在SQL Server中两者都不适用。 - BellevueBob

根据SQL Server的版本，可能不支持IIF。它是在SQL Server 2012中才可用。 - Taryn

哦，我明白了。你想要一些既可以在 SQL 中运行又可以在 MS Access 中运行的东西。是啊，你说得对，BellevueBob。 - Fuad

我到目前为止还没有尝试过 SQL 2012，但我可以告诉你...如果你能通过某种方式动态检测到你正在使用的数据中心...也许你可以替换你的查询...嗯？ - Fuad

Answer 3

通常在SQL中，创建一个新表可以解决问题。我们称之为coderesolve。

它看起来像这样：

Code    QuitOffered    QuitReferral
001410  1              Null
001462  1              Null
001431  1              Null
001411  Null           1
001464  Null           1
001432  Null           1

您可以为想要进行的每个“新”连接添加更多列。

然后您的代码看起来像这样

SELECT 
  Cast(SUBSTRING(tc2.ChildCounter, 7, LEN(tc2.ChildCounter)) AS NUMERIC) 
     AS pkChildID
  ,tc2.VisitType
  ,ttr.Result AS KWA_QuitOffered
  ,ttr.Result As KWA_QuitReferral
FROM tblConsultations tc2
INNER JOIN tblChild tc ON tc2.ChildCounter = tc.ChildCounter
LEFT  JOIN tblDelivery td ON td.ChildCounter = tc.ChildCounter
JOIN  tblTaskResults ttr on tc2.VisitType = ttr.VisitType
JOIN  tlkpKeyTasks tkt ON tkt.TaskCounter = ttr.TaskCounter
JOIN  coderesolve cr on cr.code = tkt.TaskCounter 
   AND COALESCE(cr.QuitOffered,cr.QuitReferral,0) = 1
WHERE tc2.VisitType in (1, 2, 3, 4)
AND tc2.ConsDate BETWEEN '20130127' and '20130228'
ORDER BY tc2.ChildCounter,tc2.VisitType

这应该可以在Access上运行。但是Access有时候很奇怪，所以不能保证。

另外需要注意的是，我假设你的代码是互斥的（这将在coderesolve连接中给你一个结果行），如果不是这种情况，你可能需要将其拆分为子查询并进行分组或去重 - 这取决于你的模型。

- Hogan