jjJjjj

[kaper]

--款号表
款号 款式
AAA 休闲西装
BBB 休闲西装
CCC 正统西装
DDD 休闲西装

--销售表：
款号 颜色 数量
AAA 红 10
AAA 黄 15
AAA 蓝 12

BBB 红 13
BBB 白 15
BBB 绿 18
BBB 黑 22
BBB 红 13
BBB 白 15
BBB 绿 18
BBB 黑 22

CCC 红 15
CCC 白 18
CCC 绿 18
CCC 黑 30

DDD 黑 1


============================================
要查询出每个款式销售前2名的款号（按款式来排名的，比如休闲西装卖的最好的前2款，正统西装卖的最好的前2款 ）:
因为那销售表挺大的（80多W数据），速度最好别太慢，凑合能用就行。
-----------------------------------
名次 款式 款号 颜色销售状况
1 休闲西装 BBB 红(26) 白(30) 绿(36) 黑(44)
2 休闲西装 AAA 红(10) 黄(15) 蓝(12)
1 正统西装 DDD 黑（1）

[中国风]

是一个列转行，2000用函数\2005用XML

-- 1. 创建处理函数

CREATE FUNCTION dbo.f_str(@id int)

RETURNS varchar(8000)

AS

BEGIN

    DECLARE @r varchar(8000)

    SET @r = ''

    SELECT @r = @r + ',' + value

    FROM tb

    WHERE id=@id

    RETURN STUFF(@r, 1, 1, '')

END

GO

 

-- 调用函数

SELECt id, values=dbo.f_str(id) 

FROM tb 

GROUP BY id

 

-- 2. 新的解决方法 

-- 示例数据

DECLARE @t TABLE(id int, value varchar(10))

INSERT @t SELECT 1, 'aa'

UNION ALL SELECT 1, 'bb'

UNION ALL SELECT 2, 'aaa'

UNION ALL SELECT 2, 'bbb'

UNION ALL SELECT 2, 'ccc'

 

-- 查询处理

SELECT *

FROM(

    SELECT DISTINCT 

        id

    FROM @t

)A

OUTER APPLY(

    SELECT 

        [values]= STUFF(REPLACE(REPLACE(

            (

                SELECT value FROM @t N

                WHERE id = A.id

                FOR XML AUTO

            ), '<N value="', ','), '"/>', ''), 1, 1, ''))N

[中国风]

create table SalesOrder( Model nvarchar(20),color nvarchar(5) ,num int)



create function F_test(@Model nvarchar(20))
returns nvarchar(100)
as
begin
	declare @s nvarchar(100)
	select @s=isnull(@s+' ','')+颜色+rtrim(num) from  SalesOrder where Model=@Model 
	return @s
end


go
select
	Model,
	dbo.F_test(Model) as SalesColor
from 
	SalesOrder

[中国风]

select @s=isnull(@s+' ','')+颜色+rtrim(num) from SalesOrder where Model=@Model
这里要改改
要用sum

[wzy_love_sly]

全部实现很难！

[中国风]

create table SalesOrder( Model nvarchar(20),color nvarchar(5) ,num int)
   
insert SalesOrder select 'AAA' ,    '红',       10 
insert SalesOrder select 'AAA',     '黄',       15 
insert SalesOrder select 'AAA',    '蓝',       12 

insert SalesOrder select 'BBB',     '红',       13 
insert SalesOrder select 'BBB',     '白',       15 
insert SalesOrder select 'BBB',     '绿',         18 
insert SalesOrder select 'BBB',     '黑',         22 
insert SalesOrder select 'BBB',     '红',       13 
insert SalesOrder select 'BBB',     '白',       15 
insert SalesOrder select 'BBB',     '绿' ,        18 
insert SalesOrder select 'BBB',     '黑',         22

go

create function F_test(@Model nvarchar(20))
returns nvarchar(100)
as
begin
	declare @s nvarchar(100)
	select 
		@s=isnull(@s+' ','')+color+rtrim(num) 
	from 
		(select color, sum(num) as num from  SalesOrder where Model=@Model group by color)T
	return @s
end


go
select
	distinct 
	Model,
	dbo.F_test(Model) as SalesColor
from 
	SalesOrder

odel                SalesColor                                                                                           
-------------------- ---------------------------------------------------------------------------------------------------- 
AAA                  红10 黄15 蓝12
BBB                  白30 黑44 红26 绿36

[中国风]

--用函数生成名次
create table SalesOrder( Model nvarchar(20),color nvarchar(5) ,num int)
   
