ID mmTime NO
1 2012-1-11 12
2 2012-1-11 4
3 2012-1-15 27
4 2012-1-16 8
如何如下效果: 查询结果按天分组如下
1 2 3 .... 11 12 13 14 15 16 ...31 <------代表日期
16 27 8 <------代表数量(每天的数量相加)
13 个解决方案
#1
查pivot
#2
select
sum(case day(mmTime) when 1 then NO end) as [1],
sum(case day(mmTime) when 2 then NO end) as [2],
sum(case day(mmTime) when 3 then NO end) as [3],
sum(case day(mmTime) when 4 then NO end) as [4],
sum(case day(mmTime) when 5 then NO end) as [5],
sum(case day(mmTime) when 6 then NO end) as [6],
sum(case day(mmTime) when 7 then NO end) as [7],
sum(case day(mmTime) when 8 then NO end) as [8],
sum(case day(mmTime) when 9 then NO end) as [9],
sum(case day(mmTime) when 10 then NO end) as [10],
sum(case day(mmTime) when 11 then NO end) as [11],
sum(case day(mmTime) when 12 then NO end) as [12],
sum(case day(mmTime) when 13 then NO end) as [13],
sum(case day(mmTime) when 14 then NO end) as [14],
sum(case day(mmTime) when 15 then NO end) as [15],
sum(case day(mmTime) when 16 then NO end) as [16],
sum(case day(mmTime) when 17 then NO end) as [17],
sum(case day(mmTime) when 18 then NO end) as [18],
sum(case day(mmTime) when 19 then NO end) as [19],
sum(case day(mmTime) when 20 then NO end) as [20],
sum(case day(mmTime) when 21 then NO end) as [21],
sum(case day(mmTime) when 22 then NO end) as [22],
sum(case day(mmTime) when 23 then NO end) as [23],
sum(case day(mmTime) when 24 then NO end) as [24],
sum(case day(mmTime) when 25 then NO end) as [25],
sum(case day(mmTime) when 26 then NO end) as [26],
sum(case day(mmTime) when 27 then NO end) as [27],
sum(case day(mmTime) when 28 then NO end) as [28],
sum(case day(mmTime) when 29 then NO end) as [29],
sum(case day(mmTime) when 30 then NO end) as [30],
sum(case day(mmTime) when 31 then NO end) as [31]
from SDD
#3
SELECT CONVERT(CHAR(10),date,120),COUNT FROM TBNAME
GROUP BY CONVERT(CHAR(10)date,120)
#4
select ID,[1] as [1],[2] as [2]....[31] as [31] from(select ID,DATEPART(DD,mmtime) as d,no from sdd where xxx) p
pivot (sum(no) for d in ([1]...[31]))
#5
SELECT
#6
如果其中一天没有数据是不是不做显示,如果这样的话,你可以直接按日期进行分组就可以了,截取下日期
#7
ID mmTime NO UType
1 2012-1-11 12 橘子
2 2012-1-11 4 橘子
3 2012-1-15 27 橘子
4 2012-1-16 8 橘子
5 2012-1-16 8 香蕉
如何如下效果: 查询结果按天分组如下
name 1 2 3 .... 11 12 13 14 15 16 ...31 <------代表日期
橘子 16 27 8 <------代表数量(每天的数量相加)
香蕉 8
1 2012-1-11 12 橘子
2 2012-1-11 4 橘子
3 2012-1-15 27 橘子
4 2012-1-16 8 橘子
5 2012-1-16 8 香蕉
如何如下效果: 查询结果按天分组如下
name 1 2 3 .... 11 12 13 14 15 16 ...31 <------代表日期
橘子 16 27 8 <------代表数量(每天的数量相加)
香蕉 8
#8
ID mmTime NO UType
1 2012-1-11 12 橘子
2 2012-1-11 4 橘子
3 2012-1-15 27 橘子
4 2012-1-16 8 橘子
5 2012-1-16 8 香蕉
如何如下效果: 查询结果按天分组如下
name 1 2 3 .... 11 12 13 14 15 16 ...31 <------代表日期
橘子 16 27 8 <------代表数量(每天的数量相加)
香蕉 8
1 2012-1-11 12 橘子
2 2012-1-11 4 橘子
3 2012-1-15 27 橘子
4 2012-1-16 8 橘子
5 2012-1-16 8 香蕉
如何如下效果: 查询结果按天分组如下
name 1 2 3 .... 11 12 13 14 15 16 ...31 <------代表日期
橘子 16 27 8 <------代表数量(每天的数量相加)
香蕉 8
#9
鲁迅先森 这样怎么查
#10
SELECT * INTO #TRanen FROM (
SELECT 1 AS ID, CONVERT(DATETIME,'2012-1-11') AS mmTime,12 as [NO] UNION ALL
SELECT 2, '2012-1-11',4 UNION ALL
SELECT 3, '2012-1-15',27 UNION ALL
SELECT 4, '2012-1-16',8
) T
DECLARE @SQL VARCHAR(500)
SELECT @SQL = ISNULL(@SQL + ',' ,'') + '[' + CONVERT(NVARCHAR(10),MMTIME,23)+']'
FROM #TRanen GROUP BY CONVERT(NVARCHAR(10),MMTIME,23)
EXEC('
SELECT * FROM #TRanen A PIVOT (SUM([NO]) FOR MMTIME IN('+@SQL+')) B
')
DROP TABLE #TRanen
#11
select
UType as name,
sum(case day(mmTime) when 1 then NO end) as [1],
sum(case day(mmTime) when 2 then NO end) as [2],
sum(case day(mmTime) when 3 then NO end) as [3],
sum(case day(mmTime) when 4 then NO end) as [4],
sum(case day(mmTime) when 5 then NO end) as [5],
sum(case day(mmTime) when 6 then NO end) as [6],
sum(case day(mmTime) when 7 then NO end) as [7],
sum(case day(mmTime) when 8 then NO end) as [8],
sum(case day(mmTime) when 9 then NO end) as [9],
sum(case day(mmTime) when 10 then NO end) as [10],
sum(case day(mmTime) when 11 then NO end) as [11],
sum(case day(mmTime) when 12 then NO end) as [12],
sum(case day(mmTime) when 13 then NO end) as [13],
sum(case day(mmTime) when 14 then NO end) as [14],
sum(case day(mmTime) when 15 then NO end) as [15],
sum(case day(mmTime) when 16 then NO end) as [16],
sum(case day(mmTime) when 17 then NO end) as [17],
sum(case day(mmTime) when 18 then NO end) as [18],
sum(case day(mmTime) when 19 then NO end) as [19],
sum(case day(mmTime) when 20 then NO end) as [20],
sum(case day(mmTime) when 21 then NO end) as [21],
sum(case day(mmTime) when 22 then NO end) as [22],
sum(case day(mmTime) when 23 then NO end) as [23],
sum(case day(mmTime) when 24 then NO end) as [24],
sum(case day(mmTime) when 25 then NO end) as [25],
sum(case day(mmTime) when 26 then NO end) as [26],
sum(case day(mmTime) when 27 then NO end) as [27],
sum(case day(mmTime) when 28 then NO end) as [28],
sum(case day(mmTime) when 29 then NO end) as [29],
sum(case day(mmTime) when 30 then NO end) as [30],
sum(case day(mmTime) when 31 then NO end) as [31]
from SDD
group by UType
#12
树哥,辛苦了.
#13
IF OBJECT_ID(N'tempdb..#TRanen') IS NOT NULL
DROP TABLE #TRanen
GO
SELECT * INTO #TRanen FROM (
SELECT 1 AS ID, CONVERT(DATETIME,'2012-1-11') AS mmTime,12 as [NO] UNION ALL
SELECT 2, '2012-1-11',4 UNION ALL
SELECT 3, '2012-1-15',27 UNION ALL
SELECT 4, '2012-1-16',8
) T
GO
DECLARE @SQL VARCHAR(500)
SELECT @SQL = ISNULL(@SQL + ',' ,'') + QUOTENAME(CONVERT(VARCHAR(10),MMTIME,120))
FROM (SELECT DISTINCT MMTIME = CONVERT(NVARCHAR(10),MMTIME,120) FROM #TRanen) AA
EXEC('
SELECT ID,'+@SQL+' FROM #TRanen A PIVOT (SUM([NO]) FOR MMTIME IN('+@SQL+')) B
')
DROP TABLE #TRanen
/*
ID 2012-01-11 2012-01-15 2012-01-16
1 12 NULL NULL
2 4 NULL NULL
3 NULL 27 NULL
4 NULL NULL 8
*/
#1
查pivot
#2
select
sum(case day(mmTime) when 1 then NO end) as [1],
sum(case day(mmTime) when 2 then NO end) as [2],
sum(case day(mmTime) when 3 then NO end) as [3],
sum(case day(mmTime) when 4 then NO end) as [4],
sum(case day(mmTime) when 5 then NO end) as [5],
sum(case day(mmTime) when 6 then NO end) as [6],
sum(case day(mmTime) when 7 then NO end) as [7],
sum(case day(mmTime) when 8 then NO end) as [8],
sum(case day(mmTime) when 9 then NO end) as [9],
sum(case day(mmTime) when 10 then NO end) as [10],
sum(case day(mmTime) when 11 then NO end) as [11],
sum(case day(mmTime) when 12 then NO end) as [12],
sum(case day(mmTime) when 13 then NO end) as [13],
sum(case day(mmTime) when 14 then NO end) as [14],
sum(case day(mmTime) when 15 then NO end) as [15],
sum(case day(mmTime) when 16 then NO end) as [16],
sum(case day(mmTime) when 17 then NO end) as [17],
sum(case day(mmTime) when 18 then NO end) as [18],
sum(case day(mmTime) when 19 then NO end) as [19],
sum(case day(mmTime) when 20 then NO end) as [20],
sum(case day(mmTime) when 21 then NO end) as [21],
sum(case day(mmTime) when 22 then NO end) as [22],
sum(case day(mmTime) when 23 then NO end) as [23],
sum(case day(mmTime) when 24 then NO end) as [24],
sum(case day(mmTime) when 25 then NO end) as [25],
sum(case day(mmTime) when 26 then NO end) as [26],
sum(case day(mmTime) when 27 then NO end) as [27],
sum(case day(mmTime) when 28 then NO end) as [28],
sum(case day(mmTime) when 29 then NO end) as [29],
sum(case day(mmTime) when 30 then NO end) as [30],
sum(case day(mmTime) when 31 then NO end) as [31]
from SDD
#3
SELECT CONVERT(CHAR(10),date,120),COUNT FROM TBNAME
GROUP BY CONVERT(CHAR(10)date,120)
#4
select ID,[1] as [1],[2] as [2]....[31] as [31] from(select ID,DATEPART(DD,mmtime) as d,no from sdd where xxx) p
pivot (sum(no) for d in ([1]...[31]))
#5
SELECT
#6
如果其中一天没有数据是不是不做显示,如果这样的话,你可以直接按日期进行分组就可以了,截取下日期
#7
ID mmTime NO UType
1 2012-1-11 12 橘子
2 2012-1-11 4 橘子
3 2012-1-15 27 橘子
4 2012-1-16 8 橘子
5 2012-1-16 8 香蕉
如何如下效果: 查询结果按天分组如下
name 1 2 3 .... 11 12 13 14 15 16 ...31 <------代表日期
橘子 16 27 8 <------代表数量(每天的数量相加)
香蕉 8
1 2012-1-11 12 橘子
2 2012-1-11 4 橘子
3 2012-1-15 27 橘子
4 2012-1-16 8 橘子
5 2012-1-16 8 香蕉
如何如下效果: 查询结果按天分组如下
name 1 2 3 .... 11 12 13 14 15 16 ...31 <------代表日期
橘子 16 27 8 <------代表数量(每天的数量相加)
香蕉 8
#8
ID mmTime NO UType
1 2012-1-11 12 橘子
2 2012-1-11 4 橘子
3 2012-1-15 27 橘子
4 2012-1-16 8 橘子
5 2012-1-16 8 香蕉
如何如下效果: 查询结果按天分组如下
name 1 2 3 .... 11 12 13 14 15 16 ...31 <------代表日期
橘子 16 27 8 <------代表数量(每天的数量相加)
香蕉 8
1 2012-1-11 12 橘子
2 2012-1-11 4 橘子
3 2012-1-15 27 橘子
4 2012-1-16 8 橘子
5 2012-1-16 8 香蕉
如何如下效果: 查询结果按天分组如下
name 1 2 3 .... 11 12 13 14 15 16 ...31 <------代表日期
橘子 16 27 8 <------代表数量(每天的数量相加)
香蕉 8
#9
鲁迅先森 这样怎么查
#10
SELECT * INTO #TRanen FROM (
SELECT 1 AS ID, CONVERT(DATETIME,'2012-1-11') AS mmTime,12 as [NO] UNION ALL
SELECT 2, '2012-1-11',4 UNION ALL
SELECT 3, '2012-1-15',27 UNION ALL
SELECT 4, '2012-1-16',8
) T
DECLARE @SQL VARCHAR(500)
SELECT @SQL = ISNULL(@SQL + ',' ,'') + '[' + CONVERT(NVARCHAR(10),MMTIME,23)+']'
FROM #TRanen GROUP BY CONVERT(NVARCHAR(10),MMTIME,23)
EXEC('
SELECT * FROM #TRanen A PIVOT (SUM([NO]) FOR MMTIME IN('+@SQL+')) B
')
DROP TABLE #TRanen
#11
select
UType as name,
sum(case day(mmTime) when 1 then NO end) as [1],
sum(case day(mmTime) when 2 then NO end) as [2],
sum(case day(mmTime) when 3 then NO end) as [3],
sum(case day(mmTime) when 4 then NO end) as [4],
sum(case day(mmTime) when 5 then NO end) as [5],
sum(case day(mmTime) when 6 then NO end) as [6],
sum(case day(mmTime) when 7 then NO end) as [7],
sum(case day(mmTime) when 8 then NO end) as [8],
sum(case day(mmTime) when 9 then NO end) as [9],
sum(case day(mmTime) when 10 then NO end) as [10],
sum(case day(mmTime) when 11 then NO end) as [11],
sum(case day(mmTime) when 12 then NO end) as [12],
sum(case day(mmTime) when 13 then NO end) as [13],
sum(case day(mmTime) when 14 then NO end) as [14],
sum(case day(mmTime) when 15 then NO end) as [15],
sum(case day(mmTime) when 16 then NO end) as [16],
sum(case day(mmTime) when 17 then NO end) as [17],
sum(case day(mmTime) when 18 then NO end) as [18],
sum(case day(mmTime) when 19 then NO end) as [19],
sum(case day(mmTime) when 20 then NO end) as [20],
sum(case day(mmTime) when 21 then NO end) as [21],
sum(case day(mmTime) when 22 then NO end) as [22],
sum(case day(mmTime) when 23 then NO end) as [23],
sum(case day(mmTime) when 24 then NO end) as [24],
sum(case day(mmTime) when 25 then NO end) as [25],
sum(case day(mmTime) when 26 then NO end) as [26],
sum(case day(mmTime) when 27 then NO end) as [27],
sum(case day(mmTime) when 28 then NO end) as [28],
sum(case day(mmTime) when 29 then NO end) as [29],
sum(case day(mmTime) when 30 then NO end) as [30],
sum(case day(mmTime) when 31 then NO end) as [31]
from SDD
group by UType
#12
树哥,辛苦了.
#13
IF OBJECT_ID(N'tempdb..#TRanen') IS NOT NULL
DROP TABLE #TRanen
GO
SELECT * INTO #TRanen FROM (
SELECT 1 AS ID, CONVERT(DATETIME,'2012-1-11') AS mmTime,12 as [NO] UNION ALL
SELECT 2, '2012-1-11',4 UNION ALL
SELECT 3, '2012-1-15',27 UNION ALL
SELECT 4, '2012-1-16',8
) T
GO
DECLARE @SQL VARCHAR(500)
SELECT @SQL = ISNULL(@SQL + ',' ,'') + QUOTENAME(CONVERT(VARCHAR(10),MMTIME,120))
FROM (SELECT DISTINCT MMTIME = CONVERT(NVARCHAR(10),MMTIME,120) FROM #TRanen) AA
EXEC('
SELECT ID,'+@SQL+' FROM #TRanen A PIVOT (SUM([NO]) FOR MMTIME IN('+@SQL+')) B
')
DROP TABLE #TRanen
/*
ID 2012-01-11 2012-01-15 2012-01-16
1 12 NULL NULL
2 4 NULL NULL
3 NULL 27 NULL
4 NULL NULL 8
*/