动态查询制作者设计 [英] dynamic query maker design

问题描述

我需要设计一个动态查询/条件制作器，用于指定项目的折扣。

我的问题是如何在条件之间指定逻辑运算符???

可以任何一个建议解决方案??



这适用于动态生成条件，适用于产品价格折扣，如果形状为abc且重量大于1 kg然后0.1％折扣。

第二个条件是如果形状是def或重量小于0.5公斤那么折扣是0.6％

用户可以动态定义这个列表


tnx

I need to design a dynamic query / condition maker , for specifying discount on the items.
my problem is how can i specify logical operators between conditions???
Can any one suggest solution??

This for dynamically genrate conditions for applying discount on product price like if shape is abc and weight is grater than 1 kg then 0.1% discount.
Second is condition will be if shape is "def" or weight is less than 0.5 kg then discount is 0.6%
user can define this list dynamically

tnx

推荐答案

用于提取数据的动态查询构建器 [ ^ ]

带AND / OR的动态查询构建器 [ ^ ]

这是使用存储过程完成的。

This is ealiy done using Stored procedure

@shape varchar(50),
@wt decimal(18,2),

begin
 if(@shape = 'abc' and @wt>1)
   begin
       //your calulation   
   end
 if(@shape = 'gef' and @wt>0.5)
   begin
       //your calulation   
   end 

end 

如果用户管理折扣意味着您在表格中有数据如下所示...

discountSettings

if user manages discount means you have data in table for weight like below...
discountSettings

StartRange EndRange ApplicableDisc
1          10       0.1%
10         100      0.2%

参见示例查询...

see example query...

with tbl_product as
(
    select 'p1' as product , 1  as wgt, 1000 as price
    union all
    select 'p2' as product , 15 as wgt, 1000 as price
)

select *,
    (price*ApplyDisc)/100 as DiscRs,price-((price*ApplyDisc)/100) as BillPrice
from tbl_product as a
    left join
    (
        --your discount setting table
        select 1  as StartRange, 10  as EndRange , 0.1 as ApplyDisc
            union all
        select 10 as StartRange, 100 as EndRange , 0.2 as ApplyDisc
    )
    as t on a.wgt between t.StartRange and t.EndRange --this is condition e.g. when weight is in range of 1 to 10 discount will 0.1 

快乐编码！

：）

Happy Coding!
:)

这篇关于动态查询制作者设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！