具有Joins&的复杂SQL视图Where子句 [英] Complex SQL View with Joins & Where clause

问题描述

我的SQL技能水平很基础.我当然已经写了一些一般的查询，并做了一些非常普通的看法.但是一旦我们加入了联接，就在我创建的视图中，我很想获得想要的结果.

My SQL skill level is pretty basic. I have certainly written some general queries and done some very generic views. But once we get into joins, I am choking to get the results that I want, in the view I am creating.

我觉得我快到了.就是拿不到最后一块

I feel like I am almost there. Just can't get the final piece

SELECT dbo.ics_supplies.supplies_id,
       dbo.ics_supplies.old_itemid,
       dbo.ics_supplies.itemdescription,
       dbo.ics_supplies.onhand,
       dbo.ics_supplies.reorderlevel,
       dbo.ics_supplies.reorderamt,
       dbo.ics_supplies.unitmeasure,
       dbo.ics_supplies.supplylocation,
       dbo.ics_supplies.invtype,
       dbo.ics_supplies.discontinued,
       dbo.ics_supplies.supply,
       dbo.ics_transactions.requsitionnumber,
       dbo.ics_transactions.openclosed,
       dbo.ics_transactions.transtype,
       dbo.ics_transactions.originaldate
FROM   dbo.ics_supplies
       LEFT OUTER JOIN dbo.ics_orders
                    ON dbo.ics_supplies.supplies_id = dbo.ics_orders.suppliesid
       LEFT OUTER JOIN dbo.ics_transactions
                    ON dbo.ics_orders.requisitionnumber =
                       dbo.ics_transactions.requsitionnumber
WHERE  ( dbo.ics_transactions.transtype = 'PO' ) 

当我不包括WHERE子句时，我的视图中会得到17,000多个记录.那是不对的.这样做是因为我们要在1对多表上进行匹配.耗材表是12,000条记录.始终应该有12,000条记录.再也没有了.永远不会少.

When I don't include the WHERE clause, I get 17,000+ records in my view. That is not correct. It's doing this because we are matching on a 1 to many table. Supplies table is 12,000 records. There should always be 12,000 records. Never more. Never less.

我所缺少的是:

1. 我只需要ICS_Transactions表中的一条匹配记录.理想情况下，我想要的是最新的"ICS_Transactions.OriginalDate".

1. I only need ONE matching record from the ICS_Transactions Table. Ideally, the one that I want is the most current 'ICS_Transactions.OriginalDate'.

我只希望ICS_Transactions Table字段填充IF ICS_Transacions.Type ='PO'.否则，这些字段应保持为空.

I only want the ICS_Transactions Table fields to populate IF ICS_Transacions.Type = 'PO'. Otherwise, these fields should remain null.

示例代码或其他任何东西都会有很大帮助.我已经对联接进行了大量研究，但是对于获得结果所需的信息仍然非常困惑.

Sample code or anything would help a lot. I have done a lot of research on joins and it's still very confusing to get what I need for results.

编辑/更新

我感觉好像是我以错误的方式提出了问题，或者没有对我所提出的问题给出全面的了解.为此，我深表歉意.我对SQL还是很陌生，但要努力.

I feel as if I asked my question in the wrong way, or didn't give a good overall view of what I am asking. For that, I apologize. I am still very new to SQL, but trying hard.

ICS_Supplies表具有12,810条记录ICS_Orders表具有3,666条记录ICS_Transaction表具有4,701条记录

ICS_Supplies Table has 12,810 records ICS_Orders Table has 3,666 records ICS_Transaction Table has 4,701 records

简而言之，我希望看到12,810条记录的结果.不多也不少.我正在尝试从ICS_Supplies表中创建所有记录的视图.

In short, I expect to see a result of 12,810 records. No more and no less. I am trying to create a View of ALL records from the ICS_Supplies table.

并非供应表中的所有记录都在订单和/或交易表中.但是，无论如何，我想查看所有12,810条记录.

Not all records in Supply Table are in Orders and or Transaction Table. But still, I want to see all 12,810 records, regardless.

我的用户要求，如果这些供应中的任何一个都具有未清的PO(ICS_Transactions.OpenClosed ='Open'和ICS_Transactions.InvType ='PO')，那么，我还希望查看ICS_Transactions的其他字段(ICS_Transactions.OpenClosed，ICS_Transactions.InvType，ICS_Transactions.OriginalDate，ICS_Transactions.RequsitionNumber).

My users have requested that IF any of these supplies have an open PO (ICS_Transactions.OpenClosed = 'Open' and ICS_Transactions.InvType = 'PO') Then, I also want to see additional fields from ICS_Transactions (ICS_Transactions.OpenClosed, ICS_Transactions.InvType, ICS_Transactions.OriginalDate, ICS_Transactions.RequsitionNumber).

如果没有用于供应记录的未清采购订单，则这些其他字段应为空白/空(无论这些添加的字段中包含什么数据，如果不符合条件，则它们应显示为空).

If there are no open PO's for supply record, then these additional fields should be blank/null (regardless to what data is in these added fields, they should display null if they don't meet the criteria).

只需要将ICS_Orders表从ICS_Supplies跳转到ICS_Transactions(我首先需要从Orders字段中获取请购单，如果有的话).

The ICS_Orders Table is nly needed to hop from the ICS_Supplies to the ICS_Transactions (I first, need to obtain the Requisition Number from the Orders field, if there is one).

很抱歉，如果我没有做好解释.请询问您是否需要澄清.

I am sorry if I am not doing a good job to explain this. Please ask if you need clarification.

推荐答案

这是Ross Bush回答的简化版本(它删除了CTE的联接，使事情更加集中，加快工作速度并减少了工作量.代码).

Here's a simplified version of Ross Bush's answer (It removes a join from the CTE to keep things more focussed, speed things up, and cut down the code).

;WITH
  ordered_ics_transactions AS
(
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY requisitionnumber
                           ORDER BY originaldate DESC
                      )
                        AS seq_id
  FROM
    dbo.ics_transactions
)
SELECT
  s.supplies_id,      s.old_itemid,
  s.itemdescription,  s.onhand,
  s.reorderlevel,     s.reorderamt,
  s.unitmeasure,      s.supplylocation,
  s.invtype,          s.discontinued,
  s.supply,
  t.requsitionnumber, t.openclosed,
  t.transtype,        t.originaldate
FROM
  dbo.ics_supplies           AS s
LEFT OUTER JOIN
  dbo.ics_orders             AS o
    ON  o.supplies_id = s.suppliesid
LEFT OUTER JOIN
  ordered_ics_transactions   AS t
    ON  t.requisitionnumber = o.requisitionnumber
    AND t.transtype         = 'PO'
    AND t.seq_id            = 1

这仅会结合每个请购单编号的最新交易记录，并且只有它具有 transtype ='PO'

This will only join the most recent transaction record for each requisitionnumber, and only if it has transtype = 'PO'

如果您要撤消该(仅合并具有 transtype ='PO'的交易记录，而仅合并最近的交易记录)，然后移动 transtype ='PO'过滤为 ordered_ics_transactions CTE中的 WHERE 子句.

IF you want to reverse that (joining only transaction records that have transtype = 'PO', and of those only the most recent one), then move the transtype = 'PO' filter to be a WHERE clause inside the ordered_ics_transactions CTE.

这篇关于具有Joins&的复杂SQL视图Where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！