SQL Server:如果列中的一个值至少发生一次或多次。

I'm glad I have a question with at least a working example this time. This used to be an efficient query when I was just had criteria where an result was returned if it had a count >= 1. Then I had to an additional count for different code values and if they occur 2 or more times. The query went from running in a few seconds to about 43 seconds.

我很高兴我有一个问题，至少这次有一个工作的例子。这曾经是一个有效的查询，当我有一个标准，如果它有一个count >= 1时，结果会返回。然后我需要对不同的代码值进行额外的计数，如果它们发生了2次或更多次。查询从几秒钟运行到大约43秒。

I think I have the logic right but I was wondering if someone had a more efficient way to do this.

我想我的逻辑是正确的，但我想知道是否有人有更有效的方法来做这件事。

select person.person_id
from person
where 
person.person_id in (
        select procedure.person_id
        from procedure
        where
        procedure.performed_by = '555555'
        and procedure.code in (
                '99201', '99202'
                )
        and year(procedure.service_date) = year(getdate())
        group by procedure.person_id
        having count(1) >= '1'
        ) -- having count >= 1 occurrences
or person.person_id in (
        select person_id
        from procedure
        where
        procedure.performed_by = '55555'
        and code in (
                '99304','99305'
                )
        and year(procedure.service_date) = year(getdate())
        group by procedure.person_id
        having count(1) >= '2'
        ) -- having count >= 2 occurrences

2 个解决方案

#1

Does this speed it up?

这加速了吗?

WITH CTE AS
(
    select procedure.person_id
    from procedure
    where
    procedure.performed_by = '555555'
    and procedure.code in ( '99201', '99202' )
    and year(procedure.service_date) = year(getdate())
    group by procedure.person_id
    having count(1) >= '1'

    UNION

    select person_id
    from procedure
    where
    procedure.performed_by = '55555'
    and code in ('99304','99305')
    and year(procedure.service_date) = year(getdate())
    group by procedure.person_id
    having count(1) >= '2'
)

select person.person_id
from person 
JOIN CTE ON CTE.Person_id = Person.Person_Id

#2

Your first IN is just checking for existance, so there is no real need of using HAVING (on another note, why are you comparing COUNT(1) with a string?, the result is an INT, so you should use >=1 or >=2 instead). You are also using a function on service_date before comparing it, you shouldn't do that since unables the use of a possible index on that column. I would write your query this way:

您的第一个操作是检查存在，所以没有实际需要使用(在另一个注释中，为什么您要比较COUNT(1)和字符串?结果是INT，所以应该使用>=1或>=2。在对service_date进行比较之前，您还使用了一个函数，因此，您不应该这样做，因为无法使用该列上可能的索引。我这样写您的查询:

select p.person_id
from person p
where exists (  select 1
                from procedure
                where
                procedure.performed_by = '555555'
                and procedure.code in ('99201', '99202')
                and procedure.service_date >= dateadd(year,datediff(year,0,getdate()),0)
                and procedure.service_date < dateadd(year,datediff(year,0,getdate())+1,0)
                and procedure.person_id = p.person_id)
or person.person_id in (
        select person_id
        from procedure
        where
        procedure.performed_by = '55555'
        and code in ('99304','99305')
        and procedure.service_date >= dateadd(year,datediff(year,0,getdate()),0)
        and procedure.service_date < dateadd(year,datediff(year,0,getdate())+1,0)
        group by procedure.person_id
        having count(1) >= 2
        ) -- having count >= 2 occurrences