问题描述
所以我不太确定为什么今天早上这个问题给我带来了这么多麻烦.我要把它归结为星期五早上.:) 目标是按频率统计该时期内拥有零张照片、三张照片和六张或更多照片的房地产数量.我需要一个看起来像这样的结果集(注意:三列或更多列不应该包括正好有六张的照片——最多可以有六张照片):
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
这篇关于选择相关表中具有特定行数的行数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!