insert SalesOrder select 'AAA' ,    '红',       10 
insert SalesOrder select 'AAA',     '黄',       15 
insert SalesOrder select 'AAA',    '蓝',       12 

insert SalesOrder select 'BBB',     '红',       13 
insert SalesOrder select 'BBB',     '白',       15 
insert SalesOrder select 'BBB',     '绿',         18 
insert SalesOrder select 'BBB',     '黑',         22 
insert SalesOrder select 'BBB',     '红',       13 
insert SalesOrder select 'BBB',     '白',       15 
insert SalesOrder select 'BBB',     '绿' ,        18 
insert SalesOrder select 'BBB',     '黑',         22

go

create function F_test(@Model nvarchar(20))
returns nvarchar(100)
as
begin
	declare @s nvarchar(100)
	select 
		@s=isnull(@s+' ','')+color+rtrim(num) 
	from 
		(select color, sum(num) as num from  SalesOrder a where Model=@Model group by color
		order by (select top 1 color from SalesOrder where Model=a.Model )
	)T
	return @s
end


go

create function F_test2()
returns @T table (ID int identity,Model nvarchar(10))
as
begin
	insert @T(Model)
	select 
		Model
	from 
		SalesOrder
	group by Model
	order by sum(num) desc
	return
end
go
select
	 
	[名次]=a.ID,
	Model,
	dbo.F_test(Model) as SalesColor
from 
	F_test2() a

/*
名次          Model      SalesColor                                                                                           
----------- ---------- ---------------------------------------------------------------------------------------------------- 
1           BBB        白30 黑44 红26 绿36
2           AAA        红10 黄15 蓝12

（所影响的行数为 2 行）

*/

[kaper]

楼上的大大 我没讲清楚：
--款号表
款号 类别
AAA 休闲西装
BBB 休闲西装
CCC 正统西装
DDD 休闲西装

款号AAA这个商品的“类别”是休闲西装
现在要按类别来排序 并且只显示出每个类别销售前2名的款号（按类别来排名的，比如类别为休闲西装卖的最好的前2款，类别为正统西装卖的最好的前2款）

[中国风]

款号AAA这个商品的“类别”是休闲西装
现在要按类别来排序 并且只显示出每个类别销售前2名的款号（按类别来排名的，比如类别为休闲西装卖的最好的前2款，类别为正统西装卖的最好的前2款）

--看看是不是这样,ModelType款式类型有AAA\BBB\DDD取前二名，只取AAA\BBB
create table Brand(Model nvarchar(20),ModelType nvarchar(20)) 
insert Brand select 'AAA',       '休闲西装' 
insert Brand select 'BBB',       '休闲西装' 
insert Brand select 'CCC',       '正统西装' 
insert Brand select 'DDD',       '休闲西装' 

create table SalesOrder( Model nvarchar(20),color nvarchar(5) ,num int)
   
insert SalesOrder select 'AAA' ,    '红',       10 
insert SalesOrder select 'AAA',     '黄',       15 
insert SalesOrder select 'AAA',    '蓝',       12 

insert SalesOrder select 'BBB',     '红',       13 
insert SalesOrder select 'BBB',     '白',       15 
insert SalesOrder select 'BBB',     '绿',         18 
insert SalesOrder select 'BBB',     '黑',         22 
insert SalesOrder select 'BBB',     '红',       13 
insert SalesOrder select 'BBB',     '白',       15 
insert SalesOrder select 'BBB',     '绿' ,        18 
insert SalesOrder select 'BBB',     '黑',         22

insert SalesOrder select 'CCC' ,    '红',       10 
insert SalesOrder select 'CCC',     '黄',       15 
insert SalesOrder select 'CCC',    '蓝',       12 


insert SalesOrder select 'DDD' ,    '红',       10 
insert SalesOrder select 'DDD',     '黄',       15 


go

create function F_test(@Model nvarchar(20))
returns nvarchar(100)
as
begin
	declare @s nvarchar(100)
	select 
		@s=isnull(@s+' ','')+color+'('+rtrim(num) +')'--加个括号
	from 
		(select color, sum(num) as num from  SalesOrder a where Model=@Model group by color	)T
		order by num asc
	return @s
end


go

create function F_test2()
returns @T table (ID int identity,ModelType nvarchar(20),Model nvarchar(10),num int)
as
begin
	insert @T(ModelType,Model,num)
	select 
		b.ModelType,b.Model,sum(num) as num
	from 
		SalesOrder a
	join
		Brand b on a.Model=b.Model
	group by b.ModelType,b.Model
	order by num desc
	return
