代码之家 › 专栏 › 技术社区 › ChrisLively

消除不需要的sql子选择的最佳方法?

sql-server-2008 tsql

ChrisLively · 技术社区 · 17 年前

我有一个名为Registrations的表,其中包含以下字段:

身份证件
DateStarted(非空)
已完成日期(可为空)

我有一个条形图,显示了截止日期开始和完成的注册数量。

;
WITH Initial(DateStarted, StartCount)
as (
    select Datestarted, COUNT(*)
    FROM Registrations
    GROUP BY DateStarted    
)
select I.DateStarted, I.StartCount, COUNT(DISTINCT R.RegistrationId) as CompleteCount
    from Initial I
        inner join Registrations R
            ON (I.DateStarted = R.DateCompleted)
    GROUP BY I.DateStarted, I.StartCount

它返回一个如下所示的表:

DateStarted  StartCount  CompleteCount
2009-08-01   1033        903
2009-08-02   540         498

查询只是有一个代码气味问题。有什么更好的方法?

4 回复 | 直到 17 年前

RBarryYoung 17 年前

一开始,我认为这样做:

SELECT
    DateStarted
    , COUNT(*) as StartCount
    , SUM(CASE 
        WHEN DateCompleted = DateStated THEN 1
        ELSE 0 END
        ) as CompleteCount

FROM Registration

GROUP BY DateStarted

好的,显然我以前的要求是错的。假设CompleteCounts独立于StartDate,那么我将这样做:

;WITH StartDays AS
(
    SELECT DateStarted
    , Count(*) AS CompleteCount 
    FROM Registration 
    GROUP BY DateStarted
)
, CompleteDays AS
(
    SELECT DateCompleted
    , Count(*) AS StartCount 
    FROM Registration 
    GROUP BY DateCompleted
)
SELECT
    DateStarted
    , COALESCE(StartCount, 0) AS StartCount
    , COALESCE(CompleteCount, 0) AS CompleteCount

FROM StartDays
FULL OUTER JOIN CompleteDays ON DateStarted = DateCompleted

事实上这和你的情况非常接近。

thomas 17 年前

编辑:那么为什么下面的内容不起作用呢?如果要使计数为零而不是null,可以在最后一个select语句中的计数周围抛出coalesce()语句。它还将包括已完成(或在下面的示例中结束)注册的日期,即使该日期尚未开始注册。

我假设如下表结构(大致)。

create table temp
(
    id int,
    start_date datetime,
    end_date datetime
)

insert into temp values (1, '8/1/2009', '8/1/2009')
insert into temp values (2, '8/1/2009', '8/2/2009')
insert into temp values (3, '8/1/2009', null)
insert into temp values (4, '8/2/2009', '8/2/2009')
insert into temp values (5, '8/2/2009', '8/3/2009')
insert into temp values (6, '8/2/2009', '8/4/2009')
insert into temp values (7, '8/4/2009', null)

然后你可以做下面的事情来得到你想要的。

with start_helper as
(
    select start_date, count(*) as count from temp group by start_date
),

end_helper as
(
    select end_date, count(*) as count from temp group by end_date
)

select coalesce(a.start_date, b.end_date) as date, a.count as start_count, b.count as end_count
from start_helper a full outer join end_helper b on a.start_date = b.end_date
where coalesce(a.start_date, b.end_date) is not null

我认为完整的外部连接是必要的,因为从昨天开始的记录可以在今天完成,但我们今天可能还没有开始新记录,因此您将从结果中损失一天。

John Saunders 17 年前

我看没问题。我看到使用了一个通用的表表达式。

您没有为这些表提供DDL,所以我不打算尝试复制它。但是,我认为您可以直接用SELECT替换初始值。

Shannon Severance 17 年前

select DS.DateStarted
  , count(distinct DS.RegistrationId) as StartCount
  , count(distinct DC.RegistrationId) as CompleteCount
from Registrations DS
inner join Registrations DC on DS.DateStarted = DC.DateCompleted
group by Ds.DateStarted

结果中DateStart列的名称让我有点困惑。这看起来只是一个开始和结束的日子。计数是当天开始和完成的注册数量。

select coalesce(DS.DateStarted, DC.DateCompleted) as "Date"
  , count(distinct DS.RegistrationId) as StartCount
  , count(distinct DC.RegistrationId) as CompleteCount
from Registrations DS
full outer join Registrations DC on DS.DateStarted = DC.DateCompleted
group by Ds.DateStarted, DC.DateCompleted

如果您希望包含计数为0和0的既不是DateStart也不是DateCompleted的日期,那么您将需要一个日期源,我认为在select子句中使用两个相关的子查询而不是JOIN和count distinct会更清楚:

select DateSource."Date"
    , (select count(*)
        from Registrations
        where DateStarted = DateSource."Date") as StartCount
    , (select count (*)
        from Registrations
        where DateCompleted = DateSource."Datge") as CompleteCount
from DateSource -- implementation of date source left as exercise
where DateSource.Date between @LowDate and @HighDate