数据补齐

[qch2015qch]

有2张表
DECLARE @T1 TABLE (NAME VARCHAR(20), DATE datetime, VALUE VARCHAR(10))
DECLARE @T2 TABLE (date datetime)

INSERT INTO @T1 VALUES('A', '2018-1-1', 'V')
INSERT INTO @T1 VALUES('A', '2018-1-2', 'V')
INSERT INTO @T1 VALUES('B', '2018-1-1', 'V')
INSERT INTO @T1 VALUES('C', '2018-1-1', 'V')

INSERT INTO @T2 VALUES('2018-1-1')
INSERT INTO @T2 VALUES('2018-1-2')
INSERT INTO @T2 VALUES('2018-1-3')

期望的结果是
-----------
A, 'V', '2018-1-1'
A, 'V', '2018-1-2'
A, NULL, '2018-1-3'
B, 'V', '2018-1-1'
B, NULL, '2018-1-2'
B, NULL, '2018-1-3'
C, 'V', '2018-1-1'
C, NULL, '2018-1-2'
C, NULL, '2018-1-3'
求解答~

[在路上_-]

select distinct a.name, b.date from @T1 a cross join @T2 b

[zbdzjx]

with t1(NAME, DATE , VALUE) as
(
  select 'A', '2018-1-1', 'V' union all
  select 'A', '2018-1-2', 'V' union all
  select 'B', '2018-1-1', 'V' union all
  select 'C', '2018-1-1', 'V'
)
, t2(date) as
(
  select '2018-1-1' union all
  select '2018-1-2' union all
  select '2018-1-3'
)
select a.NAME, b.VALUE, a.date from 
(select distinct t1.NAME, t2.date from t1, t2) a
left join t1 b on a.NAME=b.NAME and a.date=b.DATE

[RINK_1]

DECLARE @T1 TABLE (NAME VARCHAR(20), DATE datetime, VALUE VARCHAR(10))
DECLARE @T2 TABLE (date datetime)

INSERT INTO @T1 VALUES('A', '2018-1-1', 'V')
INSERT INTO @T1 VALUES('A', '2018-1-2', 'V')
INSERT INTO @T1 VALUES('B', '2018-1-1', 'V')
INSERT INTO @T1 VALUES('C', '2018-1-1', 'V')

INSERT INTO @T2 VALUES('2018-1-1')
INSERT INTO @T2 VALUES('2018-1-2')
INSERT INTO @T2 VALUES('2018-1-3')

SELECT A.NAME,A.date,B.VALUE FROM
(SELECT *
FROM
(SELECT DISTINCT NAME from @T1) AS A
JOIN @T2 B ON 1=1) AS A
LEFT JOIN @T1 B ON A.date=B.date AND A.NAME=B.NAME