N-P问题,这个不是SQL 的强项,应该放在前端执行。
当然在SQL也能实现,如果数据量不大的话。
建议根据数据特点:可以采用枚举法(数据量特别下的情况)和贪心法(有一定数据量,枚举法性能太差)
当然在SQL也能实现,如果数据量不大的话。
建议根据数据特点:可以采用枚举法(数据量特别下的情况)和贪心法(有一定数据量,枚举法性能太差)
解决方案 »
- 视图,多表关联查询替换问题
- SQL2000和SQL2005共存时,SQL2005的端口设置和ASP文件的数据库连接问题
- 怎样写一个函数后对于任何人都不可见呢?
- SQL 2005 快照代理 读取列长度失败 如何解决
- 请看一下SQL2008停止响应是什么原因
- (急求)SQLDMO在C#中的差异备份还原问题
- 讨论个问题,怎么按照最佳的符合预期进行搜索?此题有点难高手来
- 执行sp_addlinkedsrvlogin 为什么显示(所影响的行数为 0 行)(所影响的行数为 0 行)(所影响的行数为 0 行)(所影响的行数为 1 行)
- sqlserver2005分区表问题
- vfp当中,如何用程序调用菜单?
- 高手帮忙看一下这两句Sql语句哪个效率更高?
- sql 统计
select id=identity(int),cap into #car
from(select cap=cast(10 as dec(18,1)) union all select 8
union all select 5 union all select 3 union all select 2) t
order by cap desc--货物表
select id=identity(int),w into #goods
from(select w=cast(8 as dec(18,1)) union all select 2
union all select 1.5 union all select 2.5) t
order by w desc--所有可能组合表
select cid=a.id,cap,gid=b.id,w into #cop
from #car a join #goods b on a.cap>=b.w
order by cid--所有有效组合表:组合路径、所需车辆数、车辆id之和、车辆总载重。(总装载率>=90%)
with szx as
(
select cid,cap,gid,w,path=cast(rtrim(gid)+'-'+rtrim(cid) as varchar(8000)) from #cop where gid=1
union all
select a.cid,a.cap,a.gid,a.w,path=b.path+','+rtrim(a.gid)+'-'+rtrim(a.cid)
from #cop a, szx b
where a.gid=b.gid+1 and charindex(','+rtrim(a.gid)+'-',','+b.path)=0
--and a.cap>=a.w+(select sum(m.w) from #goods m,#car n where n.id=a.cid and charindex(','+rtrim(m.id)+'-'+rtrim(n.id)+',',','+b.path+',')>0)
)
select a.path,cc=count(distinct b.id),ct=sum(distinct b.id),cw=sum(b.cap)
into #path
from szx a join #car b
on charindex('-'+rtrim(b.id)+',',a.path+',')>0
where len(path)=(select count(1) from #goods)*4-1
group by a.path
having (select sum(w) from #goods)/sum(b.cap) between 0.90 and 1 --依次按:所需车辆最少、尽量使用大车、装载率等条件选出最优组合:
select a.path,b.cap,c.w
into #result
from (select path,rk=dense_rank() over(order by cc,ct,cw) from #path) a,#car b,#goods c
where a.rk=1
and charindex(','+rtrim(c.id)+'-'+rtrim(b.id)+',',','+a.path+',')>0
order by a.path--去除单车超载的,并显示最后的组合结果:
select path as [货-车组合],cap as 载重,w as 货重
from #result t
where not exists(select 1 from #result where path=t.path group by cap having sum(w)>cap)
order by path,cap desc
/*
货-车组合 载重 货重
------------------------------
1-1,2-3,3-1,4-3 10.0 8.0
1-1,2-3,3-1,4-3 10.0 2.0
1-1,2-3,3-1,4-3 5.0 2.5
1-1,2-3,3-1,4-3 5.0 1.5
1-1,2-3,3-3,4-1 10.0 8.0
1-1,2-3,3-3,4-1 10.0 1.5
1-1,2-3,3-3,4-1 5.0 2.5
1-1,2-3,3-3,4-1 5.0 2.0
*/--select * from #car
--select * from #goods
--select * from #cop
--select * from #path
--select * from #result
drop table #car
drop table #goods
drop table #cop
drop table #path
drop table #result
--1.避免除0错,避免with语法错
--2.增加装载率期望或方差,使配货更加均匀
--3.把单车超载判断提前,以免漏掉真正最优结果
--车辆表
select id=identity(int),cap into #car
from(select cap=cast(10 as dec(18,1)) union all select 8
union all select 5 union all select 3 union all select 2) t
order by cap desc--货物表
select id=identity(int),w into #goods
from(select w=cast(8 as dec(18,1)) union all select 2
union all select 1.5 union all select 2.5) t
order by w desc--所有可能组合表
select cid=a.id,cap,gid=b.id,w into #cop
from #car a join #goods b on a.cap>=b.w
order by cid--所有有效组合表:组合路径、所需车辆数、车辆id之和、车辆总载重。(总装载率>=90%)
;with szx as
(
select cid,cap,gid,w,path=cast(rtrim(gid)+'-'+rtrim(cid) as varchar(8000)) from #cop where gid=1
union all
select a.cid,a.cap,a.gid,a.w,path=b.path+','+rtrim(a.gid)+'-'+rtrim(a.cid)
from #cop a, szx b
where a.gid=b.gid+1 and charindex(','+rtrim(a.gid)+'-',','+b.path)=0
)
select a.path,cc=count(distinct b.id),ct=sum(distinct b.id),cw=sum(b.cap)
into #path
from szx a join #car b
on charindex('-'+rtrim(b.id)+',',a.path+',')>0
where len(path)=(select count(1) from #goods)*4-1
group by a.path
having (select sum(w) from #goods) between sum(b.cap)*0.9 and sum(b.cap) --按路径展开车辆和货物的信息,并添加装载率期望(或近似方差):
select *,cl=sum(d) over(partition by path) into #result
from
(
select *,d=abs(avg(scale) over(partition by path)-scale)
from
(
select a.path,a.cc,a.ct,a.cw,b.cap,c.w,scale=(sum(c.w) over(partition by a.path,b.cap))/b.cap
from #path a,#car b,#goods c
where charindex(','+rtrim(c.id)+'-'+rtrim(b.id)+',',','+a.path+',')>0
) t
) tt--去除单车超载的,再依次按所需车辆最少、尽量使用大车、总装载率、装载率期望值等条件选出最优组合并显示:
select path as [货-车组合],cap as 载重,w as 货重
from
(
select path,cap,w,rk=dense_rank() over(order by cc,ct,cw,cl)
from #result t
where not exists(select 1 from #result where path=t.path group by cap having sum(w)>cap)
) tt
where rk=1
order by path,cap desc
/*
货-车组合 载重 货重
------------------------------
1-1,2-3,3-3,4-1 10.0 8.0
1-1,2-3,3-3,4-1 10.0 1.5
1-1,2-3,3-3,4-1 5.0 2.0
1-1,2-3,3-3,4-1 5.0 2.5
*/
drop table #car
drop table #goods
drop table #cop
drop table #path
drop table #result