SQL Server Is it possible to use CTE for the data exist in one table, not 2?

bzzcjhmw 于 2023-06-21 发布在其他

关注(0)|答案(2)|浏览(147)

Imagine a factory for producing credit/debit cards, where the card will go through different stages of production. For example, a card will go through a molding stage, then will pass through a printing stage. Sometimes if there's problem the card will be sent back to the previous stages.

I want to write a query to find all cards that went through printing but is sent back molding (since molding is prior to printing ).

What would be the way to "link" the cards data that exist both stages (they are in the same table):

select distinct barcode
from [Audit]..Stages (nolock)
where clientID = 'AmericanExpress'
    and stagename = 'printing'
    and Description <> 'Success'

select distinct barcode
from [Audit]..Stages (nolock)
where clientID = 'AmericanExpress'
    and stagename = 'Molding'
   and Description = 'Success'

I'm confused on the right way to "filter" since all the data exist in 1 table, not 2.

;
with failure as (
    select distinct barcode
    from [Audit]..Stages (nolock)
    where clientID = 'AmericanExpress'
        and stagename = 'printing'
        and Description <> 'Success'
)

success as (select distinct jt.jtpsid, jt.jtbarcode
from failure
with failure as (
    select distinct barcode
    from [Audit]..Stages (nolock)
    where clientID = 'AmericanExpress'
    and stagename = 'Molding'
    and Description = 'Success')

sql-server

来源：https://stackoverflow.com/questions/76459025/is-it-possible-to-use-cte-for-the-data-exist-in-one-table-not-2

2条答案

按热度按时间

ijnw1ujt1#

You want to select all cards that are currently in the molding stage, and also have a printing stage where the date for the stage did not succeed. To do this you can JOIN the table to itself:

SELECT distinct barcode
FROM [Audit]..Stages s0
INNER JOIN [Audit]..Stages s1 ON s1.barcode = s0.barcode 
    AND s1.stagename = 'printing'
    AND s1.Description <> 'Success'
WHERE s0.stagename = 'molding'
    AND s0.Description = = 'success'

This would be even better if you can know the date of the s1 record is older than for the s0 record, since I expect there's probably also some kind of in-process stage that is also not a success.

Also, get rid of those (nolock) hints. They probably are not accomplishing what you've been led to believe... at least, not in a safe way. (nolock) can make a database faster by ignoring certain locks. This comes at the risk of reading stale data. The thing is, the performance is only improved in those situations when the risk of reading stale data is much higher. If there's no stale data, then there wouldn't be a lock to ignore.

赞(0）回复(0）举报 2023-06-21

gc0ot86w2#

You can use set operators

select
  s.barcode
from Stages s
where s.clientID = 'AmericanExpress'
  and s.stagename = 'printing'
  and s.Description <> 'Success'

intersect

select
  s.barcode
from Stages s
where s.clientID = 'AmericanExpress'
  and s.stagename = 'Molding'
  and s.Description = 'Success';

You can also use conditional aggregation

select
  s.barcode
from Stages s
where s.clientID = 'AmericanExpress'
  and s.stagename = 'printing'
group by
  s.barcode
having count(case when s.stagename = 'Molding'  and s.Description =  'Success' then 1 end) > 0
   and count(case when s.stagename = 'printing' and s.Description <> 'Success' then 1 end) > 0;

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server Is it possible to use CTE for the data exist in one table, not 2?

2条答案

相关问题

热门标签

最新问答