选择相关表中具有特定行数的行数

2021-09-10 00:00:00 sql tsql sql-server

所以我不太确定为什么今天早上这个问题给我带来了这么多麻烦.我要把它归结为星期五早上.:) 目标是按频率统计该时期内拥有零张照片、三张照片和六张或更多照片的房地产数量.我需要一个看起来像这样的结果集(注意:三列或更多列不应该包括正好有六张的照片——最多可以有六张照片):

So I'm not quite sure why this one is giving me so much trouble this morning. I'm going to chalk it up to being Friday morning. :) The goal is to count, by frequency, the number of real estate properties in that period that have zero photos, three photos and six or more photos. I need a result set that looks like this (NOTE: the "three column or more should NOT include ones that have exactly six -- there can be a max of six photos):

Period Start      Period End        Zero      ThreeOrMore     ExactlySix
------------------------------------------------------------------------
1/1/2011          1/7/2011          15        132             512
1/8/2011          1/14/2011         44        123             402

我补上了日期，我实际使用的 SQL 生成了正确的周开始/结束日期.

I made the dates up, the SQL I have actually produces the correct week start/end dates.

我的属性"表如下所示:

My "Property" table looks like this:

PropertyRecId    Other fields
-----------------------------
12345            <blah>
56789            <blah>

我的PropertyPhoto"表如下所示(仅包含必要的字段):

My "PropertyPhoto" table looks like this (only the necessary fields included):

PropertyPhotoId   PropertyRecId     CreatedOn
---------------------------------------------
1                 12345             3/1/2011
2                 12345             3/1/2011
etc...

我正在使用下面的查询，但我并不感激它.我只需要完成目标.请记住，对于我现有的查询，我无法访问顶级选择中的Propery"记录.我尝试将查询放到创建 PeriodStart 和 PeriodEnd 的辅助选择中，但这只是导致了不同的问题.

I'm using the query below but I'm not beholden to it. I just need to accomplish the goal. Keep in mind that with my existing query, I don't have access to the "Propery" record in my top-level select. I tried dropping the queries down into the secondary select where PeriodStart and PeriodEnd are created, but that just caused different issues.

DECLARE @DateStart datetime
DECLARE @DateEnd datetime
SET @DateStart = '1/1/2011'
SET @DateEnd = '12/31/2011'

DECLARE @Frequency varchar(50)
SET @Frequency = 'month'

SELECT
PeriodStart,
PeriodEnd,
??? As Zero,
??? As ThreeOrMore,
??? As ExactlySix
FROM (
SELECT
PeriodStart = CASE @Frequency
WHEN 'day'     THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, CreatedOn))
WHEN 'week'    THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(DAY, 1 - DATEPART(WEEKDAY, CreatedOn), CreatedOn)))
WHEN 'month'   THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(MONTH,   DATEDIFF(MONTH,   0, CreatedOn), 0)))
WHEN 'quarter' THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, CreatedOn), 0)))
WHEN 'year'    THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(YEAR,    DATEDIFF(YEAR,    0, CreatedOn), 0)))
END,
PeriodEnd   = CASE @Frequency
WHEN 'day'     THEN DATEADD(s, -1, DATEADD(day, 1, DATEDIFF(DAY, 0, CreatedOn)))
WHEN 'week'    THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, 7 - DATEPART(WEEKDAY, CreatedOn), CreatedOn))))
WHEN 'month'   THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, -1, DATEADD(MONTH,   DATEDIFF(MONTH,   0, CreatedOn) + 1, 0)))))
WHEN 'quarter' THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, CreatedOn) + 1, 0)))))
WHEN 'year'    THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, -1, DATEADD(YEAR,    DATEDIFF(YEAR,    0, CreatedOn) + 1, 0)))))
END     
FROM Property P
WHERE CreatedOn BETWEEN @DateStart AND @DateEnd
) s
GROUP BY
PeriodStart,
PeriodEnd
ORDER BY PeriodStart

推荐答案

你可以在你的子查询中使用 over 函数来得到这个，并在你的外部使用 case 语句选择添加它们:

You can use the over function in your subquery to get this, and case statements in your outer select to add 'em up:

