100分求一个SQL（2000下）语句

Question

商品名称 型号 颜色 价格  
  手机     A   红   60  
  手机     B   蓝   55  
  电脑     D   白   100  
  手机     A   红   80  
  电脑     E   白   105  
  手机     C   红    40  
  电脑     D   黄    90  以上是我的一个视图里面的内容，我现在想通过一个SQL得到如下的表统计上述视图中有几种商品，每种商品的型号有几种，颜色有哪些颜色，该商品的所有型号所有颜色中价格最小值，最大值想要的效果：商品名称      型号                         颜色   价格区间
  手机     三种 (A 2台,B 1台,C 1台)       红，蓝    40-80
  电脑     两种(D 2台,E 1台)              白，黄    90-105===================================================================以下的代码已经实现了一部分功能，只是型号那里没有统计出每种型号有几台，请在以下代码的基础帮帮忙把每周型号有几台的SQL补齐，谢谢大家了。create table tb(商品名称 varchar(10),型号 varchar(10),颜色 varchar(10),价格 int)  
insert into tb values('手机', 'A', '红' ,60)  
insert into tb values('手机', 'B', '蓝' ,55)  
insert into tb values('电脑', 'D', '白' ,100)  
insert into tb values('手机', 'A', '红' ,80)  
insert into tb values('电脑', 'E', '白' ,105)  
insert into tb values('手机', 'C', '红' ,40)  
insert into tb values('电脑', 'D', '黄' ,90)
gocreate function dbo.f_str(@商品名称 varchar(10)) returns varchar(20)
as
begin
  declare @str varchar(1000)
  select @str = isnull(@str + ',' , '') + cast(颜色 as varchar) from (select distinct 商品名称 , 颜色 from tb) t where 商品名称  = @商品名称 
  return @str
end
go--调用函数
select m.商品名称, m.型号 , n.颜色 , m.价格区间 from
(select 商品名称 , 型号 = count(distinct 型号) , 价格区间 = ltrim(min(价格)) + '-' +  ltrim(max(价格)) from tb group by 商品名称) m,
(select 商品名称  , 颜色 = dbo.f_str(商品名称) from (select distinct 商品名称 , 颜色 from tb) t group by 商品名称 ) n
where m.商品名称 = n.商品名称
/*
商品名称       型号          颜色                   价格区间                      
---------- ----------- -------------------- ------------------------- 
电脑         2           白,黄                  90-105
手机         3           红,蓝                  40-80（所影响的行数为 2 行）*/drop function dbo.f_str
drop table tb

Answer 1

drop table tb
create table tb(商品名称 varchar(10),型号 varchar(10),颜色 varchar(10),价格 int)   
insert into tb values('手机', 'A', '红' ,60)   
insert into tb values('手机', 'B', '蓝' ,55)   
insert into tb values('电脑', 'D', '白' ,100)   
insert into tb values('手机', 'A', '红' ,80)   
insert into tb values('电脑', 'E', '白' ,105)   
insert into tb values('手机', 'C', '红' ,40)   
insert into tb values('电脑', 'D', '黄' ,90)
gocreate function dbo.f_str(@商品名称 varchar(10)) returns varchar(20)
as
begin
  declare @str varchar(1000)
  select @str = isnull(@str + ',' , '') + cast(颜色 as varchar) from (select distinct 商品名称 , 颜色 from tb) t where 商品名称 = @商品名称  
  return @str
end
gocreate function dbo.f_str2(@商品名称 varchar(10)) returns varchar(20)
as
begin
  declare @str varchar(1000)
  select @str = isnull(@str + ',' , '')+ cast(型号 as varchar)+' '+rtrim(count(1))+'台' from (select 商品名称 , 型号 from tb) t 
   where 商品名称 = @商品名称  group by 型号
  return @str
end
go
--调用函数
select m.商品名称, m.型号 , n.颜色 , m.价格区间 from
(select 商品名称 , 型号 = rtrim(count(distinct 型号))+'('+dbo.f_str2(商品名称)+')'
 , 价格区间 = ltrim(min(价格)) + '-' + ltrim(max(价格)) from tb group by 商品名称) m,
(select 商品名称 , 颜色 = dbo.f_str(商品名称) from (select distinct 商品名称 , 颜色 from tb) t group by 商品名称 ) n
where m.商品名称 = n.商品名称/*
商品名称       型号                                 颜色                   价格区间
---------- ---------------------------------- -------------------- -------------------------
电脑         2(D 2台,E 1台)                       白,黄                  90-105
手机         3(A 2台,B 1台,C 1台)                  红,蓝                  40-80

Answer 2

create function dbo.f_str2(@商品名称 varchar(10)) returns varchar(20)
as
begin
  declare @str varchar(1000)
  select @str = isnull(@str + ',' , '') + cnt 
  from (select 商品名称,型号+' '+ltrim(count(1))+'台' as cnt from tb group by 商品名称,型号) t 
  where 商品名称 = @商品名称  
  return @str
end
go--调用函数
select 
  商品名称,
  ltrim(count(distinct 型号))+'种'+'('+dbo.f_str2(商品名称)+')' as 型号,
  dbo.f_str(商品名称)  as 颜色,
  ltrim(min(价格))+'-'+ltrim(max(价格)) as 价格区间
from tb
group by 商品名称/**
商品名称       型号                                   颜色                   价格区间
---------- ------------------------------------ -------------------- -------------------------
电脑         2种(D 2台,E 1台)                        白,黄                  90-105
手机         3种(A 2台,B 1台,C 1台)                   红,蓝                  40-80(2 行受影响)
**/

Answer 3

如果2005
用xml path吧
简单方便

Answer 4

create table TryTableA(商品名称 varchar(10),型号 varchar(10),颜色 varchar(10),价格 int)  
insert into TryTableA values('手机', 'A', '红' ,60)  
insert into TryTableA values('手机', 'B', '蓝' ,55)  
insert into TryTableA values('电脑', 'D', '白' ,100)  
insert into TryTableA values('手机', 'A', '红' ,80)  
insert into TryTableA values('电脑', 'E', '白' ,105)  
insert into TryTableA values('手机', 'C', '红' ,40)  
insert into TryTableA values('电脑', 'D', '黄' ,90)
goalter function dbo.f_str(@商品名称 varchar(10),@标志 int) returns varchar(20)
as
begin
  
  declare @str varchar(1000)
  set @str=''
  if @标志=1
  begin
   select @str = isnull(@str + ',' , '') + cast(颜色 as varchar) from (select distinct 商品名称 , 颜色 from TryTableA) t where 商品名称 = @商品名称 
   set @str=stuff(@str,1,1,'')
  end
  if @标志=2
  begin
   select @str = @str  +','+ 型号 + ' '+ ltrim(c)+'台' from (select 型号,count(型号) as c from TryTableA where 商品名称 = @商品名称  group by 型号) t 
   set @str='('+stuff(@str,1,1,'')+')'  
  end  
return @str
end
go--调用函数
select m.商品名称, ltrim(m.型号) + dbo.f_str(m.商品名称,2)as 型号 , n.颜色 , m.价格区间 from
(select 商品名称 , 型号 = count(distinct 型号) , 价格区间 = ltrim(min(价格)) + '-' + ltrim(max(价格)) from TryTableA group by 商品名称) m,
(select 商品名称 , 颜色 = dbo.f_str(商品名称,1) from (select distinct 商品名称 , 颜色 from TryTableA) t group by 商品名称 ) n
where m.商品名称 = n.商品名称
改了一下你的函数~~传1是你原来的。。传2是专门为台数加的。。