62,046
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :SQL77(只为思齐老)
-- Date :2010-03-07 14:28:35
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#tb1
if object_id('tempdb.dbo.#tb1') is not null drop table #tb1
go
create table #tb1([id] int,[mf_Name] varchar(4),[customer_name] varchar(1),[dinid] int,[dinData] int)
insert #tb1
select 1,'海尔','a',1,1 union all
select 4,'海尔','a',3,1 union all
select 5,'海尔','a',4,2
--> 测试数据:#TB2
if object_id('tempdb.dbo.#TB2') is not null drop table #TB2
go
create table #TB2([id] int,[product_name] varchar(8),[product_price] int,[product_num] int,[product_sumprice] int)
insert #TB2
select 1,'海尔冰箱',2000,5,10000 union all
select 3,'海尔电视',1000,4,4000 union all
select 4,'海尔电脑',3000,5,15000
--------------开始查询--------------------------
DECLARE @S VARCHAR(8000)
SELECT @S=ISNULL(@S+',','')+
'SUM(CASE WHEN dinData='+LTRIM(NUMBER)+' THEN product_sumprice ELSE 0 END)AS '''+LTRIM(NUMBER)+''''
FROM MASTER..SPT_VALUES M
LEFT JOIN #TB1 T1 ON T1.dinid=M.NUMBER
LEFT JOIN #TB2 T2 ON T1.dinid=T2.ID
WHERE M.NUMBER BETWEEN 1 AND 12 AND M.TYPE='P'
EXEC('SELECT T2.product_name,'+@S+'
FROM #TB1 T1,#TB2 T2 WHERE T1.dinid=T2.ID GROUP BY T2.product_name')
----------------结果----------------------------
/*
(所影响的行数为 3 行)
(所影响的行数为 3 行)
product_name 1 2 3 4 5 6 7 8 9 10 11 12
------------ ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
海尔冰箱 10000 0 0 0 0 0 0 0 0 0 0 0
海尔电脑 0 15000 0 0 0 0 0 0 0 0 0 0
海尔电视 4000 0 0 0 0 0 0 0 0 0 0 0
*/
----------------------------------------------------------------
-- Author :SQL77(只为思齐老)
-- Date :2010-03-07 14:28:35
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#tb1
if object_id('tempdb.dbo.#tb1') is not null drop table #tb1
go
create table #tb1([id] int,[mf_Name] varchar(4),[customer_name] varchar(1),[dinid] int,[dinData] int)
insert #tb1
select 1,'海尔','a',1,1 union all
select 4,'海尔','a',3,1 union all
select 5,'海尔','a',4,2
--> 测试数据:#TB2
if object_id('tempdb.dbo.#TB2') is not null drop table #TB2
go
create table #TB2([id] int,[product_name] varchar(8),[product_price] int,[product_num] int,[product_sumprice] int)
insert #TB2
select 1,'海尔冰箱',2000,5,10000 union all
select 3,'海尔电视',1000,4,4000 union all
select 4,'海尔电脑',3000,5,15000
--------------开始查询--------------------------
DECLARE @S VARCHAR(8000)
SELECT @S=ISNULL(@S+',','')+
'SUM(CASE WHEN dinid='+LTRIM(NUMBER)+' THEN product_sumprice ELSE 0 END)AS '''+LTRIM(NUMBER)+''''
FROM MASTER..SPT_VALUES M
LEFT JOIN #TB1 T1 ON T1.dinid=M.NUMBER
LEFT JOIN #TB2 T2 ON T1.dinid=T2.ID
WHERE M.NUMBER BETWEEN 1 AND 12 AND M.TYPE='P'
EXEC('SELECT T2.product_name,'+@S+'
FROM #TB1 T1,#TB2 T2 WHERE T1.dinid=T2.ID GROUP BY T2.product_name')
----------------结果----------------------------
/*
(所影响的行数为 3 行)
(所影响的行数为 3 行)
product_name 1 2 3 4 5 6 7 8 9 10 11 12
------------ ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
海尔冰箱 10000 0 0 0 0 0 0 0 0 0 0 0
海尔电脑 0 0 0 15000 0 0 0 0 0 0 0 0
海尔电视 0 0 4000 0 0 0 0 0 0 0 0 0
*/