end
go
--1、前2款数量相同时,记为1条记录.
select
	 [名次]=(select count(distinct num) from F_test2() where ModelType=b.ModelType and num>a.num)+1,--这样显示为num相同时计算为一个名次
	b.ModelType,
	a.Model,
	dbo.F_test(a.Model) as SalesColor
from 
	F_test2() a
join
	Brand b on a.Model=b.Model
where
	(select count(distinct num) from F_test2() where ModelType=b.ModelType and num>a.num)<2--取排前二名
order by [名次] asc 


--如果前2款数量相同时也只取两条记录时:
select
	 [名次]=(select count(distinct num) from F_test2() where ModelType=b.ModelType and num>a.num)+1,--这样显示为num相同时计算为一个名次
	b.ModelType,
	a.Model,
	dbo.F_test(a.Model) as SalesColor
from 
	F_test2() a
join
	Brand b on a.Model=b.Model
where
	a.ID in(select top 2 ID from F_test2() where ModelType=b.ModelType order  by ID asc)--取排前二名
order by a.ModelType,[名次] asc 


/*
名次          ModelType            Model      SalesColor                                                                                           
----------- -------------------- ---------- ---------------------------------------------------------------------------------------------------- 
1           休闲西装                 BBB        红(26) 白(30) 绿(36) 黑(44)
2           休闲西装                 AAA        红(10) 蓝(12) 黄(15)
1           正统西装                 CCC        红(10) 蓝(12) 黄(15)

（所影响的行数为 3 行）


*/
--drop function F_test,F_test2
--drop table SalesOrder,Brand

[中国风]

create table Brand(Model nvarchar(20),ModelName nvarchar(20)) 
insert Brand select 'AAA',       '休闲西装' 
insert Brand select 'BBB',       '休闲西装' 
insert Brand select 'CCC',       '正统西装' 
insert Brand select 'DDD',       '休闲西装' 

create table SalesOrder( Model nvarchar(20),color nvarchar(5) ,num int)
   
insert SalesOrder select 'AAA' ,    '红',       10 
insert SalesOrder select 'AAA',     '黄',       15 
insert SalesOrder select 'AAA',    '蓝',       12 

insert SalesOrder select 'BBB',     '红',       13 
insert SalesOrder select 'BBB',     '白',       15 
insert SalesOrder select 'BBB',     '绿',         18 
insert SalesOrder select 'BBB',     '黑',         22 
insert SalesOrder select 'BBB',     '红',       13 
insert SalesOrder select 'BBB',     '白',       15 
insert SalesOrder select 'BBB',     '绿' ,        18 
insert SalesOrder select 'BBB',     '黑',         22

go

create function F_test(@Model nvarchar(20))
returns nvarchar(100)
as
begin
	declare @s nvarchar(100)
	select 
		@s=isnull(@s+' ','')+color+rtrim(num) 
	from 
		(select color, sum(num) as num from  SalesOrder a where Model=@Model group by color	)T
	return @s
end


go

create function F_test2()
returns @T table (ID int identity,Model nvarchar(10))
as
begin
	insert @T(Model)
	select 
		Model
	from 
		SalesOrder
	group by Model
	order by sum(num) desc
	return
end
go
select
	 
	[名次]=a.ID,
	b.ModelName,
	a.Model,
	dbo.F_test(a.Model) as SalesColor
from 
	F_test2() a
join
	Brand b on a.Model=b.Model

/*
名次          ModelName            Model      SalesColor                                                                                           
----------- -------------------- ---------- ---------------------------------------------------------------------------------------------------- 
2           休闲西装                 AAA        红10 黄15 蓝12
1           休闲西装                 BBB        白30 黑44 红26 绿36

（所影响的行数为 2 行）


*/
--drop function F_test,F_test2
--drop table SalesOrder

[中国风]

select
	 
	[名次]=a.ID,
	b.ModelName,
	a.Model,
	dbo.F_test(a.Model) as SalesColor
from 
	F_test2() a
join
	Brand b on a.Model=b.Model
order by [名次] asc --加个排序
----------- -------------------- ---------- ---------------------------------------------------------------------------------------------------- 
1           休闲西装                 BBB        白30 黑44 红26 绿36
2           休闲西装                 AAA        红10 黄15 蓝12

（所影响的行数为 2 行）

[kaper]

THX ¬¬