Table1表,如何根据给出houdu厚度,kuandu宽度,changdu长度,进行优选选择材料
举例:houdu=0.7(厚度是先给出的是定值,一定先要根据给定houdu再根据给出的kuandu,changdu进行优选,优选的宽度要大于等于给出kuandu,changdu也是,找出最接近的),给出kuandu=830,changdu=1100,优选后最接近的材料为 木板1 0.7 850 1000,如给定kuandu=800,changdu=1050,则优选后就是 木板4 0.7 1100 750,请问SQL如何优选(长会变成宽)?
求SQL name houdu kuandu changdu
木板1 0.7 850 1000
木板2 0.7 850 1200
木板3 0.7 950 2000
木板4 0.7 1100 750
木板5 0.7 600 1100
举例:houdu=0.7(厚度是先给出的是定值,一定先要根据给定houdu再根据给出的kuandu,changdu进行优选,优选的宽度要大于等于给出kuandu,changdu也是,找出最接近的),给出kuandu=830,changdu=1100,优选后最接近的材料为 木板1 0.7 850 1000,如给定kuandu=800,changdu=1050,则优选后就是 木板4 0.7 1100 750,请问SQL如何优选(长会变成宽)?
求SQL name houdu kuandu changdu
木板1 0.7 850 1000
木板2 0.7 850 1200
木板3 0.7 950 2000
木板4 0.7 1100 750
木板5 0.7 600 1100
给出kuandu=830,changdu=1100大于等于 kuangdu又要大于等于
830 同样changdu要大于等于1100 只有是木板3 只有木板3才满足条件
而你给出的答案说是
木板1 0.7 850 1000 这是什么规律??
kuandu1差50接近给定值,changdu1差100接近给定值
kuandu2差100接近给定值,changdu2差50接近给定值选择哪个
(
name varchar(10),
houdu decimal(10,1),
kuandu int,
changdu int
)
insert into t1
select '木板1', 0.7, 850, 1000 union all
select '木板2', 0.7, 850, 1200 union all
select '木板3', 0.7, 950, 2000 union all
select '木板4', 0.7, 1100, 750 union all
select '木板5', 0.7, 600, 1100declare @houdu decimal(10,1),
@kuandu int,
@changdu int
select @houdu=0.7,@kuandu=1100,@changdu=750
;with aaa as
(select *,kuandu*changdu as row from t1 where houdu=@houdu and kuandu*changdu<=@kuandu*@changdu)
,bbb as
(select ROW_NUMBER() over(order by row desc) as rowindex,* from aaa)
select * from bbb where rowindex=1--------------------------
rowindex name houdu kuandu changdu row
1 木板4 0.7 1100 750 825000
create table t1
(
name varchar(10),
houdu decimal(10,1),
kuandu int,
changdu int
)
insert into t1
select '木板1', 0.7, 850, 1000 union all
select '木板2', 0.7, 850, 1200 union all
select '木板3', 0.7, 950, 2000 union all
select '木板4', 0.7, 1100, 750 union all
select '木板5', 0.7, 600, 1100declare @houdu decimal(10,1),
@kuandu int,
@changdu int
select @houdu=0.7,@kuandu=1100,@changdu=750select name,houdu,kuandu,changdu
from (
select *,rid=row_number() over (partition by houdu order by abs(kuandu-@kuandu),abs(changdu-@changdu))
from t1
) t
where houdu = @houdu and rid = 1drop table t1/**********************name houdu kuandu changdu
---------- --------------------------------------- ----------- -----------
木板4 0.7 1100 750(1 行受影响)
create table t1
(
name varchar(10),
houdu decimal(10,1),
kuandu int,
changdu int
)
insert into t1
select '木板1', 0.7, 850, 1000 union all
select '木板2', 0.7, 850, 1200 union all
select '木板3', 0.7, 950, 2000 union all
select '木板4', 0.7, 1100, 750 union all
select '木板5', 0.7, 600, 1100declare @houdu decimal(10,1),
@kuandu int,
@changdu int
select @houdu=0.7,@kuandu=1100,@changdu=750select *
from t1 t
where not exists (select 1 from t1 where houdu=t.houdu
and (abs(kuandu-@kuandu)<abs(t.kuandu-@kuandu)
or (abs(kuandu-@kuandu)=abs(t.kuandu-@kuandu) and abs(changdu-@changdu)<abs(t.changdu-@changdu))))
and houdu = @hoududrop table t1/*****************name houdu kuandu changdu
---------- --------------------------------------- ----------- -----------
木板4 0.7 1100 750(1 行受影响)
name houdu kuandu changdu
---------- --------------------------------------- ----------- -----------
木板4 0.7 950 2000
给出kuandu=830,changdu=1100,优选后最接近的材料为 木板1 0.7 850 1000,如给定kuandu=800,changdu=1050,则优选后就是 木板4 0.7 1100 750
”
矛盾么?
http://topic.csdn.net/u/20120307/21/90771e7a-cbb6-47ae-83b1-5ccbef494310.html