现有数据库字段
Machine Factors UsedTime
VAD-01 项目1 15
VAD-01 项目2 23
VAD-02 项目1 14
VAD-03 项目2 12
VAD-04 项目1 53
VAD-04 项目2 45
现在想通过sql语句,查询出来的报表变成:
设备 项目1用时 项目2用时 总用时
VAD-01 15 23 38
VAD-02 14 0 14
VAD-03 0 12 12
VAD-04 53 45 98求解啊
Machine Factors UsedTime
VAD-01 项目1 15
VAD-01 项目2 23
VAD-02 项目1 14
VAD-03 项目2 12
VAD-04 项目1 53
VAD-04 项目2 45
现在想通过sql语句,查询出来的报表变成:
设备 项目1用时 项目2用时 总用时
VAD-01 15 23 38
VAD-02 14 0 14
VAD-03 0 12 12
VAD-04 53 45 98求解啊
--1、行换列
--> --> (Roy)生成測試數據
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85
Go
--2000方法:
动态:declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+' from Class group by [Student]')
生成静态:select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end)
from
Class
group by [Student]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([Machine] VARCHAR(6),[Factors] VARCHAR(5),[UsedTime] INT)
INSERT [tb]
SELECT 'VAD-01','项目1',15 UNION ALL
SELECT 'VAD-01','项目2',23 UNION ALL
SELECT 'VAD-02','项目1',14 UNION ALL
SELECT 'VAD-03','项目2',12 UNION ALL
SELECT 'VAD-04','项目1',53 UNION ALL
SELECT 'VAD-04','项目2',45
--------------开始查询--------------------------
select * from
(
SELECT *,sum([UsedTime]) OVER(PARTITION BY [Machine]) as 总用时 FROM [tb]
) t
PIVOT (MAX([UsedTime]) FOR [Factors] IN ([项目1],[项目2]))a
----------------结果----------------------------
/*
Machine 总用时 项目1 项目2
------- ----------- ----------- -----------
VAD-03 12 NULL 12
VAD-02 14 14 NULL
VAD-01 38 15 23
VAD-04 98 53 45(4 行受影响)
*/
数据库里面不光光有 Machine 做为主键
还有Date,Team,这三个字段我group后,中间查询的时候
提示:
消息 8120,级别 16,状态 1,第 5 行
选择列表中的列 'PRO_Efficiency.OperationMinute' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。
难道我差几十个地段,就要group by几十个吗?
select
[Machine],
[项目1用时]=max(case when [Factors]='项目1' then [UsedTime] else 0 end),
[项目2用时]=max(case when [Factors]='项目2' then [UsedTime] else 0 end),
[总用时]=sum([UsedTime])
from
tb
group by [Machine]
/*
Machine 项目1用时 项目2用时 总用时
------- ----------- ----------- -----------
VAD-01 15 23 38
VAD-02 14 0 14
VAD-03 0 12 12
VAD-04 53 45 98(4 行受影响)*/