时间戳

首页 > 解决方案 > 获取待处理行的 SQL 查询

sql - 获取待处理行的 SQL 查询

问题描述

基本详情

+----+---------------------+
| DocumentId  | DocumentNo |
+----+---------------------+
|  1          |  100       |
+----+---------------------+

订单项

+----+--------+------------+
| Id | ItemNo | DocumentId |
+----+--------+------------+
|  1 |   4001 |          1 |
|  2 |   4002 |          1 |
|  3 |   4003 |          1 |
|  4 |   4004 |          1 |
+----+--------+------------+

选择列表

+----+------------+---------------+--------+----------+------------+
| Id | DocumentNo | ReservationNo | ItemNo | StatusId | PickListNo |
+----+------------+---------------+--------+----------+------------+
|  1 |        100 |           100 |   4001 |        1 |    0000001 |
|  2 |        100 |           100 |   4002 |        2 |    0000001 |
|  3 |        100 |           100 |   4001 |        2 |    0000002 |
|  4 |        100 |           100 |   4003 |        2 |    0000002 |
+----+------------+---------------+--------+----------+------------+

期望的结果：

+----+------------+----------------+
| Id | DocumentNo | LineItemCount  |
+----+------------+----------------+
|  1 |        100 |              3 |
+----+------------+----------------+

主表中的基本详细信息。BasicDetails 中的一个条目可能有多个 LineItems。

ItemNo 在 lineItem 中是唯一的。

BasicDetails 可能有多个pickList。例如： DocumentNo 100 here has two pickLists 0000001 and 0000002

0000001 和 0000002 中可能有相同的 itemno，但同一选项列表不能有重复的 itemno。

选择列表方案 2：

+----+------------+---------------+--------+----------+------------+
| Id | DocumentNo | ReservationNo | ItemNo | StatusId | PickListNo |
+----+------------+---------------+--------+----------+------------+
|  1 |        100 |           100 |   4001 |        2 |    0000001 |
|  2 |        100 |           100 |   4002 |        2 |    0000001 |
|  3 |        100 |           100 |   4001 |        2 |    0000002 |
|  4 |        100 |           100 |   4003 |        2 |    0000002 |
+----+------------+---------------+--------+----------+------------+

期望的结果：

+----+------------+----------------+
| Id | DocumentNo | LineItemCount  |
+----+------------+----------------+
|  1 |        100 |              4 |
+----+------------+----------------+

选择列表方案 3：

+----+------------+---------------+--------+----------+------------+
| Id | DocumentNo | ReservationNo | ItemNo | StatusId | PickListNo |
+----+------------+---------------+--------+----------+------------+
|  1 |        100 |           100 |   4001 |        1 |    0000001 |
|  2 |        100 |           100 |   4002 |        2 |    0000001 |
|  3 |        100 |           100 |   4001 |        1 |    0000002 |
|  4 |        100 |           100 |   4003 |        2 |    0000002 |
+----+------------+---------------+--------+----------+------------+

期望的结果是：

+----+------------+----------------+
| Id | DocumentNo | LineItemCount  |
+----+------------+----------------+
|  1 |        100 |              3 |
+----+------------+----------------+

我的查询：

   select b.documentid,b.documentno,count(l.id) cnt
   from basicdetails b left join lineitems l on b.documentid= l.documentid
   left join picklist p on l.itemno =p.itemno where p.statusid=1
   group by  b.documentid,b.documentno

标签： sqlsql-servertsqlsql-server-2008

解决方案

我不是 100% 确定你需要什么，但到目前为止：

select b.documentid
       , b.documentno
       , (select count(*) from PickList) - (select count(*) 
                                            from PickList pn1
                                            where pn1.StatusId = 1)
from BasicDetails b 
left join LineItems l on b.DocumentId = l.DocumentId
left join PickList p on l.ItemNo = p.ItemNo 
where p.StatusId = case when (select count(*) cnt_all 
                              from PickList pn
                              where pn.StatusId = 1) = 0 then 2
                        else 1
                   end
group by  b.documentid
          , b.documentno ;

演示：https ://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=797817d75bda9d81bbebcf43d584564a

如果我从评论中了解信息，那将是正确的：

select b.documentid
       , b.documentno
       , case when (select count(*) cnt_all 
                    from PickList pn
                    where pn.StatusId = 1) = 0
              then (select count(*) cnt_all
                    from LineItems)
              else (select count(*) cnt_all
                    from LineItems) -count(distinct l.id)
         end cnt
from BasicDetails b 
left join LineItems l on b.DocumentId = l.DocumentId
left join PickList p on l.ItemNo = p.ItemNo
where p.StatusId = case when (select count(*) cnt_all 
                              from PickList pn
                              where pn.StatusId = 1) = 0 then 2
                        else 1
                   end
group by  b.documentid
          , b.documentno;

演示 2：https ://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=233e5ef14a2b6aaa45ec509872f240c9

推荐阅读