从具有排除项的表中选择值

本文介绍了从具有排除项的表中选择值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述 29岁程序员，3月因学历无情被辞！选择 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，（ select distinct DispatchorderId，ManufacturerSerialNumber，Serial，dateDispathced，[Despatch Order 否]，DepotTypeId 来自 VwDispatchedall）d 其中 r.ManufacturerSerialNumber = d.ManufacturerSerialNumber collate database_default 和 d.Workcompleted> r.DateReceived 嗨以上是我的sql查询，我正在运行以获取所有收到的日期与发送的日期。我遇到的问题是每个收到的日期加入每个收到的日期，所以我添加了dateDispathced>的排除位置。 DateReceived有部分帮助，我现在遇到的问题是，如果有任何收到的日期<发送一个日期它将显示该记录以及以下是ManufacturerSerialNumber上的一个示例 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)dwhere r.ManufacturerSerialNumber = d.ManufacturerSerialNumber collate database_default and d.Workcompleted > r.DateReceivedHi 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 wellbelow is one example on a ManufacturerSerialNumbermanufactserial| Serial|Dispatched OrderNO| ReceivingOrderNo |DateReceived | DateDispatched500025 |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-14which 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[^], OUTERDifference 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.DepotTypeIdFROM VwReceived AS r INNER JOIN VwDispatchedall AS d ON r.ManufacturerSerialNumber = d.ManufacturerSerialNumberWHERE d.Workcompleted > r.DateReceived AND r.DateReceived < d.DateDispatched 这篇关于从具有排除项的表中选择值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！上岸，阿里云！