DECLARE @Frequency varchar(50)
SET @Frequency = 'month'

SELECT
    PeriodStart,
    PeriodEnd,
    SUM(case when s.PhotoCount = 0 then 1 else 0 end) As Zero,
    SUM(case when s.PhotoCount between 3 and 5 then 1 else 0 end) As ThreeOrMore,
    SUM(case when s.PhotoCount = 6 then 1 else 0 end) As ExactlySix
FROM (
    SELECT
        PeriodStart = 
        CASE @Frequency
            WHEN 'day'     THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, CreatedOn))
            WHEN 'week'    THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(DAY, 1 - DATEPART(WEEKDAY, CreatedOn), CreatedOn)))
            WHEN 'month'   THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(MONTH,   DATEDIFF(MONTH,   0, CreatedOn), 0)))
            WHEN 'quarter' THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, CreatedOn), 0)))
            WHEN 'year'    THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(YEAR,    DATEDIFF(YEAR,    0, CreatedOn), 0)))
        END,
        PeriodEnd   = 
        CASE @Frequency
            WHEN 'day'     THEN DATEADD(s, -1, DATEADD(day, 1, DATEDIFF(DAY, 0, CreatedOn)))
            WHEN 'week'    THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, 7 - DATEPART(WEEKDAY, CreatedOn), CreatedOn))))
            WHEN 'month'   THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, -1, DATEADD(MONTH,   DATEDIFF(MONTH,   0, CreatedOn) + 1, 0)))))
            WHEN 'quarter' THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, CreatedOn) + 1, 0)))))
            WHEN 'year'    THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, -1, DATEADD(YEAR,    DATEDIFF(YEAR,    0, CreatedOn) + 1, 0)))))
        END,
        p.PropertyRecId,
        COUNT(ph.PropertyPhotoId) over (partition by p.PropertyRecId) as PhotoCount
    FROM
        Property P
        left join PropertyPhoto ph on
            p.PropertyRecId = ph.PropertyRecId
    WHERE 
        CreatedOn BETWEEN @DateStart AND @DateEnd
) s
GROUP BY
    PeriodStart,
    PeriodEnd
ORDER BY 
    PeriodStart

您也可以使用传统的 group by 进行内部查询，但我喜欢一些 over 并希望引起您的注意:

You can also make your inner query with a traditional group by, but I love me some over and wanted to bring it to your attention:

SELECT
    PeriodStart = 
    CASE @Frequency
        WHEN 'day'     THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, CreatedOn))
        WHEN 'week'    THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(DAY, 1 - DATEPART(WEEKDAY, CreatedOn), CreatedOn)))
        WHEN 'month'   THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(MONTH,   DATEDIFF(MONTH,   0, CreatedOn), 0)))
        WHEN 'quarter' THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, CreatedOn), 0)))
        WHEN 'year'    THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(YEAR,    DATEDIFF(YEAR,    0, CreatedOn), 0)))
    END,
    PeriodEnd   = 
    CASE @Frequency
        WHEN 'day'     THEN DATEADD(s, -1, DATEADD(day, 1, DATEDIFF(DAY, 0, CreatedOn)))
        WHEN 'week'    THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, 7 - DATEPART(WEEKDAY, CreatedOn), CreatedOn))))
        WHEN 'month'   THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, -1, DATEADD(MONTH,   DATEDIFF(MONTH,   0, CreatedOn) + 1, 0)))))
        WHEN 'quarter' THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, CreatedOn) + 1, 0)))))
        WHEN 'year'    THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, -1, DATEADD(YEAR,    DATEDIFF(YEAR,    0, CreatedOn) + 1, 0)))))
    END,
    p.PropertyRecId,
    COUNT(ph.PropertyPhotoId) over (partition by p.PropertyRecId) as PhotoCount
FROM
    Property P
    left join PropertyPhoto ph on
        p.PropertyRecId = ph.PropertyRecId
WHERE 
    CreatedOn BETWEEN @DateStart AND @DateEnd
GROUP BY
    p.PropertyRecId,
    p.CreatedOn

相关文章