SQL插入重復項

我的代碼如下：

SELECT DISTINCT
    lc.locationName,
    (SELECT SUM(c.TimeSlotsCount) FROM Schedule s 
     WHERE s.ServiceId = sv.ServiceId 
       AND sv.LocationId = lc.LocationId 
       AND sv.ServiceName LIKE '%TYPE_ONE%' 
       AND s.Date >= GETDATE()) AS TYPE_ONE_COUNT,
    (SELECT SUM(c.TimeSlotsCount) FROM Schedule s 
     WHERE s.ServiceId = sv.ServiceId 
       AND sv.LocationId = lc.LocationId 
       AND sv.ServiceName LIKE '%TYPE_TWO%' 
       AND s.Date >= GETDATE()) AS TYPE_TWO_COUNT,
    (SELECT SUM(c.TimeSlotsCount) FROM Schedule s 
     WHERE s.ServiceId = sv.ServiceId 
       AND sv.LocationId = lc.LocationId 
       AND sv.ServiceName LIKE '%TYPE_THREE%' 
       AND s.Date >= GETDATE()) AS TYPE_THREE_COUNT
FROM 
    Schedule AS sc WITH (NOLOCK) --/*This table has timeslotscount, serviceid*/
LEFT JOIN 
    ServiceList AS sv ON sv.ServiceId = sc.ServiceId --/*This table has locationid, serviceid(type1/type2/type3 depending on location[all locations has type1/2/3])*/
LEFT JOIN 
    Location AS lc ON sv.LocationId = lc.LocationId --/*This table has  locationid, locationname*/

我應該解釋一下SUM()，時間表上有不同的日期，例如

serviceId | dd/mm/yyyy hh:mm:ss | count for this day

我想獲得今天之后的“serviceId”的所有“計數”，因此“日期> = GETDATE（）”

基本上我希望表格看起來像這樣：

LOCATION | TYPE_ONE_COUNT | TYPE_TWO_COUNT | TYPE_THREE_COUNT

我能夠得到這些值，但我得到它們是這樣的：

LocationOne | 12 |  0 |  0   
LocationOne | 0  | 12 |  0  
LocationOne | 0  |  0 | 34  
LocationTwo | 1  |  0 |  0  
LocationTwo | 0  | 42 |  0  
LocationTwo | 0  | 0  |  9

而我想顯示為

LocationOne | 12 | 12 | 34  
LocationTwo | 1  | 42 |  9

任何和所有的幫助表示贊賞。我錯的地方，語法/性能改進，參考，任何東西。

uj5u.com熱心網友回復：

您可以使用條件聚合來做到這一點：

SELECT lc.locationName,
       SUM(CASE WHEN sv.ServiceName LIKE '%TYPE_ONE%' THEN sc.TimeSlotsCount ELSE 0 END) AS TYPE_ONE_COUNT,
       SUM(CASE WHEN sv.ServiceName LIKE '%TYPE_TWO%' THEN sc.TimeSlotsCount ELSE 0 END) AS TYPE_TWO_COUNT,
       SUM(CASE WHEN sv.ServiceName LIKE '%TYPE_THREE%' THEN sc.TimeSlotsCount ELSE 0 END) AS TYPE_THREE_COUNT 
FROM Location AS lc 
LEFT JOIN ServiceList AS sv ON sv.LocationId = lc.LocationId
LEFT JOIN Schedule AS sc ON sv.ServiceId = sc.ServiceId AND sc.Date >= GETDATE() 
GROUP BY lc.LocationId, lc.locationName;

uj5u.com熱心網友回復：

幾種方式。一種是簡單地執行最后一個操作：

小提琴

另請注意：我調整了原始 SQL，因為c.TimeSlotsCount它不存在。您沒有c表/別名。我添加TimeSlotsCount到Schedule表中。根據TimeSlotsCount.

WITH cte AS (
        Your above SQL from the question, without semicolon
     )
SELECT locationName
     , SUM(TYPE_ONE_COUNT)   AS TYPE_ONE_COUNT
     , SUM(TYPE_TWO_COUNT)   AS TYPE_TWO_COUNT
     , SUM(TYPE_THREE_COUNT) AS TYPE_THREE_COUNT
  FROM cte
 GROUP BY locationName
;

我們也可以在原始 SQL 中做一個稍微不同的 SUM，不用DISTINCT和GROUP BY，避免額外的 CTE 術語。

但從邏輯上講，這些是相同的。

最終 SQL，直接使用您的 SQL：

WITH cte AS (
      SELECT DISTINCT
          lc.locationName,
          (SELECT SUM(s.TimeSlotsCount) FROM Schedule s 
           WHERE s.ServiceId = sv.ServiceId 
             AND sv.LocationId = lc.LocationId 
             AND sv.ServiceName LIKE '%TYPE_ONE%' 
             AND s.Date >= GETDATE()) AS TYPE_ONE_COUNT,
          (SELECT SUM(s.TimeSlotsCount) FROM Schedule s 
           WHERE s.ServiceId = sv.ServiceId 
             AND sv.LocationId = lc.LocationId 
             AND sv.ServiceName LIKE '%TYPE_TWO%' 
             AND s.Date >= GETDATE()) AS TYPE_TWO_COUNT,
          (SELECT SUM(s.TimeSlotsCount) FROM Schedule s 
           WHERE s.ServiceId = sv.ServiceId 
             AND sv.LocationId = lc.LocationId 
             AND sv.ServiceName LIKE '%TYPE_THREE%' 
             AND s.Date >= GETDATE()) AS TYPE_THREE_COUNT
      FROM 
          Schedule AS sc WITH (NOLOCK) --/*This table has timeslotscount, serviceid*/
      LEFT JOIN 
          ServiceList AS sv ON sv.ServiceId = sc.ServiceId --/*This table has locationid, serviceid(type1/type2/type3 depending on location[all locations has type1/2/3])*/
      LEFT JOIN 
          Location AS lc ON sv.LocationId = lc.LocationId --/*This table has  locationid, locationname*/
     )
SELECT locationName
     , SUM(TYPE_ONE_COUNT)   AS TYPE_ONE_COUNT
     , SUM(TYPE_TWO_COUNT)   AS TYPE_TWO_COUNT
     , SUM(TYPE_THREE_COUNT) AS TYPE_THREE_COUNT
  FROM cte
 GROUP BY locationName
;

標籤：sql sql-server 数据库

上一篇：如何以干凈的方式回顯串列的幾個名稱

下一篇：當列不早于特定日期時選擇唯一名稱