MS Access复杂订购者 [英] MS Access Complicated Order By

问题描述

对于正在开发发票应用程序的我的PHB，我决定发票上列出的已售零件必须按照非常独特的顺序进行运输.如果可能的话，我想用一个sql语句完成此操作.

For an invoicing app I'm working on my PHB has decided that the parts sold listed on the invoice need to go in a very unique order. I would like to accomplish this with a single sql statement if possible.

本质上，顺序必须如此

1. 最昂贵的部分(但只有在$ 0列出其他部分的情况下)
2. 所有零件的价格为$ 0
3. 按part_id的顺序列出的所有其他零件(或所有零件)
4. part_id为("MISC-30"，"MISC-31"，"TEMP")的所有零件
5. 所有零件的负数量[返回]

也需要添加一些注释，但这必须由代码来处理

There is also a jumble of comments that will need to be added but That will have to be handled by the code

到目前为止，我有:

SELECT * 
FROM order_part 
WHERE ordid = 1234 
ORDER BY qty > 0, part_id NOT IN("MISC-30","MISC-31","TEMP"), part_id

但是我不知道如何合并前两个规则

However I cannot figure out how to incorporate the first 2 rules

推荐答案

由于您不得不放弃很久以前在这个项目上一团糟；)

Since you've had to give up being messing long ago on this project ;)

Select * 
    , IIF(((Select Count(*) from order_part 
        where orderid = 1234 and price = 0))=0 
           and price = ((select max(price) from       
               order_part where orderid = 1234
           and qty >0 and part_id not in(("MISC-30","MISC-31","TEMP") 
          )), 1
    , IIf(price = 0, 2
    , IIf(part_id IN("MISC-30","MISC-31","TEMP"), 4
    , IIf(qty < 0, 5
    , 3)))) AS Part_Sort
from order_part
Order By Part Sort, part_id

真的希望Access有案例说明.但是您可以构建这些嵌套的IIf，并根据您的逻辑提供一个排序编号.最终的"ELSE"部分是#3，因为仅按部分ID进行排序是第三选择，并且不属于这些其他类别.抱歉，我知道括号是错误的.

Really wish Access had case statement. But you can build these nested IIf's and provide a sorting number based on your logic. The final "ELSE" part is the #3 since just sorting by the part ID is the third choice/ doesn't fall under these other categories. Sorry, I know the parenthesis are wrong.

这篇关于MS Access复杂订购者的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！