SQL案例問題

SELECT
   CASE
       WHEN OfferSum.DayOfWeek = 'Monday' THEN 'Mon'
       WHEN OfferSum.DayOfWeek = 'Tuesday' THEN 'Tues'
       WHEN OfferSum.DayOfWeek = 'Wednesday' THEN 'Wed'
       WHEN OfferSum.DayOfWeek = 'Thursday' THEN 'Thurs'
       WHEN OfferSum.DayOfWeek = 'Friday' THEN 'Fri'
       WHEN OfferSum.DayOfWeek = 'Saturday' THEN 'Sat'
       WHEN OfferSum.DayOfWeek = 'Sunday' THEN 'Sun'
   END [DayOfWeek]
  ,OfferSum.TotalCount [TotalCount]
FROM (Select COUNT(*) [TotalCount] FROM Offer) AS OfferSum

預期產出

我的 8

週二 0

週三 1

星期四 1

週五 0

星期六 2

太陽 0

但是沒有數據的天數沒有顯示在表格中。我該怎麼做？

SQL 無法返回不存在的數據…如果您的數據中沒有星期一，SQL 將不會“猜測”您在星期一期待什麼……（這種情況與您在這裡所期待的不符）。

如果您希望 SQL 向您顯示每天的數據，那麼您需要一個包含所有日期的查找表，然後您可以簡單地對其他表進行左連接。

這是一個可以用作起點的快速範例：

create table #MyData (Day_week varchar(10), totalcount int);
insert into #MyData values ('monday',1),('monday',5),('monday',3),
('friday',3),('friday',8),
('saturday',12),('saturday',2),('saturday',1),('saturday',1);

-- This is similar to your example, counting the # of totalcount for each day. This does not show missing date
SELECT
   CASE
       WHEN Day_week = 'Monday' THEN 'Mon'
       WHEN Day_week= 'Tuesday' THEN 'Tues'
       WHEN Day_week = 'Wednesday' THEN 'Wed'
       WHEN Day_week = 'Thursday' THEN 'Thurs'
       WHEN Day_week = 'Friday' THEN 'Fri'
       WHEN Day_week = 'Saturday' THEN 'Sat'
       WHEN Day_week = 'Sunday' THEN 'Sun'
   END [DayOfWeek]
  ,count(TotalCount) [TotalCount]
from #MyData
group by CASE
       WHEN Day_week = 'Monday' THEN 'Mon'
       WHEN Day_week= 'Tuesday' THEN 'Tues'
       WHEN Day_week = 'Wednesday' THEN 'Wed'
       WHEN Day_week = 'Thursday' THEN 'Thurs'
       WHEN Day_week = 'Friday' THEN 'Fri'
       WHEN Day_week = 'Saturday' THEN 'Sat'
       WHEN Day_week = 'Sunday' THEN 'Sun'
   END;

-- This is the lookup table to help you accomplish what you want
create table #lookup (ID int, Day_week varchar(10), Short_Day varchar(5));
insert into #lookup values (1,'Monday','Mon'),(2,'Tuesday','Tues'),(3,'Wednesday','Wed'),(4,'Thursday','Thurs'),(5,'Friday','Fri'),(6,'Saturday','Sat'),(7,'Sunday','Sun');

select  L.Short_Day, count(d.totalCount) "Count of totalCount"
from #lookup L 
left join #MyData D on L.Day_week=D.Day_week
group by L.ID, L.Short_Day
order by L.ID;

-- Cleaning up
drop table #MyData;
drop table #lookup;

請注意，我在查找表中添加了一個“ID”列，這只是為了能夠按天顯示結果順序（您可能不希望輸出為“星期五、星期一、星期六……”

引用自：https://dba.stackexchange.com/questions/300571