22,210
社区成员
发帖
与我相关
我的任务
分享高分请教一个复杂点的行转列的问题
表结构
CREATE TABLE ProductRelationAttr
(
id int identity primary key
productid int
attrid int,
attrvalueid int,
sku varchar(20)
)
测试数据
INSERT INTO ProductRelationAttr
SELECT 10001, 1, 4 , 'EP001X' UNION
SELECT 10001, 1, 5 , 'EP002X' UNION
SELECT 10001, 2, 2 , 'EP001X' UNION
SELECT 10001, 2, 3 , 'EP002X' UNION
SELECT 10002, 1, 4 , 'EP003X'
动态转换后表结构
id, producid, attrid1, attrid2 , attrvalueid1, attrvalueid2, sku
1 10001 1 4 2 2 'EP001X'
2 10001 1 5 2 3 'EP002X'
3 10002 1 NULL 4 NULL 'EP003X'
CREATE TABLE ProductRelationAttr
(
id int identity primary key
productid int
attrid int,
attrvalueid int,
sku varchar(20)
)
测试数据
INSERT INTO ProductRelationAttr
SELECT 10001, 1, 4 , 'EP001X' UNION
SELECT 10001, 1, 5 , 'EP002X' UNION
SELECT 10001, 2, 2 , 'EP001X' UNION
SELECT 10001, 2, 3 , 'EP002X' UNION
SELECT 10002, 1, 4 , 'EP003X'
动态转换后表结构
id, producid, attrid1, attrid2 , attrvalueid1, attrvalueid2, sku
1 10001 1 4 2 2 'EP001X'
2 10001 1 5 2 3 'EP002X'
3 10002 1 NULL 4 NULL 'EP003X'
...全文
请发表友善的回复…
发表回复
ILOVE_ASPNET 2014-04-10
- 打赏
- 举报
谢谢，非常对，但是我这个有点复杂，因为是动态的，不只有Attr1 attr2 还有3和4也有， 这种情况下 就比较纠结了，能否有办法？
ILOVE_ASPNET 2014-04-10
- 打赏
- 举报
会有
MootShao 2014-04-10
- 打赏
- 举报
CREATE TABLE #ProductRelationAttr
(
id int identity primary key ,
productid int ,
attrid int,
attrvalueid int,
sku varchar(20)
)
--测试数据
INSERT INTO #ProductRelationAttr
SELECT 10001, 1, 4 , 'EP001X' UNION
SELECT 10001, 1, 5 , 'EP002X' UNION
SELECT 10001, 2, 2 , 'EP001X' UNION
SELECT 10001, 2, 3 , 'EP002X' UNION
SELECT 10002, 1, 4 , 'EP003X'
select row_number() over(order by p1.id) id ,p1.productid ,p1.attrid attrid1
,p2.attrid attrid2 ,p1.attrvalueid attrvalueid1 ,p2.attrvalueid attrvalueid2 ,p1.sku
from (
select sku ,productid ,min(attrid) attrid
from #ProductRelationAttr group by sku ,productid
)f join #ProductRelationAttr p1 on f.sku = p1.sku and f.productid = p1.productid and f.attrid = p1.attrid
left join #ProductRelationAttr p2 on p1.sku = p2.sku and p1.attrid <> p2.attrid and p1.productid = p2.productid
drop table #ProductRelationAttr
power_virus 2014-04-10
- 打赏
- 举报
会不会有attrid3, attrid4......这种情况？，也就是说表的字段会动态增加的？
ILOVE_ASPNET 2014-04-10
- 打赏
- 举报
MootShao 2014-04-10
- 打赏
- 举报
谢谢，非常对，但是我这个有点复杂，因为是动态的，不只有Attr1 attr2 还有3和4也有， 这种情况下 就比较纠结了，能否有办法？[/quote]
CREATE TABLE #ProductRelationAttr
(
id int identity primary key ,
productid int ,
attrid int,
attrvalueid int,
sku varchar(20)
)
--测试数据
INSERT INTO #ProductRelationAttr
SELECT 10001, 1, 4 , 'EP001X' UNION
SELECT 10001, 1, 5 , 'EP002X' UNION
SELECT 10001, 2, 2 , 'EP001X' UNION
SELECT 10001, 2, 3 , 'EP002X' UNION
SELECT 10002, 1, 4 , 'EP003X' UNION
SELECT 10001, 3, 2 , 'EP001X'
select * ,row_number() over(partition by sku ,productid order by attrid)list
into #table
from #ProductRelationAttr
declare @i int = 0,@sql varchar(max)='',@name varchar(max)='',@n int = 1 ,@str varchar(max)=''
select @i = max(list) from #table
while (@n < @i)
begin
set @n = @n + 1
set @sql = @sql + 'left join #table t'+ cast(@n as varchar(4))
+ ' on t1.sku = t' + cast(@n as varchar(4)) +'.sku and t1.productid = t' + cast(@n as varchar(4))
+'.productid and t1.list + ' + cast(@n-1 as varchar(4)) +' = t' + cast(@n as varchar(4))+'.list '
set @name = @name + ',t' + cast(@n as varchar(4)) +'.attrid attrid' + cast(@n as varchar(4))
+',t' + cast(@n as varchar(4)) +'.attrvalueid attrvalueid' + cast(@n as varchar(4))
end
set @str = '
select row_number() over(order by t1.id) id ,t1.productid ,t1.attrid attrid1 ,t1.attrvalueid attrvalueid1 '+ @name +'
,t1.sku
from #table t1 '+ @sql +'
where t1.list = 1
'
exec (@str)
drop table #ProductRelationAttr ,#table
/**结果如下
id productid attrid1 attrvalueid1 attrid2 attrvalueid2 attrid3 attrvalueid3 sku
1 10001 1 4 2 2 3 2 EP001X
2 10001 1 5 2 3 NULL NULL EP002X
3 10002 1 4 NULL NULL NULL NULL EP003X
**/
fling_boy 2014-04-10
- 打赏
- 举报
查一下，动态交叉数据报表，就可以找到答案了。
这是一个例子：
一种比较复杂的交叉数据报表的作法及其复杂度的简析
有时候需要旋转查询结果以便在水平方向显示列，而在垂直方向显示行。就是所谓的创建 PivotTable、创建交叉数据报表或旋转数据。交叉数据报表在做具体应用的查询分析的时候是非常有用的。有些时候交叉数据报表又叫透视表。
本文向大家介绍一种比较复杂的透视表的作法，并对其作时间复杂度分析。 为演示方便，在本文中用到的表全部是临时表，使用的是SQL Server 2000。临时表以“#”开头,用户产生的临时表并不放在本用户创建的数据库实例当中，而是放在系统自己的tempdb数据库中，在用户与数据库断开连接后，数据库服务器会自动删除该临时表。
一、 题目来源如下：
对某生产部门的生产情况进行查询，此查询按用户、年份、季度进行。查询某用户、在某年每一季度的平均生产量和该季度的累计生产量。为了简明起见，在表中只有A用户，查询A用户从2002年到2003年的四个季度的“平均生产量”和“累计生产量”。其数据表如下(#temp1)：
NULL 年份 季度 平均生产量 累计生产量
A 2002 1 200 600
A 2002 2 140 420
A 2002 3 165 495
A 2002 4 190 570
A 2003 1 100 300
A 2003 2 130 390
A 2003 3 170 510
A 2003 4 180 540 （#temp1）
按照交叉报表的要求季度列应当按行显示，对每一季度显示其平均生产量和累计生产量。
二、 生成交叉报表过程：
2.1、 均生产量和累计生产量分开表示：
“select 用户,年份,季度,平均生产量,'0' as 累计生产量
into #temp2
from #temp1
union
select 用户,年份,季度,累计生产量,'1'
from #temp1“
以上语句的作用是设置标记来区分“平均生产量”和“累计生产量”。其中“0”表平均生产量，“1”表示累计生产量。得到数据表如下(#temp2):
用户 年份 季度 平均生产量 累计生产量
A 2002 1 200 0
A 2002 1 600 1
A 2002 2 140 0
A 2002 2 420 1
A 2002 3 165 0
A 2002 3 495 1
A 2002 4 190 0
A 2002 4 570 1
A 2003 1 100 0
A 2003 1 300 1
A 2003 2 130 0
A 2003 2 390 1
A 2003 3 170 0
A 2003 3 510 1
A 2003 4 180 0
A 2003 4 540 1 (#temp2)
“select 用户,年份,季度+累计生产量 as 季度flag,平均生产量=case 累计生产量 when 0 then 平均生产量 else 0 end,
累计生产量=case 累计生产量 when 1 then 平均生产量 else 0 end
into #temp3
from #temp2“
以上语句的作用是设置标记来区分某季度的“平均生产量”和“累计生产量”。其中“10”表示 “季度1” 的平均生产量，“11”表示 “季度1”的累计生产量。得到数据表如下(#temp3)：
用户 年份 季度flag 平均生产量 累计生产量
A 2002 10 200 0
A 2002 11 0 600
A 2002 20 140 0
A 2002 21 0 420
A 2002 30 165 0
A 2002 31 0 495
A 2002 40 190 0
A 2002 41 0 570
A 2003 10 100 0
A 2003 11 0 300
A 2003 20 130 0
A 2003 21 0 390
A 2003 30 170 0
A 2003 31 0 510
A 2003 40 180 0
A 2003 41 0 540 (#temp3)
2.2、生成主SQL语句，执行主SQL语句形成最后的交叉报表，
declare @sql varchar(8000)
select @sql='select NULL as 用户, NULL as 年份'
select @sql=@sql+','+'''第'+jd+'季度'''+','+'''第'+jd+'季度'''
from(select distinct jd from #temp_jd)a
select @sql=@sql+' union '
select @sql=@sql+'select NULL ,NULL '
select @sql=@sql+',''平均生产量'''+',''累计生产量'''
from(select distinct jd from #temp_jd)a
select @sql=@sql+' union '
select @sql=@sql+'select 用户,年份'
select @sql=@sql+', ['+jd+'0]'+'=convert(varchar,sum(case 季度flag when '+jd+'0
then 平均生产量 else 0 end))'+', ['+jd+'1]'+'=convert(varchar,sum(case 季flag
when '+jd+'1 then 累计生产量 else 0 end))'
from(select jd from #temp_jd)a
select @sql=@sql+ ' from #temp3 group by 用户,年份'
exec(@sql)
生成交叉报表如下：
NULL NULL 第1季度 第1季度 第2季度 第2季度 第3季度 第3季度 第4季度 第4季度
NULL NULL 平均生产量 累计生产量 平均生产量 累计生产量 平均生产量 累计生产量 平均生产量 累计生产量
A 2002 200 600 140 420 165 495 190 570
A 2003 100 300 130 390 170 510 180 540
三、说明:
1、在组合主SQL语句的过程中用到了临时表#temp_jd,此表的作用是，辅助生成主SQL语句，并动态向主SQL语句提供季度分量。
2、在给主SQL语句赋值的过程中使用select而非set。这是因为主SQL语句是动态生成的，因为在将季度列显示为行的过程中使用的如下语句：
select @sql=@sql+',['+jd+'0]'+'=convert(varchar,sum(case 季度flag when '+jd+'0
then 平均生产量 else 0 end))'+', ['+jd+'1]'+'=convert(varchar,sum(case 季flag
when '+jd+'1 then 累计生产量 else 0 end))'
from(select jd from #temp_jd)a
该语句可理解为 select @sql=@sql+"***" from (select jd from #temp_jd)a
其中(select jd from #temp_jd)a可理解为将select jd from #temp_jd 返回的记录集起一个别名a
由此我们不难理解，此语句起的是循环的作用。可以按照#temp_jd的季度分量动态生成交叉报表的列。假如#temp_jd中有1、2季度则只生成1、2季度的“平均生产量”和“累计生产量”,以此类推。而#temp_jd的形成是与我们此文中的主数据表#temp1有关的，即#temp_jd中的季度分量的个数是对#temp1中季度分量集合的计数。
四、其时间复杂度分析：
SQL语句中的查询语句是:
select [ALL|DISTINCT]<目标列表达式>[,<目标列表达式>,...]
from <表名|视图名>[,<表名|视图名>,...]
[where <条件表达式1>[,<条件表达式2>,...]]
[group by <列名> [having <条件表达式>]
[order by <列名>]。 其执行原理如下：
按照Where 语句中的 条件表达式 从数据源中找到符合条件的元组（记录），返回的是记录的集合，在返回记录的同时对select 语句进行操作,如果有group by 语句，则按group by 语句中的 条件表达式对聚合函数进行分组操作。 所以其执行过程可理解如下：
While( where条件表达式满足)
{
语句1：从 from 指定的数据源中返回记录；
语句2：对 语句1中返回的记录进行select 操作；
语句3：if(存在 group by 条件表达式 )
{
语句1：按group by 条件表达式执行聚合函数；
}
}
1、对上文中生成#temp2,#temp3 的过程其时间复杂度为0(n)。
2、形成主SQL语句的过程，在动态生成季度列的过程中，其时间复杂度为0(n)。
3、执行主SQL语句，其时间复杂度为0（n）。
