从具有排除项的表中选择值 [英] Selecting values from tables with Exclusions

问题描述

 选择  distinct  d.DispatchorderId DispatchId，d。[Despatch 订单否]，r.ReceivingOrderNo，d.ManufacturerSerialNumber，d.Serial，r.DateReceived DateReceived，d.dateDispathced，d.DepotTypeId 
 
 来自 
（选择 不同 ManufacturerSerialNumber，workcompleted DateReceived，[Despatch  Order  No] ReceivingOrderNo  from  VwReceived）r，
（< span class =code-keyword> select   distinct  DispatchorderId，ManufacturerSerialNumber，Serial，dateDispathced，[Despatch  Order 否]，DepotTypeId 来自 VwDispatchedall）d 
 其中 r.ManufacturerSerialNumber = d.ManufacturerSerialNumber  collat​​e  database_default 和 d.Workcompleted> r.DateReceived 

嗨以上是我的sql查询，我正在运行以获取所有收到的日期与发送的日期。

我遇到的问题是每个收到的日期加入每个收到的日期，所以我添加了dateDispathced>的排除位置。 DateReceived有部分帮助，我现在遇到的问题是，如果有任何收到的日期<发送一个日期它将显示该记录以及



以下是ManufacturerSerialNumber上的一个示例



< pre lang =text> manufactserial | Serial | Dispatched OrderNO | ReceivingOrderNo | DateReceived | DateDispatched
500025 | 20000 | 4555 | | 1002 | 2013-01-14 | 2013年2月11日|
500025 | 20000 | 5000 | | 1002 | 2013-01-14 | 2013年3月14日|
500025 | 20000 | 5000 | | 1689 | 2013-03-01 | 2013-03-14 |

我不希望第二行显示，因为不是有效的已接收与已发送。



第二行显示2013-01-14至2013-03-14发送日期收到的日期

这是不正确的，因为正确的数据显示在下一行，其中收到的日期是2013-03-01，发送日期是2013-03-14所以基本上我需要在一个发货日期显示一个收到的日期

解决方案

你有2个表： VwDispatchedall ， VwReceived 。你为什么不用加入' [ ^ ]？

有几种类型的JOIN'： LEFT [ ^ ]，权利 [ ^ ]， INNER [ ^ ]，外部



它们之间的区别如下所示： SQL连接的可视化表示 [ ^ ]

  SELECT  DESTINCT d.DispatchorderId DispatchId，d。[Despatch  Order  No]，r.ReceivingOrderNo，d.ManufacturerSerialNumber ，d.Serial，r.DateReceived DateReceived，d.dateDispathced，d.DepotTypeId 
  FROM  VwReceived  AS  r  INNER   JOIN  VwDispatchedall  AS  d  ON  r.ManufacturerSerialNumber = d.ManufacturerSerialNumber 
  WHERE  d.Workcompleted> r.DateReceived  AND  r.DateReceived< d.DateDispatched 

select  distinct d.DispatchorderId DispatchId, d.[Despatch Order No],r.ReceivingOrderNo,  d.ManufacturerSerialNumber,d.Serial, r.DateReceived DateReceived, d.dateDispathced ,d.DepotTypeId

                     from
(select distinct ManufacturerSerialNumber,workcompleted DateReceived,[Despatch Order No] ReceivingOrderNo from VwReceived)r,
(select distinct DispatchorderId, ManufacturerSerialNumber, Serial ,dateDispathced,[Despatch Order No],DepotTypeId   from VwDispatchedall)d
where r.ManufacturerSerialNumber = d.ManufacturerSerialNumber collate database_default and  d.Workcompleted > r.DateReceived

Hi Above is my sql query that i am running to get all the received dates vs despatched dates.
Problem i am having it was joining each received date with each dispatched date, so i added the exclusion of where dateDispathced > DateReceived helped partially, problem i am now having is if there is any received dates < one date dispatched it will show that record as well

below is one example on a ManufacturerSerialNumber

manufactserial| Serial|Dispatched OrderNO| ReceivingOrderNo |DateReceived | DateDispatched
500025        |20000  |      4555   |          |1002        |2013-01-14   | 2013-02-11|
500025        |20000  |      5000   |          |1002        |2013-01-14   | 2013-03-14|
500025        |20000  |      5000   |          |1689        |2013-03-01   | 2013-03-14|

I do not want the second line to show as that is not a valid Received vs Dispatched.

The second line shows a the date received as 2013-01-14 to the dispatch date of 2013-03-14
which is incorrect as the correct data is represented on the next line where the date received is 2013-03-01 and dispatched date is 2013-03-14 so basicly i need to show one received date to one dispatch date

解决方案

You have 2 tables: VwDispatchedall, VwReceived. Why don''t you use JOIN''s[^]?
There is several types of JOIN''s: LEFT[^], RIGHT[^], INNER[^], OUTER

Difference between them is shown here: Visual Representation of SQL Joins[^]

SELECT DESTINCT d.DispatchorderId DispatchId, d.[Despatch Order No],r.ReceivingOrderNo,  d.ManufacturerSerialNumber,d.Serial, r.DateReceived DateReceived, d.dateDispathced ,d.DepotTypeId
FROM VwReceived AS r INNER JOIN VwDispatchedall AS d ON r.ManufacturerSerialNumber = d.ManufacturerSerialNumber
WHERE d.Workcompleted > r.DateReceived AND r.DateReceived < d.DateDispatched

这篇关于从具有排除项的表中选择值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！