数据样表: 地区 位置 优 良 不合格 时间 深圳 市区 1000 6000 500 2009-07-19
东莞 郊区 6060 900 100 2009-07-10
深圳 流动 1800 8800 800 2009-07-19
广州 市区 9000 67000 900 2009-07-20
……
……
给定一个时间,得到以下结构的查询结果:
地区 市区 郊区 流动
优 良 不合格 优 良 不合格 优 良 不合格
广州 @@@@@ @@@@ @@@ @@@@ @@@@ @@@ @@@@ @@ @@@@
佛山 @@@@@ @@@@ @@@ @@@@ @@@@ @@@ @@@@ @@ @@@@
深圳 @@@@@ @@@@ @@@ @@@@ @@@@ @@@ @@@@ @@ @@@@
东莞 @@@@@ @@@@ @@@ @@@@ @@@@ @@@ @@@@ @@ @@@@
……
揭阳 @@@@@ @@@@ @@@ @@@@ @@@@ @@@ @@@@ @@ @@@@
1、地区字段会因位置和时间字段的不同而出现多次重复
2、位置字段只有三种情况:市区、郊区、流动3、结果的地区字段记录没有重复,而且要按照一定顺序(例如地区的发达程度,有二十一个地区)!
希望各位高手帮忙解决,小弟感激不禁!
东莞 郊区 6060 900 100 2009-07-10
深圳 流动 1800 8800 800 2009-07-19
广州 市区 9000 67000 900 2009-07-20
……
……
给定一个时间,得到以下结构的查询结果:
地区 市区 郊区 流动
优 良 不合格 优 良 不合格 优 良 不合格
广州 @@@@@ @@@@ @@@ @@@@ @@@@ @@@ @@@@ @@ @@@@
佛山 @@@@@ @@@@ @@@ @@@@ @@@@ @@@ @@@@ @@ @@@@
深圳 @@@@@ @@@@ @@@ @@@@ @@@@ @@@ @@@@ @@ @@@@
东莞 @@@@@ @@@@ @@@ @@@@ @@@@ @@@ @@@@ @@ @@@@
……
揭阳 @@@@@ @@@@ @@@ @@@@ @@@@ @@@ @@@@ @@ @@@@
1、地区字段会因位置和时间字段的不同而出现多次重复
2、位置字段只有三种情况:市区、郊区、流动3、结果的地区字段记录没有重复,而且要按照一定顺序(例如地区的发达程度,有二十一个地区)!
希望各位高手帮忙解决,小弟感激不禁!
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
FROM @t a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END--显示结果
SELECT SPACE(b.Level*2)+'|--'+a.Name
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
|--山东省
|--烟台市
|--招远市
|--青岛市
|--四会市
|--清远市
|--小分市
--*/
DECLARE @a TABLE(No varchar(10),Name varchar(10))
INSERT @a SELECT '101' ,'现金'
UNION ALL SELECT '102' ,'银行存款'
UNION ALL SELECT '10201','工行'
UNION ALL SELECT '10202','建行'
UNION ALL SELECT '10203','农行'DECLARE @b TABLE(No varchar(10),[Money] money )
INSERT @b SELECT '101' ,100
UNION ALL SELECT '10201',20
UNION ALL SELECT '10202',120--逐级汇总查询
SELECT a.No,a.Name,
[Money]=ISNULL(SUM([Money]),0)
FROM @a a
LEFT JOIN @b b ON b.No LIKE a.No+'%'
GROUP BY a.No,a.Name
ORDER BY a.No
/*--结果
No Name Money
---------------- ----------------- ---------------------
101 现金 100.0000
102 银行存款 140.0000
10201 工行 20.0000
10202 建行 120.0000
10203 农行 .0000
--*/
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GO--广度搜索排序函数
CREATE FUNCTION f_Sort(@ID char(3)=NULL,@sort int=1)
RETURNS @t_Level TABLE(ID char(3),sort int)
AS
BEGIN
DECLARE tb CURSOR LOCAL
FOR
SELECT ID FROM tb
WHERE PID=@ID
OR(@ID IS NULL AND PID IS NULL)
OPEN TB
FETCH tb INTO @ID
WHILE @@FETCH_STATUS=0
BEGIN
INSERT @t_Level VALUES(@ID,@sort)
SET @sort=@sort+1
IF @@NESTLEVEL<32 --如果递归层数未超过32层(递归最大允许32层)
BEGIN
--递归查找当前节点的子节点
INSERT @t_Level SELECT * FROM f_Sort(@ID,@sort)
SET @sort=@sort+@@ROWCOUNT --排序号加上子节点个数
END
FETCH tb INTO @ID
END
RETURN
END
GO--显示结果
SELECT a.*
FROM tb a,f_Sort(DEFAULT,DEFAULT) b
WHERE a.ID=b.ID
ORDER BY b.sort
/*--结果
ID PID Name
------ --------- ----------
001 NULL 山东省
002 001 烟台市
004 002 招远市
003 001 青岛市
005 NULL 四会市
006 005 清远市
007 006 小分市
--*/太晚了 睡觉了 楼主看看给你的例子吧
[市区|优]=max(case when 位置='市区' then 优 else 0 end),
[市区|良]=max(case when 位置='市区' then 良 else 0 end),
[市区|不合格]=max(case when 位置='市区' then 不合格 else 0 end),
[郊区|优]=max(case when 位置='郊区' then 优 else 0 end),
[郊区|良]=max(case when 位置='郊区' then 良 else 0 end),
[郊区|不合格]=max(case when 位置='郊区' then 不合格 else 0 end), [郊区|优]=max(case when 位置='郊区' then 优 else 0 end),
[郊区|良]=max(case when 位置='流动' then 良 else 0 end),
[郊区|不合格]=max(case when 位置='流动' then 不合格 else 0 end)
from TB group by 地区
Select @s=isnull(@s+',','')+'['+位置+'/优]=max(case when [位置]='+quotename([位置],'''')+' then [优] else 0 end),
['+位置+'/良]=max(case when [位置]='+quotename([位置],'''')+' then [良] else 0 end),
['+位置+'/不合格]=max(case when [位置]='+quotename([位置],'''')+' then [不合格] else 0 end)'
from TB
exec('select [地区],'+@s+' from TB group by [地区]')
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-17 02:19:45
---------------------------------
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([地区] nvarchar(2),[位置] nvarchar(2),[优] int,[良] int,[不合格] int,[时间] Datetime)
Insert tb
Select '深圳','市区',1000,6000,500,'2009-07-19' union all
Select '东莞','郊区',6060,900,100,'2009-07-10' union all
Select '深圳','流动',1800,8800,800,'2009-07-19' union all
Select '广州','市区',9000,67000,900,'2009-07-20'
Go
--Select * from tb-->SQL查询如下:
select [地区],
[市区/优]=max(case when [位置]='市区' then [优] else 0 end),
[市区/良]=max(case when [位置]='市区' then [良] else 0 end),
[市区/不合格]=max(case when [位置]='市区' then [不合格] else 0 end),
[郊区/优]=max(case when [位置]='郊区' then [优] else 0 end),
[郊区/良]=max(case when [位置]='郊区' then [良] else 0 end),
[郊区/不合格]=max(case when [位置]='郊区' then [不合格] else 0 end),
[流动/优]=max(case when [位置]='流动' then [优] else 0 end),
[流动/良]=max(case when [位置]='流动' then [良] else 0 end),
[流动/不合格]=max(case when [位置]='流动' then [不合格] else 0 end)
from tb
group by [地区]
/*
地区 市区/优 市区/良 市区/不合格 郊区/优 郊区/良 郊区/不合格 流动/优 流动/良 流动/不合格
---- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
东莞 0 0 0 6060 900 100 0 0 0
广州 9000 67000 900 0 0 0 0 0 0
深圳 1000 6000 500 0 0 0 1800 8800 800(3 行受影响)
*/这种情况无需动态SQL
f0 f1 f2 f3
2006 A 10 10
2006 B 20 20
2006 C 10 10
2007 A 5 5
2007 B 10 10
......
转换成用Grid显示:
f0 | A | B | C ...
| f2 f3 | f2 f3 | f2 f3 ... 动态的表头
------------------------------
2006 10 10 20 20 10 10
2007 5 5 10 10 --drop table #temp
declare @t table
(
f0 varchar(10),
f1 varchar(10),
f2 varchar(10),
f3 varchar(10)
)insert @t
select '2006', 'A', '10', '10' union all
select '2006', 'B', '20', '20' union all
select '2006', 'C', '10', '10' union all
select '2007', 'A', '5', '5' union all
select '2007', 'B', '10', '10' select f0,f1,f2+' '+f3 as f2 into #temp from @tdeclare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ',[' + f1 + '] = max(case f1 when ''' + f1 + ''' then f2 else null end)'
from #temp group by f1 order by f1
set @sql = 'select f0 ' + @sql + ' from #temp group by f0'
EXEC(@sql)drop table #temp
f0 A B C
---------- --------------------- --------------------- ---------------------
2006 10 10 20 20 10 10
2007 5 5 10 10 NULL
declare @t table
(
f0 varchar(10),
f1 varchar(10),
f2 varchar(10),
f3 varchar(10)
)insert @t
select '2006', 'A', '10', '10' union all
select '2006', 'B', '20', '20' union all
select '2006', 'C', '10', '10' union all
select '2007', 'A', '5', '5' union all
select '2007', 'B', '10', '10' select f0,f1,cast(f2+f3 as int) as f2 into #temp from @tdeclare @sql varchar(4000)
set @sql = 'select f0 '
select @sql = @sql + ' , sum(case f1 when '''+f1+''' then f2 end) ['+f1+']'
from (select distinct f1 from #temp) as a
set @sql = @sql + ' from #temp group by f0'
exec(@sql) drop table #tempf0 A B C
---------- ----------- ----------- -----------
2006 1010 2020 1010
2007 55 1010 NULL
如果不要最后那个NULLdrop table #temp
declare @t table
(
f0 varchar(10),
f1 varchar(10),
f2 varchar(10),
f3 varchar(10)
)insert @t
select '2006', 'A', '10', '10' union all
select '2006', 'B', '20', '20' union all
select '2006', 'C', '10', '10' union all
select '2007', 'A', '5', '5' union all
select '2007', 'B', '10', '10' select f0,f1,f2+' '+f3 as f2 into #temp from @tdeclare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ',[' + f1 + '] = max(case f1 when ''' + f1 + ''' then f2 else ''' + '' + ''' end)'
from #temp group by f1 order by f1
set @sql = 'select f0 ' + @sql + ' from #temp group by f0'
EXEC(@sql)drop table #temp
f0 A B C
---------- --------------------- --------------------- ---------------------
2006 10 10 20 20 10 10
2007 5 5 10 10
不使用临时表的方法create table #t
(
f0 varchar(10),
f1 varchar(10),
f2 varchar(10),
f3 varchar(10)
)insert into #t(f0,f1,f2,f3) values('2006', 'A', '10', '10')
insert into #t(f0,f1,f2,f3) values('2006', 'B', '20', '20')
insert into #t(f0,f1,f2,f3) values('2006', 'C', '10', '10')
insert into #t(f0,f1,f2,f3) values('2007', 'A', '5', '5')
insert into #t(f0,f1,f2,f3) values('2007', 'B', '10', '10')declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ',[' + f1 + '] = max(case f1 when ''' + f1 + ''' then f2 + ''' + ' ' + '''+ f3 else ''' + '' + ''' end)'
from #t group by f1 order by f1
set @sql = 'select f0 ' + @sql + ' from #t group by f0'
EXEC(@sql)
drop table #t
在大家的帮助下,问题解决了!
我采用的是htl258的方法,其他的有时间我再慢慢研究!
感谢楼上所有关注和帮助我的前辈和朋友!谢谢!