代码之家 › 专栏 › 技术社区 › Bryan Ward

基于两列之间的值的SQL透视

tsql sql-server-2005 sql

Bryan Ward · 技术社区 · 15 年前

我有一个表,其中有两列,一列用于开始日期,另一列用于结束日期。我需要编写一个查询,它将返回一年中每个月的一列,如果月份介于0之间,则该列的值为1。PIVOT语句似乎是我在这里要查找的,但从我所能知道的最好的情况来看,PIVOT子句正在查找匹配的值,而不是检查值是否在其他两个值之间。Pivot子句在这里是正确的构造吗,还是需要分解并编写12个case语句,然后聚合这些语句?

3 回复 | 直到 12 年前

Bryan Ward 15 年前

我想我已经找到解决办法了。有三个基本步骤:

在12个月内每个月都有一个约会
检查此日期是否介于开始日期和结束日期之间
透视结果

为了得到12个日期(每个月一个),我使用了一个稍微递归的LIKE WITH语句来创建一个临时表,其中1列包含12个日期:

WITH months (date) AS (
SELECT GETDATE() AS date
UNION ALL
SELECT 
    DATEADD(MONTH,1,date)
FROM months
WHERE DATEDIFF(MONTH,GETDATE(),date) < 12)

从这里我可以交叉连接这个临时表和我真正关心的信息的表。然后我使用开始和结束之间的where日期来过滤不属于该月的任何条目。所以像这样:

SELECT other.Title, MONTH(months.date) CROSS JOIN other
WHERE months.date BETWEEN other.start AND other.end

在这一步中,我们必须小心地只选择结果中明确需要的列,或者那些将使用PIVOT语句聚合的列。

最后,我们必须透视结果:

PIVOT (MAX(PID) FOR date IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))

因此,结果查询可能如下所示:

WITH months (date) AS (
SELECT GETDATE() AS date
UNION ALL
SELECT 
DATEADD(MONTH,1,date)
FROM months
WHERE DATEDIFF(MONTH,GETDATE(),date) < 12)
SELECT  Title,
    [1]     AS January,
    [2]     AS February,
    [3]     AS March,
    [4]     AS April,
    [5]     AS May,
    [6]     AS June,
    [7]     AS July,
    [8]     AS August,
    [9]     AS September,
    [10]    AS October,
    [11]    AS November,
    [12]    AS December
FROM
(
SELECT other.Title,MONTH(months.date)
CROSS JOIN other
WHERE months.date BETWEEN other.startDate AND other.endDate
) AS subquery
PIVOT (MAX(PID) FOR date IN
([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS p

我已经消除了我为加入我需要的其他信息而经历的所有其他复杂性,所以这实际上不是我编写的查询,但它应该封装我在需要时获得结果所使用的基本查询结构。

Eric H. 15 年前

我同意12个案例陈述。

这实际上是我在回去之前草拟的解决方案,我看到你在其中提到了你的问题。

WGroleau 14 年前

为什么不先计算月份的列,然后使用它来透视呢?

日期部分(月,[日期])

还是我误解了问题?