SQL Server：SELECT ID只有一个条件

I have a patients table with details such as conditions that the patient has. from the below table I want to select Patients, Claims which have ONLY a single condition - 'Hypertension'. Example Patient B is the expected output. Patient A will not be selected because he claimed for multiple conditions.

我有一张患者表，其中包含患者所具有的病症等详细信息。从下表我想选择只有一个条件的患者，声称 - “高血压”。示例患者B是预期输出。不会选择患者A，因为他声称有多种情况。

+----+---------+--------------+
| ID | ClaimID |  Condition   |
+----+---------+--------------+
| A  |   14234 | Hypertension |
| A  |   14234 | Diabetes     |
| A  |   63947 | Diabetes     |
| B  |   23853 | Hypertension |
+----+---------+--------------+

I tried using the NOT IN condition as below but doesn't seem to help

我尝试使用NOT IN条件如下，但似乎没有帮助

SELECT ID, ClaimID, Condition 
FROM myTable 
WHERE Condition IN ('Hypertension') 
  AND Condition NOT IN ('Diabetes') 

6 个解决方案

#1

One method uses not exists:

一种方法使用不存在：

select t.*
from mytable t
where t.condition = 'Hypertension' and
      not exists (select 1
                  from mytable t2
                  where t2.id = t.id and t2.condition <> t.condition
                 );

#2

Or you can do it like this:

或者你可以这样做：

select 
    id,
    claim_id,
    condition
from 
    patient
where 
    id in
    (
        select
            id
        from
            patient
        group by
            id having count (distinct condition) = 1
    );

Result:

结果：

id claim_id    condition
-- ----------- ----------------
B        23853 Hypertension

(1 rows affected)

Setup:

建立：

create table patient
(
    id         varchar(1),
    claim_id   int,
    condition  varchar(16)
);

insert into patient (id, claim_id, condition) values ('A', 14234, 'Hypertension');
insert into patient (id, claim_id, condition) values ('A', 14234, 'Diabetes');
insert into patient (id, claim_id, condition) values ('A', 63947, 'Diabetes');
insert into patient (id, claim_id, condition) values ('B', 23853, 'Hypertension');

#3

You can do this with a CTE.

你可以用CTE做到这一点。

I set up this CTE with two parameters, one being the Condition you seek, and the other being the max number of combined conditions to find (in your case 1).

我使用两个参数设置此CTE，一个是您寻找的条件，另一个是要查找的组合条件的最大数量（在您的情况下为1）。

DECLARE @myTable TABLE (Id VARCHAR(1), ClaimID INT, Condition VARCHAR(100))
INSERT INTO @myTable (Id, ClaimID, Condition)
SELECT 'A',14234,'Hypertension' UNION ALL
SELECT 'A',14234,'Diabetes' UNION ALL
SELECT 'A',63947,'Diabetes' UNION ALL
SELECT 'B',23853,'Hypertension'

DECLARE @Condition VARCHAR(100)
DECLARE @MaxConditions TINYINT

SET @Condition='Hypertension'
SET @MaxConditions=1

; WITH CTE AS
(
    SELECT *, COUNT(2) OVER(PARTITION BY ClaimID) AS CN
    FROM @myTable T1
    WHERE EXISTS (SELECT 1 FROM @myTable T2 WHERE T1.ClaimID=T2.ClaimID AND T2.Condition=@Condition)
)

SELECT *
FROM CTE
WHERE CN<=@MaxConditions

If you don't care about the fluff, and just want all ClaimID's with just ONE condition regardless of which condition it is use this.

如果你不关心绒毛，只想让所有的ClaimID只有一个条件，无论它使用哪种条件。

DECLARE @myTable TABLE (Id VARCHAR(1), ClaimID INT, Condition VARCHAR(100))
INSERT INTO @myTable (Id, ClaimID, Condition)
SELECT 'A',14234,'Hypertension' UNION ALL
SELECT 'A',14234,'Diabetes' UNION ALL
SELECT 'A',63947,'Diabetes' UNION ALL
SELECT 'B',23853,'Hypertension'

