从SQL Server中的表中仅选择具有公共状态的行

I have table as follows:

我有如下表:

ID CustomerID  AccountNumber StatusID
-------------------------------------
1     300          300100       1
2     300          300200       3
3     300          300300       3
4     400          400100       1
5     400          400200       1
6     500          500100       1

StatusID:

1 = Approved

1 =已批准

3 = Pending

3 =待定

Now I need to select all customers whose accounts are approved (none are pending), but not customers whose accounts are still pending.

现在,我需要选择其帐户已获批准的所有客户(没有待处理的客户),但不会选择其帐户仍处于待处理状态的客户。

Please let me know if you need more details from my end.

如果您需要我的更多详细信息,请告诉我。

7 个解决方案

#1

This is the another simple way to do basing on your sample data provided

这是另一种基于提供的样本数据的简单方法

SELECT DISTINCT 
    T.ID, 
    T.CustomerID  , 
    T.AccountNumber , 
    T.StatusID 
FROM 
    Table1 T
INNER JOIN 
    (SELECT CID 
     FROM @Table1 
     WHERE StatusID = 3) TT ON T.CID <> TT.CID

#2

Group by the customer and take only those having no status <> 1

由客户分组并仅接受状态<> 1的那些

select customerID
from your_table
group by customerID
having sum(case when status <> 1 then 1 else 0 end) = 0

#3

Use HAVING Clause with MAX, it will give you all customers who has only status "Approved" =1

使用带有MAX的HAVING子句,它将为您提供仅具有“已批准”状态= 1的所有客户

SELECT 
    CustomerID
FROM @tblTest
GROUP BY CustomerID
HAVING MAX(StatusID)=1

#4

It is easy to get a list of all customers with pending accounts:

很容易获得具有待定帐户的所有客户的列表:

select customerID
from your_table
where StatusID = 3

And thus just select data where customer is not in the above:

因此,只需选择客户不在上面的数据:

select distinct CustomerID
from your_table
where CustomerID not in (select customerID
                         from your_table
                         where StatusID = 3)

(distict to avoid duoplicates.)

(为了避免死亡而畏惧。)

#5

The first answer seems to work.Another alternative way would be to do this which is a bit lengthy though

第一个答案似乎有效。另一种方法是做这个虽然有点冗长

 select distinct customerid from 
 (
  select customerid,count(1) as cnt 
  from 
 (select customerid,statusID,count(1) as cnt 
  from ids 
  group by customerid,statusid)a
  group by customerid
  having count(1)=1
)a

#6

Just an another way of doing is with simple self join.

另一种做法是简单的自我加入。

Schema:

CREATE TABLE #TAB (ID INT, CustomerID INT,  AccountNumber VARCHAR(20), StatusID INT)

INSERT INTO #TAB

SELECT 1,300,300100,  1 
UNION ALL
SELECT 2,300,300200,  3
UNION ALL
SELECT 3,300,300300,  3
UNION ALL
SELECT 4,400,400100,  1
UNION ALL
SELECT 5,400,400200,  1
UNION ALL
SELECT 6,500,500100,  1

And make a Self Join like below, and filter with Where clause who are having pending status records.

并像下面一样进行自我加入,并使用具有待定状态记录的Where子句进行过滤。

SELECT AP.* FROM #TAB AP
LEFT JOIN #TAB P ON AP.CustomerID = p.CustomerID
AND P.StatusID=3 
WHERE AP.StatusID=1
AND P.ID IS NULL

#7

You can use simple query like below to get the all customers whose accounts are approved

您可以使用下面的简单查询来获取其帐户获得批准的所有客户

select distinct(customerID) 
from your_table 
where StatusID = 1

#1