两个个表,分别是
票据表 票据ID(varchar)(主键),商品ID(varchar),价格(varchar),客户ID(varchar)
客户表 客户ID(varchar)(主键),客户名称(varchar)
注意
1、票据表的价格是varchar型的,可能是一个价格数字字符,如'7.5',也可能是多个用逗号分开的数字字符,如'12,14.5',还可能是汉字字符“未定”
2、在票据表中同一个商品ID有多条记录,价格可能也不相同
现在需要写一个SQL,要求查询出如下信息:
商品ID;
这件商品的最高成交价格;
最高价格的票据的客户名称,如果最高价格有多条记录,可以随便取一条;
这件商品的最低成交价格;
最低价格的票据的客户名称,如果最高价格有多条记录,可以随便取一条;
这件商品的平均价格;
注意,逗号分隔的多个价格要分别判断高价低价,也要计算平均价格。好了,问题就是这样,我面临的问题是不会在这样的查询中处理这个用逗号分开的数字。表的记录在数万条左右,也不是很大,可以考虑使用临时表。求求各位大大帮我解答下呀,小弟在此先谢谢了~~如果要说为什么会有这么蛋疼的问题呢,为什么不在程序里解决或者当初设计表时怎么没注意这些,我也解释不了,那就不解释了。谢谢~~
票据表 票据ID(varchar)(主键),商品ID(varchar),价格(varchar),客户ID(varchar)
客户表 客户ID(varchar)(主键),客户名称(varchar)
注意
1、票据表的价格是varchar型的,可能是一个价格数字字符,如'7.5',也可能是多个用逗号分开的数字字符,如'12,14.5',还可能是汉字字符“未定”
2、在票据表中同一个商品ID有多条记录,价格可能也不相同
现在需要写一个SQL,要求查询出如下信息:
商品ID;
这件商品的最高成交价格;
最高价格的票据的客户名称,如果最高价格有多条记录,可以随便取一条;
这件商品的最低成交价格;
最低价格的票据的客户名称,如果最高价格有多条记录,可以随便取一条;
这件商品的平均价格;
注意,逗号分隔的多个价格要分别判断高价低价,也要计算平均价格。好了,问题就是这样,我面临的问题是不会在这样的查询中处理这个用逗号分开的数字。表的记录在数万条左右,也不是很大,可以考虑使用临时表。求求各位大大帮我解答下呀,小弟在此先谢谢了~~如果要说为什么会有这么蛋疼的问题呢,为什么不在程序里解决或者当初设计表时怎么没注意这些,我也解释不了,那就不解释了。谢谢~~
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (id int,price varchar(100),customer int,cinvcode int)
insert into #tb
select 1,'1.5',4,2 union all
select 2,'3.5,6',3,2 union all
select 3,'2.5,3',6,1 union all
select 4,'5',1,5 select id,
customer,
cinvcode,
price=substring(price+',',number,charindex(',',price+',',number)-number)
from #tb ,master..spt_values s
where type='p' and number>0 and substring(','+price,number,1)=','
--@pristr为字符串价格,@lx为返回最大或最小值,0为返回最小,1为返回最大
create function dbo.getprice(@pristr nvarchar(100),@lx int)
returns numeric(20,2)
as
begin
declare @maxprice numeric(20,2),@minprice numeric(20,2),@price numeric(20,2),@str nvarchar(100)
declare @i int
if (@pristr='未定' or ltrim(@pristr)='')
return null
else if (charindex(',',@pristr)=0)
return convert(numeric(20,2),@pristr)
else begin
set @str=@pristr
set @i=charindex(',',@str)
while @i>0
begin
set @price=convert(numeric(20,2),left(@str,@i-1))
if (@minprice is null) set @minprice=@price
else if (@minprice>@price) set @minprice=@price
if (@maxprice is null) set @maxprice=@price
else if (@maxprice<@price) set @maxprice=@price
set @str=stuff(@str,1,@i,'')
set @i=charindex(',',@str)
end
set @price=convert(numeric(20,2),@str)
if (@minprice>@price) set @minprice=@price
if (@maxprice<@price) set @maxprice=@price
if (@lx=0) set @price=@minprice else set @price=@maxprice
return @price
end
return 0endselect dbo.getprice('未定',0),dbo.getprice('5.0',0),dbo.getprice('5.0',1),dbo.getprice('1.0,2.5,3.3,4.4,6,5',0),dbo.getprice('1.0,2.5,3.3,4.4,6,5',1) NULL 5.00 5.00 1.00 6.00
(1 行受影响)根据此函数,再用select max(dbo.getprice(价格,1),min(dbo.getprice(价格,0)
就可求出最大价格与最小价格