DECLARE @MaxConditions TINYINT

SET @MaxConditions=1

; WITH CTE AS
(
    SELECT *, COUNT(2) OVER(PARTITION BY ClaimID) AS CN
    FROM @myTable T1
)

SELECT *
FROM CTE
WHERE CN<=@MaxConditions

#4

Here is one method using Having clause

这是使用Having子句的一种方法

SELECT t.*
FROM   mytable t
WHERE  EXISTS (SELECT 1
               FROM   mytable t2
               WHERE  t2.id = t.id
               HAVING Count(CASE WHEN condition = 'Hypertension' THEN 1 END) > 0
                      AND Count(CASE WHEN condition != 'Hypertension' THEN 1 END) = 0) 

#5

And yet a couple of other ways to do this:

还有其他几种方法可以做到这一点：

    declare @TableA  table(Id char,
                           ClaimId int,
                           Condition varchar(250));
    insert into @TableA (id, claimid, condition)
                values ('A', 14234, 'Hypertension'),
                       ('A', 14234, 'Diabetes'),
                       ('A', 63947, 'Diabetes'),
                       ('B', 23853, 'Hypertension')       


    select id, claimid, condition
    from @TableA a
    where not exists(select id
                     from @TableA b
                     where a.id = b.id
                     group by b.id
                     having count(b.id) > 1)

    OR

    ;with cte as
   (
     select id, claimid, condition
     from @TableA
   )
   ,
   cte2 as
   (
     Select id, count(Id) as counts
     from cte
     group by id
     having count(id) < 2       
    )

   Select cte.id, claimid, condition
   From cte
   inner join
   cte2
   on cte.id = cte2.id

#6

I decided to revise my answer into an appropriate one.

我决定将我的答案修改为合适的答案。

A simple solution to your question is to count the rows instead of the ID values (since it's not an integer).

问题的一个简单解决方案是计算行而不是ID值（因为它不是整数）。

Here is a simple introduction:

这是一个简单的介绍：

SELECT 
    ID 
FROM 
    #PatientTable 
GROUP BY 
    ID 
HAVING
    ID = ID AND COUNT(*) = 1 

This will Return the ID B

这将返回ID B.

+----+
| ID |
+----+
| B  |
+----+

Surely, this is not enough, as you may work with a large data and need more filtering.

当然，这还不够，因为您可能需要处理大量数据并需要更多过滤。

So, we will go and use it as a sub-query.

因此，我们将把它用作子查询。

Using it as a sub-query it's simple :

使用它作为子查询很简单：

SELECT
    ID, 
    ClaimID, 
    Condition
FROM 
    #PatientTable
WHERE 
    ID = (SELECT ID AS NumberOfClaims FROM #PatientTable GROUP BY ID HAVING ID = ID AND COUNT(*) = 1)

This will return

这将返回

+----+---------+--------------+
| ID | ClaimID |  Condition   |
+----+---------+--------------+
| B  |   23853 | Hypertension |
+----+---------+--------------+

So far so good, but there is another issue we may face. Let's say you have a multiple Claims from a multiple patients, using this query as is will only show one patient. To show all patients we need to use IN rather than = under the WHERE clause

到目前为止一切顺利，但我们可能面临另一个问题。假设您有多个患者的多个声明，使用此查询只会显示一个患者。为了显示所有患者，我们需要在WHERE子句下使用IN而不是=

WHERE 
    ID IN (SELECT ID AS NumberOfClaims FROM #PatientTable GROUP BY ID HAVING ID = ID AND COUNT(*) = 1)

This will list all patients that falls under this condition.

这将列出所有属于这种情况的患者。

If you need more conditions to filter, you just add them to the WHERE clause and you'll be good to go.

如果你需要更多条件来过滤，你只需将它们添加到WHERE子句中就可以了。