有一个表TMP_A,有四千条记录create table TMP_A
(
GOODSID NUMBER(10), ---货品ID
PRICE NUMBER(20,10),
GOODSNAME VARCHAR2(40),
GOODSTYPE VARCHAR2(40),
PRODAREA VARCHAR2(40),
GOODSUNIT VARCHAR2(10)
)
insert into pub_goods (GOODSID, PRICE, GOODSNAME, GOODSTYPE, PRODAREA, GOODSUNIT)
values (1, 66.9000000000, '505神功元气袋', '成人型', '陕西咸阳505集团公司', '袋');insert into pub_goods (GOODSID, PRICE, GOODSNAME, GOODSTYPE, PRODAREA, GOODSUNIT)
values (2, 18.0000000000, '安尔眠糖浆', '10ml*10', '浙江锦江药业有限公司', '盒');insert into pub_goods (GOODSID, PRICE, GOODSNAME, GOODSTYPE, PRODAREA, GOODSUNIT)
values (3, 118.0000000000, '安宫牛黄丸', '3g', '北京同仁堂集团公司中药二厂', '粒');insert into pub_goods (GOODSID, PRICE, GOODSNAME, GOODSTYPE, PRODAREA, GOODSUNIT)
values (4, 37.0000000000, '(特)安宫牛黄丸', '3g', '江西樟树制药厂', '粒');insert into pub_goods (GOODSID, PRICE, GOODSNAME, GOODSTYPE, PRODAREA, GOODSUNIT)
values (5, 60.8000000000, '爱活胆通', '100s', '德国汉堡爱活大药厂', '盒');insert into pub_goods (GOODSID, PRICE, GOODSNAME, GOODSTYPE, PRODAREA, GOODSUNIT)
values (6, 222.8000000000, '安君宁丸', '6g*10袋', '湖南泰尔制药有限公司', '盒');insert into pub_goods (GOODSID, PRICE, GOODSNAME, GOODSTYPE, PRODAREA, GOODSUNIT)
values (7, 5.8000000000, '(特)复方醋酸曲安奈德溶液(安隆)', '10ml', '广东恒诚制药有限公司', '盒');insert into pub_goods (GOODSID, PRICE, GOODSNAME, GOODSTYPE, PRODAREA, GOODSUNIT)
values (8, 15.0000000000, '(特限)安神补脑液', '10ml*10支', '吉林敖东药业集团股份有限公司', '盒');insert into pub_goods (GOODSID, PRICE, GOODSNAME, GOODSTYPE, PRODAREA, GOODSUNIT)
values (9, 38.6000000000, '脑震宁颗粒', '10g*12包', '山西安特制药有限公司', '盒');insert into pub_goods (GOODSID, PRICE, GOODSNAME, GOODSTYPE, PRODAREA, GOODSUNIT)
values (10, 2.2000000000, '冰硼散', '3g', '上海中药制药一厂', '盒');
insert into pub_goods (GOODSID, PRICE, GOODSNAME, GOODSTYPE, PRODAREA, GOODSUNIT)
values (11, 1.0000000000, '冰硼散', '3g', '广州中药制药一厂', '盒');
我想实现的是,输入一个金额 ,随机从表里提取商品信息比如输入37,显示
货品ID 数量 零售价 小计
2 2 18 36
11 1 1 36 或
4 1 36 36 或
10 1 2.2 2.2
7 6 5.8 34.8
(
GOODSID NUMBER(10), ---货品ID
PRICE NUMBER(20,10),
GOODSNAME VARCHAR2(40),
GOODSTYPE VARCHAR2(40),
PRODAREA VARCHAR2(40),
GOODSUNIT VARCHAR2(10)
)
insert into pub_goods (GOODSID, PRICE, GOODSNAME, GOODSTYPE, PRODAREA, GOODSUNIT)
values (1, 66.9000000000, '505神功元气袋', '成人型', '陕西咸阳505集团公司', '袋');insert into pub_goods (GOODSID, PRICE, GOODSNAME, GOODSTYPE, PRODAREA, GOODSUNIT)
values (2, 18.0000000000, '安尔眠糖浆', '10ml*10', '浙江锦江药业有限公司', '盒');insert into pub_goods (GOODSID, PRICE, GOODSNAME, GOODSTYPE, PRODAREA, GOODSUNIT)
values (3, 118.0000000000, '安宫牛黄丸', '3g', '北京同仁堂集团公司中药二厂', '粒');insert into pub_goods (GOODSID, PRICE, GOODSNAME, GOODSTYPE, PRODAREA, GOODSUNIT)
values (4, 37.0000000000, '(特)安宫牛黄丸', '3g', '江西樟树制药厂', '粒');insert into pub_goods (GOODSID, PRICE, GOODSNAME, GOODSTYPE, PRODAREA, GOODSUNIT)
values (5, 60.8000000000, '爱活胆通', '100s', '德国汉堡爱活大药厂', '盒');insert into pub_goods (GOODSID, PRICE, GOODSNAME, GOODSTYPE, PRODAREA, GOODSUNIT)
values (6, 222.8000000000, '安君宁丸', '6g*10袋', '湖南泰尔制药有限公司', '盒');insert into pub_goods (GOODSID, PRICE, GOODSNAME, GOODSTYPE, PRODAREA, GOODSUNIT)
values (7, 5.8000000000, '(特)复方醋酸曲安奈德溶液(安隆)', '10ml', '广东恒诚制药有限公司', '盒');insert into pub_goods (GOODSID, PRICE, GOODSNAME, GOODSTYPE, PRODAREA, GOODSUNIT)
values (8, 15.0000000000, '(特限)安神补脑液', '10ml*10支', '吉林敖东药业集团股份有限公司', '盒');insert into pub_goods (GOODSID, PRICE, GOODSNAME, GOODSTYPE, PRODAREA, GOODSUNIT)
values (9, 38.6000000000, '脑震宁颗粒', '10g*12包', '山西安特制药有限公司', '盒');insert into pub_goods (GOODSID, PRICE, GOODSNAME, GOODSTYPE, PRODAREA, GOODSUNIT)
values (10, 2.2000000000, '冰硼散', '3g', '上海中药制药一厂', '盒');
insert into pub_goods (GOODSID, PRICE, GOODSNAME, GOODSTYPE, PRODAREA, GOODSUNIT)
values (11, 1.0000000000, '冰硼散', '3g', '广州中药制药一厂', '盒');
我想实现的是,输入一个金额 ,随机从表里提取商品信息比如输入37,显示
货品ID 数量 零售价 小计
2 2 18 36
11 1 1 36 或
4 1 36 36 或
10 1 2.2 2.2
7 6 5.8 34.8
*
from
pub_goods
where
零售价=??
order by
newid()
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
/*
--By Ken 2009-11-25
*/
if object_id('tb') is not null drop table tb
create table tb (code varchar(10),price dec(5,2))
insert into tb
select '001', 1.22 union all
select '002' , 34.5 union all
select '003' , 11.3 union all
select '004' , 25 union all
select '005' , 56.8 union all
select '022' ,18.00 union all
select '140' , 2.00 union all
select '011' , 8.70 union all
select '006' , 35.00 union all
select '142' , 20.90 union all
select '146' , 5.30 union all
select '099' , 17.70 union all
select '121' , 0.70 union all
select '038' , 5.20 union all
select '299' , 6.20 union all
select '183' , 9.00 union all
select '299' , 9.01 declare @count int,@row int,@i int,@id int --总行数、随机数、取出行数、匹配的行数
declare @nums dec(18,2),@rand dec(18,2) --总金额
declare @remain dec(18,2),@max_price dec(18,2) -- 剩余金额、最大单价
declare @mintotal dec(18,2)
declare @times int
declare @multiple int
--初始值
select @i = 1,@times = 0,
@nums = 2200.52,
@count = count(1) from tb
--生成id,随机取值用
select identity(int,1,1) as id,* into #temp from tb order by price
select @mintotal = sum(price) from #temp where id <= 12
if @nums < @mintotal
begin
--select '输入金额小于临界值'
drop table #temp
return
end
--创建临时表
create table #temp_tb (id int,code varchar(10),nums int,price dec(5,2),total as price*nums)select @remain = @nums
while @remain <> 0
begin
--随机取12行
while @i <= 12
begin
select @row = convert(int,(rand(checksum(newid()))*@count)) + 1
if EXISTS (select 1 from #temp_tb where id = @row)
begin
select @row = min(id) from #temp
where id not in (select id from #temp_tb)
end
insert into #temp_tb(id,code,nums,price)
select id,code,1,price from #temp where id = @row
select @i = @i + 1
end
--倍数
select @rand = sum(price) from #temp_tb
select @multiple = cast(@nums/@rand as int) - 5 --可自选
if @multiple > 0
begin
update #temp_tb set nums = @multiple
end
--剩余金额
select @remain = @nums - sum(nums*price) from #temp_tb
select @max_price = avg(price) from #temp_tb
--分配nums值
while @remain > @max_price
begin
update #temp_tb set nums = nums+1
where id = convert(int,(rand(checksum(newid()))*@count)) + 1
select @remain = @nums - sum(nums*price) from #temp_tb
end
--获取匹配行
select @id = max(id) from #temp_tb where @remain/price = cast(@remain/price as int)
and @remain > 0
--有
if @id is not null
begin
update #temp_tb set nums = nums + @remain/price
where id = @id
select @remain = 0
end
--无则重新循环
else
begin
delete from #temp_tb
select @i = 1
select @remain = @nums
select @times = @times + 1
if @times = 1000 --设置循环次数,匹配不了就退出循环
begin
break
end
end
end
select sum(total) as 总金额 from #temp_tb
select * from #temp_tb
drop table #temp,#temp_tb
--自己把字段改改吧
--测试数据
if object_id('tb') is not null drop table tb
create table tb (code varchar(10),price dec(5,2))
insert into tb
select '001', 1.22 union all
select '002' , 34.5 union all
select '003' , 11.3 union all
select '004' , 25 union all
select '005' , 56.8 union all
select '022' ,18.00 union all
select '140' , 2.00 union all
select '011' , 8.70 union all
select '006' , 35.00 union all
select '142' , 20.90 union all
select '146' , 5.30 union all
select '099' , 17.70 union all
select '121' , 0.70 union all
select '038' , 5.20 union all
select '299' , 6.20 union all
select '183' , 9.00 union all
select '299' , 9.01
--存储过程
alter proc proc_trans_make_price @nums dec(18,2)
as
declare @count int,@row int,@i int,@id int --总行数、随机数、取出行数、匹配的行数
declare @rand dec(18,2) --总金额
declare @remain dec(18,2),@max_price dec(18,2) -- 剩余金额、最大单价
declare @mintotal dec(18,2)
declare @times int
declare @multiple int
declare @row_count int
--初始值
select @i = 1,@times = 0,
--@nums = 2200.52,
@count = count(1) from tb
--生成id,随机取值用
select @row_count = cast(rand()*10 as int)+1
select identity(int,1,1) as id,* into #temp from tb order by price
select @mintotal = sum(price) from #temp where id <= @row_count
if @nums < @mintotal
begin
--select '输入金额小于临界值'
drop table #temp
return
end
--创建临时表
create table #temp_tb (id int,code varchar(10),nums int,price dec(5,2),total as price*nums)select @remain = @nums
while @remain <> 0
begin
--随机取12行
while @i <= @row_count
begin
select @row = convert(int,(rand(checksum(newid()))*@count)) + 1
if EXISTS (select 1 from #temp_tb where id = @row)
begin
select @row = min(id) from #temp
where id not in (select id from #temp_tb)
end
insert into #temp_tb(id,code,nums,price)
select id,code,1,price from #temp where id = @row
select @i = @i + 1
end
--倍数
select @rand = sum(price) from #temp_tb
select @multiple = cast(@nums/@rand as int) - 5 --可自选
if @multiple > 0
begin
update #temp_tb set nums = @multiple
end
--剩余金额
select @remain = @nums - sum(nums*price) from #temp_tb
select @max_price = avg(price) from #temp_tb
--分配nums值
while @remain > @max_price
begin
update #temp_tb set nums = nums+1
where id = convert(int,(rand(checksum(newid()))*@count)) + 1
select @remain = @nums - sum(nums*price) from #temp_tb
end
--获取匹配行
select @id = max(id) from #temp_tb where @remain/price = cast(@remain/price as int)
and @remain > 0
--有
if @id is not null
begin
update #temp_tb set nums = nums + @remain/price
where id = @id
select @remain = 0
end
--无则重新循环
else
begin
delete from #temp_tb
select @i = 1
select @remain = @nums
select @times = @times + 1
if @times = 1000 --设置循环次数,匹配不了就退出循环
begin
break
end
end
end
--select sum(total) as 总金额 from #temp_tb
select * from #temp_tb
drop table #temp,#temp_tb
--调用
proc_trans_make_price 40
-----------------
3 140 1 2.00 2.00
10 003 1 11.30 11.30
1 121 8 .70 5.60
7 011 1 8.70 8.70
6 299 2 6.20 12.40
-----------------
8 183 1 9.00 9.00
4 038 1 5.20 5.20
5 146 1 5.30 5.30
11 099 1 17.70 17.70
1 121 4 .70 2.80