select t.mm,e.ID
from(
  select a.ID,b.[month] as mm
  from (select distinct ID from tb) a cross join (select distinct [month] from tb) b
)t left join tb e on t.ID = e.ID and t.mm = e.[month]

------解决方案--------------------
探讨

谢谢........
顶起来....我的数据表好复杂(三个表连起来)...如果把tb 换成我的那一堆可以是可以,但麻烦......有没有更好的办法


------解决方案--------------------
SQL code

;with cte as(
此处放你的那一堆
)
此处放1楼的那一堆,并把表名改成 cte

------解决方案--------------------
我补句2005+，蹭分
探讨

SQL code

;with cte as(
此处放你的那一堆
)
此处放1楼的那一堆,并把表名改成 cte


------解决方案--------------------
SQL code



--创建测试表
CREATE TABLE #T
(
    ID varchar(2),
    month int
)

--插入测试数据
INSERT INTO #T
SELECT 'A',1 UNION ALL
SELECT 'B',1 UNION ALL
SELECT 'C',1 UNION ALL
SELECT 'A',2 UNION ALL
SELECT 'C',2 UNION ALL
SELECT 'A',3 UNION ALL
SELECT 'B',3 

SELECT A.month,ISNULL(B.ID,'') AS ID  FROM 
(SELECT month,ID FROM
(SELECT DISTINCT ID FROM #T) A,
(SELECT DISTINCT month FROM #T) B) A LEFT JOIN #T B
ON A.month=B.month and A.ID=B.ID
ORDER BY month,A.ID


--删除测试表
DROP TABLE #T