sql查询在两个日期之间检索。 [英] sql query retrieving between two dates.

问题描述

这是我的表格数据

This is My Table Data

  a    b     c      YY   MM  DD  Hr  Min Sec
1080 20006  24122  2013  5   6   17  8   1
1080 20006  24123  2013  5   7   17  16  28
1080 20006  24124  2013  5   11  17  21  21
1080 20006  24125  2013  5   13  17  25  12
1080 20007  24126  2013  7   9   17  30  13
1080 20007  24127  2013  7   9   17  40  2
1080 20007  24128  2013  7   9   17  49  35
1080 20007  24129  2013  7   9   18  0   10
1080 20008  24130  2013  8   9   18  6   27
1080 20008  24131  2013  8   11  18  12  55
1080 20009  24132  2013  5   13  18  19  48
1080 20009  24133  2013  5   13  18  28  36

我想要结果格式化。（结果基于数据（DD）和月份（MM））

I want result bellow format.(Result based on data(DD) and month(MM))

  b     YY   MM  PacketsCountbetweenDD(0t10)     PacketsCountbetweenDD(11t15)
20006  2013  5         2                                2
20007  2013  7         4                                0
20008  2013  8             1                                1
20009  2013  5             0                                1

推荐答案

试试这个。 ..

Try this...

select b,YY,MM,Isnull([0],0) as [PacketsCountbetweenDD(0 to 10)],isnull([10],0) [PacketsCountbetweenDD(11 to 15)],isnull([20],0) [PacketsCountbetweenDD(15 to 20)]  from
(
    SELECT b,YY,MM,c ,PacketCategory
    FROM
    (
        SELECT b,YY,MM, count(c) c, round(DD,-1,case when DD>15 then 0.5 else 1 end) AS PacketCategory
        FROM YourTblNm
        group by  b,YY,MM, round(DD,-1,case when DD>15 then 0.5 else 1 end)
    ) as aa
    GROUP BY b,YY,MM ,c, PacketCategory
) as a
pivot (max (c) for PacketCategory in ([0],[10],[20])) as pvttbl

快乐编码！

：）

Happy Coding!
:)

试试这个：

Try this:

DECLARE @tmp TABLE (a INT, b INT, c INT, [YY] INT, [MM] INT, [DD] INT, [Hr] INT, [Min] INT, [Sec] INT)

INSERT INTO @tmp (a, b, c, [YY], [MM], [DD], [Hr], [Min], [Sec]) 
SELECT 1080 AS a, 20006 as b, 24122 AS c, 2013 AS [YY], 5 AS [MM], 6 AS [DD], 17 AS [Hr], 8 AS [Min], 1 AS [Sec]
UNION ALL
SELECT 1080, 20006, 24123, 2013, 5,  7,  17, 16, 28
UNION ALL
SELECT 1080, 20006, 24124, 2013, 5,  11, 17, 21, 21
UNION ALL
SELECT 1080, 20006, 24125, 2013, 5,  13, 17, 25, 12
UNION ALL
SELECT 1080, 20007, 24126, 2013, 7,  9,  17, 30, 13
UNION ALL
SELECT 1080, 20007, 24127, 2013, 7,  9,  17, 40, 2
UNION ALL
SELECT 1080, 20007, 24128, 2013, 7,  9,  17, 49, 35
UNION ALL
SELECT 1080, 20007, 24129, 2013, 7,  9,  18, 0,  10
UNION ALL
SELECT 1080, 20008, 24130, 2013, 8,  9,  18, 6,  27
UNION ALL
SELECT 1080, 20008, 24131, 2013, 8,  11, 18, 12, 55
UNION ALL
SELECT 1080, 20009, 24132, 2013, 5,  13, 18, 19, 48
UNION ALL
SELECT 1080, 20009, 24133, 2013, 5,  13, 18, 28, 36

SELECT b, [YY], [MM], [Packet0to10], [Packet11to15], [MoreThan15]
FROM (
	SELECT b, [YY], [MM], [PacketName], COUNT([DD]) AS CountOfPackets
	FROM (
		SELECT b, [YY], [MM], [DD], CASE
			WHEN [DD] >=0 AND [DD] <=10 THEN 'Packet0to10'
			WHEN [DD] >10 AND [DD] <=15 THEN 'Packet11to15'
			ELSE 'MoreThan15' END AS [PacketName] 
		FROM @tmp
		) AS T
	GROUP BY b, [YY], [MM], [PacketName]
	) AS DT
PIVOT (SUM([CountOfPackets]) FOR [PacketName] IN ([Packet0to10], [Packet11to15], [MoreThan15])) AS PT

这篇关于sql查询在两个日期之间检索。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！