www.xxxx日本,精品久久久久久亚洲综合网站 ,天堂a

本文介紹了選擇相關表中具有特定行數的行數的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學習吧！

問題描述

所以我不太確定為什么今天早上這個問題給我帶來了這么多麻煩.我要把它歸結為星期五早上.:) 目標是按頻率統計該時期內擁有零張照片、三張照片和六張或更多照片的房地產數量.我需要一個看起來像這樣的結果集(注意:三列或更多列不應該包括正好有六張的照片——最多可以有六張照片):

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

這篇關于選擇相關表中具有特定行數的行數的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！

【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題，如果有圖片或者內容侵犯了您的權益，請聯系我們刪除處理，感謝您的支持！

久久久久久久av_日韩在线中文_看一级毛片视频_日本精品二区_成人深夜福利视频_武道仙尊动漫在线观看

選擇相關表中具有特定行數的行數

問題描述

推薦答案

相關文檔推薦