SQL案例问题

Question

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

预期产出

Mon 8

图恩斯0

Wed 1

星期四1

Fri 0

卫星2号

太阳0

但没有数据的日子没有在表中显示。我该怎么做呢？

Answer 1

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“列，这只能显示每天的结果顺序(您可能不希望输出为”星期五、星期一、星期六……“。