27,579
社区成员
发帖
与我相关
我的任务
分享怎么把行变成列并且统计个数
if OBJECT_ID('A') IS NOT NULL
DROP TABLE [A]
CREATE TABLE [A](mtype varchar(10),worktime DATETIME)
insert into [a]
select 'SD01' ,'2017-02-28 08:35:00.000' UNION ALL
select 'SD01' ,'2017-02-28 08:50:00.000' UNION ALL
select 'SD01' ,'2017-02-28 14:40:00.000' UNION ALL
select 'VS02' ,'2017-02-28 15:00:00.000' UNION ALL
select 'UY02' ,'2017-02-28 20:00:00.000' UNION ALL
select 'VS02' ,'2017-02-28 21:00:00.000'
能够按时间统计从早上8:30到晚上21:30，每个小时统计一次,还有mtype内容是不定的，还有其他的类型，结果像这样
time SD01 VS02 UY02 VS02
8:30-9:30 2
9:30-10:30
....
14:30-15:30 1 1
...
19:30-20:30 1
20:30-21:30 1
...全文
请发表友善的回复…
发表回复
ajdkjalj 2018-10-25
- 打赏
- 举报
报错对象名#A无效，看起来有点费劲，不知道在哪改[/quote]
你把#A改成你自己的表名。
还有，你在#4提到了不同日期的会一起统计进去，但是你给的时间段本来就不体现日期，只体现了时间段，所以不同日期同一时间段的，当然是会统计在一起。如果你只统计某一天的，那就在下面的这段代码上加上where条件，只筛选某一天。
CTE_2
AS
(SELECT CONVERT(VARCHAR(5),A.START_TIME,108)+''-''+CONVERT(VARCHAR(5),A.END_TIME,108) AS PERIOD,
mtype
FROM CTE_1 A
LEFT JOIN #A B ON CONVERT(VARCHAR(10),B.WORKTIME,108) BETWEEN A.START_TIME AND A.END_TIME)
WHERE SELECT CONVERT(VARCHAR(10),worktime,23)='某天'
刚才那个写错了，多加了个SELECT，应该是下面的
CTE_2
AS
(SELECT CONVERT(VARCHAR(5),A.START_TIME,108)+''-''+CONVERT(VARCHAR(5),A.END_TIME,108) AS PERIOD,
mtype
FROM CTE_1 A
LEFT JOIN #A B ON CONVERT(VARCHAR(10),B.WORKTIME,108) BETWEEN A.START_TIME AND A.END_TIME)
WHERE CONVERT(VARCHAR(10),worktime,23)='某天'
加了不起任何作用呢
RINK_1 2018-10-25
- 打赏
- 举报
报错对象名#A无效，看起来有点费劲，不知道在哪改[/quote]
你把#A改成你自己的表名。
还有，你在#4提到了不同日期的会一起统计进去，但是你给的时间段本来就不体现日期，只体现了时间段，所以不同日期同一时间段的，当然是会统计在一起。如果你只统计某一天的，那就在下面的这段代码上加上where条件，只筛选某一天。
CTE_2
AS
(SELECT CONVERT(VARCHAR(5),A.START_TIME,108)+''-''+CONVERT(VARCHAR(5),A.END_TIME,108) AS PERIOD,
mtype
FROM CTE_1 A
LEFT JOIN #A B ON CONVERT(VARCHAR(10),B.WORKTIME,108) BETWEEN A.START_TIME AND A.END_TIME)
WHERE SELECT CONVERT(VARCHAR(10),worktime,23)='某天'
CTE_2
AS
(SELECT CONVERT(VARCHAR(5),A.START_TIME,108)+''-''+CONVERT(VARCHAR(5),A.END_TIME,108) AS PERIOD,
mtype
FROM CTE_1 A
LEFT JOIN #A B ON CONVERT(VARCHAR(10),B.WORKTIME,108) BETWEEN A.START_TIME AND A.END_TIME)
WHERE CONVERT(VARCHAR(10),worktime,23)='某天'
RINK_1 2018-10-25
- 打赏
- 举报
报错对象名#A无效，看起来有点费劲，不知道在哪改[/quote]
你把#A改成你自己的表名。
还有，你在#4提到了不同日期的会一起统计进去，但是你给的时间段本来就不体现日期，只体现了时间段，所以不同日期同一时间段的，当然是会统计在一起。如果你只统计某一天的，那就在下面的这段代码上加上where条件，只筛选某一天。
CTE_2
AS
(SELECT CONVERT(VARCHAR(5),A.START_TIME,108)+''-''+CONVERT(VARCHAR(5),A.END_TIME,108) AS PERIOD,
mtype
FROM CTE_1 A
LEFT JOIN #A B ON CONVERT(VARCHAR(10),B.WORKTIME,108) BETWEEN A.START_TIME AND A.END_TIME)
WHERE SELECT CONVERT(VARCHAR(10),worktime,23)='某天'
ajdkjalj 2018-10-25
- 打赏
- 举报
你的这个日期如果没在同一天统计的就不对了，我不知道你这个日期是怎么算的，就统计当前的日期吧，如果我给的记录中有个是29号的，也统计进来了
ajdkjalj 2018-10-25
- 打赏
- 举报
报错对象名#A无效，看起来有点费劲，不知道在哪改
RINK_1 2018-10-24
- 打赏
- 举报
DECLARE @START_TIME TIME
DECLARE @END_TIME TIME
DECLARE @SQL VARCHAR(MAX)
SET @START_TIME='08:30'
SET @END_TIME='21:30'
;WITH CTE_1
AS
(SELECT A.NUMBER,
CASE WHEN NUMBER<>0 THEN DATEADD(MINUTE,1,DATEADD(HOUR,NUMBER,START_TIME)) ELSE DATEADD(HOUR,NUMBER,START_TIME) END AS START_TIME,
DATEADD(HOUR,NUMBER+1,START_TIME) AS END_TIME
FROM MASTER.DBO.spt_values A
JOIN
(SELECT DATEDIFF(HOUR,@START_TIME,@END_TIME) AS QTY,@START_TIME AS START_TIME) AS B ON A.NUMBER<=B.QTY-1
WHERE TYPE='P'),
CTE_2
AS
(SELECT CONVERT(VARCHAR(5),A.START_TIME,108)+'-'+CONVERT(VARCHAR(5),A.END_TIME,108) AS PERIOD,
mtype
FROM CTE_1 A
LEFT JOIN #A B ON CONVERT(VARCHAR(10),B.WORKTIME,108) BETWEEN A.START_TIME AND A.END_TIME)
SELECT @SQL=ISNULL(@SQL+',','')+'SUM(CASE WHEN MTYPE='''+MTYPE+''' THEN 1 ELSE 0 END) AS '+MTYPE
FROM
(SELECT MTYPE FROM #A GROUP BY MTYPE) AS A
SET @SQL='
DECLARE @START_TIME TIME
DECLARE @END_TIME TIME
SET @START_TIME=''08:30''
SET @END_TIME=''21:30''
;WITH CTE_1
AS
(SELECT A.NUMBER,
CASE WHEN NUMBER<>0 THEN DATEADD(MINUTE,1,DATEADD(HOUR,NUMBER,START_TIME)) ELSE DATEADD(HOUR,NUMBER,START_TIME) END AS START_TIME,
DATEADD(HOUR,NUMBER+1,START_TIME) AS END_TIME
FROM MASTER.DBO.spt_values A
JOIN
(SELECT DATEDIFF(HOUR,@START_TIME,@END_TIME) AS QTY,@START_TIME AS START_TIME) AS B ON A.NUMBER<=B.QTY-1
WHERE TYPE=''P''),
CTE_2
AS
(SELECT CONVERT(VARCHAR(5),A.START_TIME,108)+''-''+CONVERT(VARCHAR(5),A.END_TIME,108) AS PERIOD,
mtype
FROM CTE_1 A
LEFT JOIN #A B ON CONVERT(VARCHAR(10),B.WORKTIME,108) BETWEEN A.START_TIME AND A.END_TIME)
SELECT PERIOD,'+@SQL+' FROM CTE_2 GROUP BY PERIOD'
EXEC(@SQL)
Hello World, 2018-10-23
- 打赏
- 举报
SELECT t.mtype ,
CONVERT(CHAR(5), DATEADD(HOUR, n.number, n.timeStart), 14) + '-'
+ CONVERT(CHAR(5), DATEADD(HOUR, n.number + 1, n.timeStart), 14) 时段 ,
COUNT(1) AS N
INTO #temp
FROM ( SELECT number ,
CONVERT(DATETIME, '2000-1-1 8:30') timeStart
FROM master..spt_values
WHERE type = 'p'
AND number < 13 ) n
LEFT JOIN a t ON CONVERT(CHAR(5), t.worktime, 14)
BETWEEN CONVERT( CHAR(5) , DATEADD(HOUR, n.number, n.timeStart), 14) AND CONVERT( CHAR(5) , DATEADD( HOUR , n.number + 1, n.timeStart), 14)
GROUP BY CONVERT(CHAR(5), DATEADD(HOUR, n.number, n.timeStart), 14) + '-'
+ CONVERT(CHAR(5), DATEADD(HOUR, n.number + 1, n.timeStart), 14),t.mtype
ORDER BY 时段
DECLARE @types VARCHAR(MAX),@sql NVARCHAR(MAX)
SELECT @types = isnull(@types +',','') + '['+ mtype +']' FROM (SELECT DISTINCT mtype from #temp WHERE mtype IS NOT NULL) t
SET @sql = 'SELECT 时段,' + @types + ' FROM #temp AS t PIVOT (SUM(N) FOR mtype IN (' + @types + ')) AS d order by 时段'
EXEC(@sql)
