如何让T-SQL显示不会出现多次的值

I'm sorry, I'm newbie using T-SQL AND I would like to know how can i get value that doesn't occurs more than once. I already tried this but it didn't work.

对不起,我是使用T-SQL的新手,我想知道如何获得不会出现多次的价值。我已经尝试了这个,但它没有用。

SELECT DISTINCT * 
FROM Orders 
WHERE PmtType  NOT IN ('VISA','DISC','FUNDING','DEALER CHECK','MC'
                       ,'AMEX','BONUS POOL','DLR CK - NET30'
                       ,'WIRE','MO','EXCHANGE','ONLINE','NULL')  
   AND OrderDate BETWEEN '2014-03-01 00:00:00' 
                          and '2015-03-01 00:00:00' 
   AND CompanyId  IN ('1311','8390','8394','8396','8397','8399','3966',
                     '8407','8408','8315','8411','8413','8414','8416'
                     ,'8419','4850','8426','8428','8429','8430')

What I'm trying to get is this. Companies that receive Free Demos. Which the PmtType would be free, but never purchased a product.

我想要得到的就是这个。获得免费演示的公司。哪个PmtType是免费的,但从未购买过产品。

If the customer never purchase a product the customer Id shouldn't appear in the

如果客户从不购买产品,则客户ID不应出现在

PmtType IN ('VISA','DISC','FUNDING','DEALER CHECK'
            ,'MC' ,'AMEX','BONUS POOL','DLR CK - NET30'
            ,'WIRE','MO','EXCHANGE','ONLINE','NULL')

1 个解决方案

#1

if i read the question correctly you want to know which IDs have only 1 order, this will do it. I used generic field name as you didnt specify what "value" you want to find...

如果我正确地阅读了这个问题,你想知道哪些ID只有1个订单,这就可以了。我使用了通用字段名称,因为你没有指定你想要找到的“值”...

EDIT: added the NOT EXISTS clause after OP Comment, you may no longer need the group by, that is up to you...

编辑:OP评论后添加了NOT EXISTS条款,您可能不再需要该组,这取决于您...

SELECT CompanyId --add fields here as needed.
     ,Count(*) [Occurences]
FROM Orders o
WHERE PmtType = 'FREE'
AND NOT EXISTS (SELECT CompanyId 
                FROM Orders io 
                WHERE o.CompanyId = io.CompanyId 
                    AND PmtType <> 'FREE' )
GROUP BY CompanyId --add fields here as needed.
HAVING Count(*) = 1  --leave this out to see how many free demos each company got.

#1