IF OBJECT_ID('tempdb..#test','U') IS NOT NULL DROP TABLE #test CREATE TABLE #test ( YearMonth CHAR(6) ,FootName NVARCHAR(20) ,Price DECIMAL(9,1) ) INSERT INTO #test SELECT '201401', N'水果', 3.2 UNION ALL SELECT '201401', N'蔬菜', 3.1 UNION ALL SELECT '201401', N'水果', 3.5 UNION ALL SELECT '201402', N'蔬菜', 5.2SELECT A.YearMonth,A.FootName,ISNULL(SUM(B.Price),0) AS price FROM ( SELECT * FROM (SELECT DISTINCT YearMonth FROM #test) AS A CROSS JOIN (SELECT DISTINCT FootName FROM #test) AS B ) AS A LEFT JOIN #test AS B ON A.YearMonth=B.YearMonth AND A.FootName=B.FootName GROUP BY A.YearMonth,A.FootName ORDER BY A.YearMonth/* YearMonth FootName price 201401 水果 6.7 201401 蔬菜 3.1 201402 水果 0.0 201402 蔬菜 5.2 */
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2014-06-26 10:53:28 -- Version: -- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86) -- Feb 10 2012 19:13:17 -- Copyright (c) Microsoft Corporation -- Enterprise Edition: Core-based Licensing 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]([a] int,[b] varchar(4),[c] numeric(2,1)) insert [tb] select 201401,'水果',3.2 union all select 201401,'蔬菜',3.1 union all select 201401,'水果',3.5 union all select 201402,'蔬菜',5.2 --------------开始查询-------------------------- select b.a,b.b,isnull(a.c,0) as c from (select a,b,sum(c) as c from [tb]group by a,b) as a right join (select * from(select distinct a from tb) as c cross join (select distinct b from tb) as b) as b on a.a=b.a and a.b=b.b ----------------结果---------------------------- /* a b c ----------- ---- --------------------------------------- 201401 蔬菜 3.1 201402 蔬菜 5.2 201401 水果 6.7 201402 水果 0.0(4 行受影响) */
CREATE TABLE #test
(
YearMonth CHAR(6)
,FootName NVARCHAR(20)
,Price DECIMAL(9,1)
)
INSERT INTO #test
SELECT '201401', N'水果', 3.2 UNION ALL
SELECT '201401', N'蔬菜', 3.1 UNION ALL
SELECT '201401', N'水果', 3.5 UNION ALL
SELECT '201402', N'蔬菜', 5.2SELECT A.YearMonth,A.FootName,ISNULL(SUM(B.Price),0) AS price
FROM (
SELECT *
FROM
(SELECT DISTINCT YearMonth FROM #test) AS A
CROSS JOIN
(SELECT DISTINCT FootName FROM #test) AS B
) AS A
LEFT JOIN #test AS B ON A.YearMonth=B.YearMonth AND A.FootName=B.FootName
GROUP BY A.YearMonth,A.FootName
ORDER BY A.YearMonth/*
YearMonth FootName price
201401 水果 6.7
201401 蔬菜 3.1
201402 水果 0.0
201402 蔬菜 5.2
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-06-26 10:53:28
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
-- Feb 10 2012 19:13:17
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing 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]([a] int,[b] varchar(4),[c] numeric(2,1))
insert [tb]
select 201401,'水果',3.2 union all
select 201401,'蔬菜',3.1 union all
select 201401,'水果',3.5 union all
select 201402,'蔬菜',5.2
--------------开始查询--------------------------
select b.a,b.b,isnull(a.c,0) as c from (select a,b,sum(c) as c from [tb]group by a,b) as a right join
(select * from(select distinct a from tb) as c cross join (select distinct b from tb) as b) as b on a.a=b.a and a.b=b.b
----------------结果----------------------------
/* a b c
----------- ---- ---------------------------------------
201401 蔬菜 3.1
201402 蔬菜 5.2
201401 水果 6.7
201402 水果 0.0(4 行受影响)
*/