求SQL语句或解决问题的方法
T1：字段 xm(姓名),zb(所在销售组),sscp(销售部品),sl(销售数量),je(单价金额)
表内数据有 ：李明，苏州，产品1，50，80
李明，苏州，产品2，60，40
李明，苏州，产品3，70，100
王歆与，广东，产品1，90，80
王歆与，广东，产品2，100，40
王歆与，广东，产品3，120，100
张可，广东，产品1，14，80
张可，广东，产品2，67，40
张可，广东，产品3，55，100
李一天，苏州，产品1，45，80
李一天，苏州，产品2，34，40
李一天，苏州，产品3，22，100
希望查询结果如下
姓名 产品1 公司排名 小组排名 产品2 公司排名 小组排名 产品3 公司排名 小组排名 总销售金额 公司排名 小组排名
李明 50 1 60 3 1 70 2 1 13400 2 1
王歆与 90 1 1 100 1 1 120 1 1 23200 1 1
张可 14 4 2 67 2 2 55 3 2 9300 3 2
李一天 45 3 2 34 4 2 22 4 2 7160 4 2
SQL?
[解决办法]
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-11-06 10:58:35
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[T1]
if object_id('[T1]') is not null drop table [T1]
go
create table [T1]([xm] varchar(6),[zb] varchar(4),[sscp] varchar(5),[sl] int,[je] int)
insert [T1]
select '李明','苏州','产品1',50,80 union all
select '李明','苏州','产品2',60,40 union all
select '李明','苏州','产品3',70,100 union all
select '王歆与','广东','产品1',90,80 union all
select '王歆与','广东','产品2',100,40 union all
select '王歆与','广东','产品3',120,100 union all
select '张可','广东','产品1',14,80 union all
select '张可','广东','产品2',67,40 union all
select '张可','广东','产品3',55,100 union all
select '李一天','苏州','产品1',45,80 union all
select '李一天','苏州','产品2',34,40 union all
select '李一天','苏州','产品3',22,100
--------------开始查询--------------------------
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename(sscp)+'=max(case when [sscp]='+quotename(sscp,'''')+' then [sl] else 0 end)'
+','+quotename('rankCompany')+'=max(case when [sscp]='+quotename(sscp,'''')+' then [rankCompany] else 0 end)'
+','+quotename('rankGroup')+'=max(case when [sscp]='+quotename(sscp,'''')+' then [rankGroup] else 0 end)'
from (
select * ,rank()OVER(PARTITION BY sscp ORDER BY sl desc ) [rankCompany],RANK()OVER(PARTITION BY zb ORDER BY sl)[rankGroup],(SELECT SUM(sl*je) FROM t1 a WHERE a.xm=t1.xm)[total],RANK()OVER(ORDER BY (SELECT SUM(sl*je) FROM t1 a WHERE a.xm=t1.xm))totalrank
from [T1]
) a group by sscp
exec('select [xm],total,totalrank '+@s+' from (select * ,rank()OVER(PARTITION BY sscp ORDER BY sl desc ) [rankCompany],RANK()OVER(PARTITION BY zb ORDER BY sl)[rankGroup],(SELECT SUM(sl*je) FROM t1 a WHERE a.xm=t1.xm)[total],RANK()OVER(ORDER BY (SELECT SUM(sl*je) FROM t1 a WHERE a.xm=t1.xm))totalrank
from [T1])a group by [xm],total,totalrank')
----------------结果----------------------------
/*
xm total totalrank 产品1 rankCompany rankGroup 产品2 rankCompany rankGroup 产品3 rankCompany rankGroup
------ ----------- -------------------- ----------- -------------------- -------------------- ----------- -------------------- -------------------- ----------- -------------------- --------------------
李明 13400 7 50 2 4 60 3 5 70 2 6
李一天 7160 1 45 3 3 34 4 2 22 4 1
王歆与 23200 10 90 1 4 100 1 5 120 1 6
张可 9300 4 14 4 1 67 2 3 55 3 2
*/
create table T1
(xm varchar(10),zb varchar(10),sscp varchar(10),sl int,je int)
insert into T1
select '李明', '苏州', '产品1', 50, 80 union all
select '李明', '苏州', '产品2', 60, 40 union all
select '李明', '苏州', '产品3', 70, 100 union all
select '王歆与', '广东', '产品1', 90, 80 union all
select '王歆与', '广东', '产品2', 100, 40 union all
select '王歆与', '广东', '产品3', 120, 100 union all
select '张可', '广东', '产品1', 14, 80 union all
select '张可', '广东', '产品2', 67, 40 union all
select '张可', '广东', '产品3', 55, 100 union all
select '李一天', '苏州', '产品1', 45, 80 union all
select '李一天', '苏州', '产品2', 34, 40 union all
select '李一天', '苏州', '产品3', 22, 100
declare @tsql varchar(6000)
select @tsql=isnull(@tsql+',','')
+'sum(case a.sscp when '''+sscp+''' then a.sl else 0 end) '''+sscp+''', '
+'(select count(1) from
(select 1 ''d'' from T1 b
where b.sscp='''+sscp+''' group by b.xm
having sum(b.sl)>=(select sum(e.sl) from T1 e where e.xm=a.xm and e.sscp='''+sscp+''')) c) ''公司排名'', '
+'(select count(1) from
(select 1 ''d'' from T1 b
where b.sscp='''+sscp+''' and b.zb=a.zb group by b.xm
having sum(b.sl)>=(select sum(e.sl) from T1 e where e.xm=a.xm and e.sscp='''+sscp+''')) c) ''小组排名'' '
from (select distinct sscp from T1) t
select @tsql='select a.xm ''姓名'','+@tsql+','
+' sum(a.sl*a.je) ''总销售金额'',
(select count(1) from
(select 1 ''d'' from T1 b group by b.xm having sum(b.sl*b.je)>=(select sum(e.sl*e.je) from T1 e where e.xm=a.xm)) c) ''公司排名'',
(select count(1) from
(select 1 ''d'' from T1 b where b.zb=a.zb group by b.xm having sum(b.sl*b.je)>=(select sum(e.sl*e.je) from T1 e where e.xm=a.xm)) c) ''小组排名''
from T1 a
group by a.xm,a.zb '
exec(@tsql)
/*
姓名 产品1 公司排名 小组排名 产品2 公司排名 小组排名 产品3 公司排名 小组排名 总销售金额 公司排名 小组排名
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
王歆与 90 1 1 100 1 1 120 1 1 23200 1 1
张可 14 4 2 67 2 2 55 3 2 9300 3 2
李明 50 2 1 60 3 1 70 2 1 13400 2 1
李一天 45 3 2 34 4 2 22 4 2 7160 4 2
(4 row(s) affected)
*/
