如果在详细信息表中找到记录，则显示Y / N列

I'm trying to create a query so that I can have a column show Y/N if a particular item was ordered for a group of orders. The item I'm looking for would be OLI.id = '538'.

我正在尝试创建一个查询,以便我可以让列显示Y / N,如果为一组订单订购了特定项目。我正在寻找的项目是OLI.id ='538'。

So my results would be:

所以我的结果是:

Order#, Customer#, FreightPaid
12345, 00112233, Y
12346, 00112233, N

I cannot figure out if I need to use a subquery or the where exists function ?

我无法弄清楚我是否需要使用子查询或where exists函数?

Here's my current query:

这是我当前的查询:

    SELECT distinct
      OrderID,
      Accountuid as Customerno
  FROM [SMILEWEB_live].[dbo].[OrderLog] OL
  inner join Orderlog_item OLI on OLI.orderlogkey = OL.[key]
  inner join Account A on A.uid = OL.Accountuid
  where A.GroupId = 'X9955'
  and OL.CreateDate >= GETDATE() - 60

1 个解决方案

#1

I would suggest an exists clause instead of a join:

我建议使用exists子句而不是连接:

select ol.OrderID, ol.Accountuid as Customerno,
       (case when exists (select 1 
                          from Orderlog_item OLI join
                               Account A
                               on A.uid = OL.Accountuid
                          where OLI.orderlogkey = OL.[key] and A.GroupId = 'X9955'
                         )
             then 1 else 0
        end) as flag
from [SMILEWEB_live].[dbo].[OrderLog] OL
where OL.CreateDate >= GETDATE() - 60;

This prevents a couple of problems. First, duplicate rows which are caused when there are multiple matching rows (and select distinct add unnecessary overhead). Second, missing rows, which happen when you use inner join instead of an outer join.

这可以防止出现一些问题。首先,在存在多个匹配行时导致重复行(并选择distinct添加不必要的开销)。第二,缺少行,当您使用内部联接而不是外部联接时会发生这种情况。

1 个解决方案

#1

更多相关文章

随机推荐