qq_45189757
夏尔勒克莱尔
采纳率85.7%
2019-06-12 11:58

求问大佬们这题用 SQL server 怎么解?

已采纳

有以下三张表
主表:POHead
订单号 最后打印日期(任何订单行被打印后会更新此日期)
+-------+---------------------------+
| PONbr | PrintDateTime |
+-------+-------------------------+
| P001 | 2019-01-01 08:40:35.213 |
| P002 | 1900-01-01 00:00:00.000 |
| P003 | 2019-01-02 14:00:00.000 |
+-------+-------------------------+
PONbr 为主键

从表:PODet
订单号 订单行号 物品号 数量 行创建日期
+-------+---------+--------+-----+-------------------------+
| PONbr | LineNbr | ItemId | Qty | CreateDateTime |
+-------+---------+--------+-----+-------------------------+
| P001 | 1 | IT1 | 3 | 2019-01-01 08:00:00.000 |
| P001 | 2 | IT2 | 5 | 2019-01-01 08:00:00.000 |
| P002 | 1 | IT3 | 7 | 2019-01-01 08:00:00.000 |
| P002 | 2 | IT4 | 8 | 2019-01-01 08:00:00.000 |
| P003 | 1 | IT5 | 10 | 2019-01-02 08:00:00.000 |
| P003 | 2 | IT6 | 11 | 2019-01-03 08:00:00.000 |
+-------+---------+--------+-----+-------------------------+
PONbr、LineNbr 为联合主键

修改记录表:MdfLog
订单号 订单行号 修改时间 数量
+-------+---------+-------------------------+-----+
| PONbr | LineNbr | MdfDateTime | Qty |
+-------+---------+-------------------------+-----+
| P001 | 1 | 2019-01-01 08:00:00.000 | 3 |
| P001 | 2 | 2019-01-01 08:00:00.000 | 5 |
| P002 | 1 | 2019-01-01 08:00:00.000 | 7 |
| P002 | 2 | 2019-01-01 08:00:00.000 | 7 |
| P002 | 2 | 2019-01-01 10:00:00.000 | 8 |
| P003 | 1 | 2019-01-02 08:00:00.000 | 2 |
| P003 | 1 | 2019-01-02 18:00:00.000 | 10 |
| P003 | 2 | 2019-01-03 08:00:00.000 | 11 |
| P003 | 2 | 2019-01-04 08:10:00.000 | 12 |
+-------+---------+-------------------------+-----+

目标查询结果:
订单号 订单行号 是否需要打印(0 - 不需要;1 - 需要)
+-------+---------+-----------+
| PONbr | LineNbr | NeedPrin |
+-------+---------+-----------+
| P001 | 1 | 0 |
| P001 | 2 | 0 |
| P002 | 1 | 1 |
| P002 | 2 | 1 |
| P003 | 1 | 1 |
| P003 | 2 | 1 |
+-------+---------+-----------+

题目解释:
订单行记录(PODet)在新增或修改后会记录到 MdfLog 表中。
查询结果为列出所有订单行是否是需要打印状态。
“需要”打印的含义如下:
1. 订单从未被打印过
2. 订单行在打印后有过修改
3. 订单行是在最后一次打印后新增的
★最后一次修改后数量与最后一次打印时数量一致(修改回原来的数量),则不需要打印

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • qq_36263134 南方的夜雨里 2年前

    select ph.PONbr as PONbr,pm.LineNbr as LineNbr,
    (case when(MdfDateTime > PrintDateTime and pm.IsZero != 0 ) then 0 else 1 end) as NeedPrin

    from POHead as ph
    inner join (select ml.MdfDateTime,(ml.Qty - pd.Qty) as IsZero,pd.LineNbr,pd.PONbr from PODet as pd inner join MdfLog as ml on pd.PONbr = ml.PONbr and pd.LineNbr = ml.LineNbr) as pm on ph.PONbr = pm.PONbr

    点赞 评论 复制链接分享