有一列datetime类型如求一条sql查询语句语句
给定一短时间,比如'2010-06-05'至'2010-06-12'
将同一天的在下午13点之前出现的次数(count) 的显示在第一行,13点之后的次数(count)显示在第二行
第三行计算总合
日期按顺序显示
输出如下形式结果(图片内容仅供参考,实际数据和结果并不对应)
给定一短时间,比如'2010-06-05'至'2010-06-12'
将同一天的在下午13点之前出现的次数(count) 的显示在第一行,13点之后的次数(count)显示在第二行
第三行计算总合
日期按顺序显示
输出如下形式结果(图片内容仅供参考,实际数据和结果并不对应)
union all
select count(1) from tb where 时间列 between '2010-06-05' and '2010-06-12' and datepart(hh,时间列) >= 13
union all
select count(1) from tb where 时间列 between '2010-06-05' and '2010-06-12'
WHEN CAST(CONVERT(CHAR, 时间列, 8) AS DATETIME)<'13:00' THEN '<13:00'
ELSE '>=13:00'
END AS 类型,
COUNT(1) AS 次数
FROM tb
WHERE 时间列>= '2010-06-05'
AND 时间列<DATEADD(dd, 1, '2010-06-12')
GROUP BY CASE
WHEN CAST(CONVERT(CHAR, 时间列, 8) AS DATETIME)<'13:00' THEN '<13:00'
ELSE '>=13:00'
END
GROUP BY (CONVERT(VARCHAR(10), 时间列, 120) +
CASE
WHEN detepart(hour, 时间列)<13 THEN '<13'
ELSE '>13'
END
那个合计似乎很难出来
SELECT ISNULL(
CASE
WHEN CAST(CONVERT(CHAR, 时间列, 8) AS DATETIME)<'13:00' THEN '<13:00'
ELSE '>=13:00'
END, '合计'
) AS 类型, COUNT(1) AS 次数
FROM tb
WHERE 时间列>= '2010-06-05'
AND 时间列<DATEADD(dd, 1, '2010-06-12')
GROUP BY CONVERT(CHAR, 时间列,23),
CASE
WHEN CAST(CONVERT(CHAR, 时间列, 8) AS DATETIME)<'13:00' THEN '<13:00'
ELSE '>=13:00'
END
WITH ROLLUP
CASE
WHEN CAST(CONVERT(CHAR, 时间列, 8) AS DATETIME)<'13:00' THEN '<13:00'
ELSE '>=13:00'
END, '合计'
) AS 类型, COUNT(1) AS 次数
FROM tb
WHERE 时间列>= '2010-06-05'
AND 时间列<DATEADD(dd, 1, '2010-06-12')
GROUP BY CASE
WHEN CAST(CONVERT(CHAR, 时间列, 8) AS DATETIME)<'13:00' THEN '<13:00'
ELSE '>=13:00'
END
WITH ROLLUP
IF OBJECT_ID('[T_dinapp]') IS NOT NULL
DROP TABLE [T_dinapp]
GO
CREATE TABLE [T_dinapp] ([times] [datetime])
INSERT INTO [T_dinapp]
SELECT '2010-6-10 9:00:00' UNION ALL
SELECT '2010-6-10 10:00:00' UNION ALL
SELECT '2008-6-4 23:00:00' UNION ALL
SELECT '2010-6-11 1:00:00' UNION ALL
SELECT '2010-6-11 15:00:00' UNION ALL
SELECT '2010-3-8 18:00:00' UNION ALL
SELECT '2010-6-10 19:00:00' UNION ALL
SELECT '2010-6-10 20:00:00' UNION ALL
SELECT '2010-6-11 19:00:00' UNION ALL
SELECT '2010-6-12 19:00:00' UNION ALL
SELECT '2008-4-5 15:00:00' UNION ALL
SELECT '2009-6-5 12:00:00' UNION ALL
SELECT '2010-4-8 12:00:00'--SELECT * FROM [T_dinapp]-->SQL查询如下:
DECLARE @t1 DATETIME,@t2 DATETIME
SELECT @t1='2010-06-05',@t2='2010-06-12'
DECLARE @s VARCHAR(MAX)
SELECT @s=ISNULL(@s,'')+',MAX(CASE [日期] WHEN '''+CONVERT(CHAR(10), [times],23)+''' THEN 次数 ELSE 0 END)['+CONVERT(CHAR(10), [times],23)+']'
FROM [T_dinapp]
WHERE [times]>= @t1
AND [times]<DATEADD(dd, 1, @t2)
GROUP BY CONVERT(CHAR(10), [times],23)
SET @s='SELECT ISNULL(类型,''合计'') 类型'+@s+'
FROM(
SELECT CONVERT(CHAR(10), [times],23) AS 日期,
CASE
WHEN CAST(CONVERT(CHAR, [times], 8) AS DATETIME)<''13:00'' THEN ''<13:00''
ELSE ''>=13:00''
END 类型,
COUNT(1) AS 次数
FROM [T_dinapp]
WHERE [times]>= '''+CONVERT(VARCHAR,@t1,23)+'''
AND [times]<'''+CONVERT(VARCHAR,DATEADD(dd, 1, @t2),23)+'''
GROUP BY CONVERT(CHAR(10), [times],23),
CASE
WHEN CAST(CONVERT(CHAR, [times], 8) AS DATETIME)<''13:00'' THEN ''<13:00''
ELSE ''>=13:00''
END
) AS T
GROUP BY 类型
WITH ROLLUP'
EXEC(@s)
/*
类型 2010-06-10 2010-06-11 2010-06-12
------- ----------- ----------- -----------
<13:00 2 1 0
>=13:00 2 2 1
合计 2 2 1(3 行受影响)
*/
IF OBJECT_ID('[T_dinapp]') IS NOT NULL
DROP TABLE [T_dinapp]
GO
CREATE TABLE [T_dinapp] ([times] [datetime])
INSERT INTO [T_dinapp]
SELECT '2010-6-10 9:00:00' UNION ALL
SELECT '2010-6-10 10:00:00' UNION ALL
SELECT '2008-6-4 23:00:00' UNION ALL
SELECT '2010-6-11 1:00:00' UNION ALL
SELECT '2010-6-11 15:00:00' UNION ALL
SELECT '2010-3-8 18:00:00' UNION ALL
SELECT '2010-6-10 19:00:00' UNION ALL
SELECT '2010-6-10 20:00:00' UNION ALL
SELECT '2010-6-11 19:00:00' UNION ALL
SELECT '2010-6-12 19:00:00' UNION ALL
SELECT '2008-4-5 15:00:00' UNION ALL
SELECT '2009-6-5 12:00:00' UNION ALL
SELECT '2010-4-8 12:00:00'--SELECT * FROM [T_dinapp]-->SQL查询如下:
DECLARE @t1 DATETIME,@t2 DATETIME
SELECT @t1='2010-06-05',@t2='2010-06-12'
DECLARE @s VARCHAR(MAX)
SELECT @s=ISNULL(@s,'')+',SUM(CASE [日期] WHEN '''+CONVERT(CHAR(10), [times],23)+''' THEN 次数 ELSE 0 END)['+CONVERT(CHAR(10), [times],23)+']'
FROM [T_dinapp]
WHERE [times]>= @t1
AND [times]<DATEADD(dd, 1, @t2)
GROUP BY CONVERT(CHAR(10), [times],23)
SET @s='SELECT ISNULL(类型,''合计'') 类型'+@s+'
FROM(
SELECT CONVERT(CHAR(10), [times],23) AS 日期,
CASE
WHEN CAST(CONVERT(CHAR, [times], 8) AS DATETIME)<''13:00'' THEN ''<13:00''
ELSE ''>=13:00''
END 类型,
COUNT(1) AS 次数
FROM [T_dinapp]
WHERE [times]>= '''+CONVERT(VARCHAR,@t1,23)+'''
AND [times]<'''+CONVERT(VARCHAR,DATEADD(dd, 1, @t2),23)+'''
GROUP BY CONVERT(CHAR(10), [times],23),
CASE
WHEN CAST(CONVERT(CHAR, [times], 8) AS DATETIME)<''13:00'' THEN ''<13:00''
ELSE ''>=13:00''
END
) AS T
GROUP BY 类型
WITH ROLLUP'
EXEC(@s)
/*
类型 2010-06-10 2010-06-11 2010-06-12
------- ----------- ----------- -----------
<13:00 2 1 0
>=13:00 2 2 1
合计 4 3 1(3 行受影响)
*/
IF OBJECT_ID('[T_dinapp]') IS NOT NULL
DROP TABLE [T_dinapp]
GO
CREATE TABLE [T_dinapp] ([times] [datetime])
INSERT INTO [T_dinapp]
SELECT '2010-6-10 9:00:00' UNION ALL
SELECT '2010-6-10 10:00:00' UNION ALL
SELECT '2008-6-4 23:00:00' UNION ALL
SELECT '2010-6-11 1:00:00' UNION ALL
SELECT '2010-6-11 15:00:00' UNION ALL
SELECT '2010-3-8 18:00:00' UNION ALL
SELECT '2010-6-10 19:00:00' UNION ALL
SELECT '2010-6-10 20:00:00' UNION ALL
SELECT '2010-6-11 19:00:00' UNION ALL
SELECT '2010-6-12 19:00:00' UNION ALL
SELECT '2008-4-5 15:00:00' UNION ALL
SELECT '2009-6-5 12:00:00' UNION ALL
SELECT '2010-4-8 12:00:00'--SELECT * FROM [T_dinapp]-->SQL查询如下:
DECLARE @t1 DATETIME,@t2 DATETIME
SELECT @t1='2010-06-10',@t2='2010-06-13'
DECLARE @s VARCHAR(MAX)
SELECT @s=ISNULL(@s,'')+',SUM(CASE [日期] WHEN '''+CONVERT(CHAR(10),@t1+number,23)+''' THEN 次数 ELSE 0 END)['+CONVERT(CHAR(10), @t1+number ,23)+']'
FROM master..spt_values
WHERE TYPE='p'
AND number<=DATEDIFF(dd, @t1, @t2)
SET @s='SELECT ISNULL(类型,''合计'') 类型'+@s+'
FROM(
SELECT CONVERT(CHAR(10), [times],23) AS 日期,
CASE
WHEN CAST(CONVERT(CHAR, [times], 8) AS DATETIME)<''13:00'' THEN ''<13:00''
ELSE ''>=13:00''
END 类型,
COUNT(1) AS 次数
FROM [T_dinapp]
WHERE [times]>= '''+CONVERT(VARCHAR,@t1,23)+'''
AND [times]<'''+CONVERT(VARCHAR,DATEADD(dd, 1, @t2),23)+'''
GROUP BY CONVERT(CHAR(10), [times],23),
CASE
WHEN CAST(CONVERT(CHAR, [times], 8) AS DATETIME)<''13:00'' THEN ''<13:00''
ELSE ''>=13:00''
END
) AS T
GROUP BY 类型
WITH ROLLUP'
EXEC(@s)
/*
类型 2010-06-10 2010-06-11 2010-06-12 2010-06-13
------- ----------- ----------- ----------- -----------
<13:00 2 1 0 0
>=13:00 2 2 1 0
合计 4 3 1 0(3 行受影响)
*/最后改一下,把没有记录的也显示出来。