急求SQL

[fyming]

CREATE TABLE #0(F0 NVARCHAR(10),F1 VARCHAR(1),F2 INT)
INSERT INTO #0(F0,F1,F2)
SELECT '20181115','a',1 UNION ALL
SELECT '20181115','b',1 UNION ALL
SELECT '20181116','a',1

SELECT F0,F1,SUM(F2) FROM #0 GROUP BY F0,F1 ORDER BY F0,F1
--------------------------
结果：
F0 F1
20181115 a 1
20181115 b 1
20181116 a 1
--------------------------
想要结果：
F0 F1
20181115 a 1
20181115 b 1
20181116 a 1
20181116 b 0
--------------------------
也就是说，每天都要有 a、b两行，如果没有，用0补齐

[leo_lesley]

select t.F0,t.F1,isnull(a.F2,0) from (select * from (select f0 from #0 group by f0) a cross apply (select 'a' f1 union select 'b') b ) t left join #0 a on t.F0 = a.F0 and t.f1 = a.F1 

[二月十六]

SELECT t3.*,ISNULL(#0.F2,0) AS F2 FROM (
SELECT * FROM (
SELECT DISTINCT F0 FROM #0)t1,
(SELECT DISTINCT f1 FROM #0)t2)t3
LEFT JOIN #0 ON #0.F0 = t3.F0 AND #0.F1 = t3.F1

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('tempdb..#0') IS NOT NULL DROP TABLE #0
--
CREATE TABLE #0(F0 NVARCHAR(10),F1 VARCHAR(1),F2 INT)
INSERT INTO #0(F0,F1,F2)
SELECT '20181115','a',1 UNION ALL
SELECT '20181115','b',1 UNION ALL
SELECT '20181116','a',1 
-- 以上为测试数据 --

SELECT 
	tt.F0
	,tt.F1
	,ISNULL(n,0) AS n 
FROM 
(
	SELECT * FROM 
	(SELECT F1 FROM #0 GROUP BY F1) AS t1
	CROSS APPLY
	(SELECT F0 FROM #0 GROUP BY F0) AS t2
) AS tt 
LEFT JOIN 
(
	SELECT F0,F1,SUM(F2) AS n 
	FROM #0 
	GROUP BY F0,F1 
) AS t 
ON tt.F1=t.F1 AND tt.f0=t.f0
ORDER BY tt.F0
/*
F0         F1   n
---------- ---- -----------
20181115   a    1
20181115   b    1
20181116   a    1
20181116   b    0
 */

[二月十六]

加一个order by

SELECT t3.*,ISNULL(#0.F2,0) AS F2 FROM (
SELECT * FROM (
SELECT DISTINCT F0 FROM #0)t1,
(SELECT DISTINCT f1 FROM #0)t2)t3
LEFT JOIN #0 ON #0.F0 = t3.F0 AND #0.F1 = t3.F1
ORDER BY F0