IF EXISTS(SELECT NAME FROM sys.objects AS o WHERE NAME = 'test') DROP TABLE test GO CREATE TABLE test(storeid VARCHAR(10) , months VARCHAR(06) , sales INT) GO INSERT INTO test SELECT '11111','201401' , 20 UNION ALL SELECT '11111','201402' , 30 UNION ALL SELECT '11111','201403' , 10 UNION ALL SELECT '11112','201401' , 10 UNION ALL SELECT '11112','201402' , 20 UNION ALL SELECT '11113','201401' , 50 UNION ALL SELECT '11114','201402' , 40 GO --执行查询 ;WITH a AS( SELECT storeid, CONVERT(VARCHAR(04), DATEPART(YEAR, GETDATE())) + CASE WHEN LEN(CONVERT(VARCHAR(02), s.number)) = 1 THEN '0' + CONVERT(VARCHAR(02), s.number) ELSE CONVERT(VARCHAR(02), s.number) END AS months, 0 AS sales FROM ( SELECT storeid FROM test GROUP BY storeid ) AS t, MASTER..spt_values AS s WHERE s.type = 'P' AND s.number < DATEPART(MONTH, GETDATE()) AND s.number <> 0 ) SELECT a.storeid , a.months , CASE WHEN test.sales IS NULL THEN 0 ELSE test.sales END AS sales FROM a LEFT JOIN test ON a.storeid = test.storeid AND a.months = test.months /*结果 storeid months sales ---------- ------- ----------- 11111 201401 20 11111 201402 30 11111 201403 10 11112 201401 10 11112 201402 20 11112 201403 0 11113 201401 50 11113 201402 0 11113 201403 0 11114 201401 0 11114 201402 40 11114 201403 0(12 行受影响) */ 你要的结果是不是有点问题
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2014-04-18 19:30:34 -- Version: -- Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (Intel X86) -- Sep 22 2011 00:28:06 -- Copyright (c) 1988-2008 Microsoft Corporation -- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([storeID] int,[MONTH] int,[SALES] int) insert [tb] select 11111,1,20 union all select 11111,2,30 union all select 11111,3,10 union all select 11112,1,10 union all select 11112,2,20 union all select 11113,1,50 union all select 11114,2,40 --------------开始查询-------------------------- select a.storeID,a.MONTH,ISNULL(b.SALES,0) as SALES from ( select * from (select distinct storeID from tb) as a cross join (select distinct MONTH from tb) as b ) a left join tb as b on a.storeID=b.storeID and a.MONTH=b.MONTH order by storeID,MONTH ----------------结果---------------------------- /* storeID MONTH SALES ----------- ----------- ----------- 11111 1 20 11111 2 30 11111 3 10 11112 1 10 11112 2 20 11112 3 0 11113 1 50 11113 2 0 11113 3 0 11114 1 0 11114 2 40 11114 3 0(12 行受影响) */
DROP TABLE test
GO
CREATE TABLE test(storeid VARCHAR(10) , months VARCHAR(06) , sales INT)
GO
INSERT INTO test
SELECT '11111','201401' , 20 UNION ALL
SELECT '11111','201402' , 30 UNION ALL
SELECT '11111','201403' , 10 UNION ALL
SELECT '11112','201401' , 10 UNION ALL
SELECT '11112','201402' , 20 UNION ALL
SELECT '11113','201401' , 50 UNION ALL
SELECT '11114','201402' , 40
GO
--执行查询
;WITH a AS(
SELECT storeid,
CONVERT(VARCHAR(04), DATEPART(YEAR, GETDATE())) + CASE
WHEN LEN(CONVERT(VARCHAR(02), s.number))
= 1 THEN
'0'
+
CONVERT(VARCHAR(02), s.number)
ELSE
CONVERT(VARCHAR(02), s.number)
END AS months,
0 AS sales
FROM (
SELECT storeid
FROM test
GROUP BY
storeid
) AS t,
MASTER..spt_values AS s
WHERE s.type = 'P'
AND s.number < DATEPART(MONTH, GETDATE())
AND s.number <> 0
)
SELECT a.storeid , a.months , CASE WHEN test.sales IS NULL THEN 0 ELSE test.sales END AS sales
FROM a LEFT JOIN test ON a.storeid = test.storeid AND a.months = test.months
/*结果
storeid months sales
---------- ------- -----------
11111 201401 20
11111 201402 30
11111 201403 10
11112 201401 10
11112 201402 20
11112 201403 0
11113 201401 50
11113 201402 0
11113 201403 0
11114 201401 0
11114 201402 40
11114 201403 0(12 行受影响) */ 你要的结果是不是有点问题
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-04-18 19:30:34
-- Version:
-- Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (Intel X86)
-- Sep 22 2011 00:28:06
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([storeID] int,[MONTH] int,[SALES] int)
insert [tb]
select 11111,1,20 union all
select 11111,2,30 union all
select 11111,3,10 union all
select 11112,1,10 union all
select 11112,2,20 union all
select 11113,1,50 union all
select 11114,2,40
--------------开始查询--------------------------
select a.storeID,a.MONTH,ISNULL(b.SALES,0) as SALES from
(
select * from (select distinct storeID from tb) as a cross join (select distinct MONTH from tb) as b
) a
left join
tb as b
on
a.storeID=b.storeID and a.MONTH=b.MONTH
order by storeID,MONTH
----------------结果----------------------------
/* storeID MONTH SALES
----------- ----------- -----------
11111 1 20
11111 2 30
11111 3 10
11112 1 10
11112 2 20
11112 3 0
11113 1 50
11113 2 0
11113 3 0
11114 1 0
11114 2 40
11114 3 0(12 行受影响)
*/