sql 查表问题？
本帖最后由 studyarea 于 2013-08-09 16:33:52 编辑 假如有表A（名称，最大值，最小值，平均值，时间）
名称 最大值 最小值 平均值 时间
电流 5 2 3.5 2013-08-01
电压 230 180 205 2013-08-01
温度 39 27 33 2013-08-01
电流 8 1 4.5 2013-08-02
电压 220 180 200 2013-08-02
温度 36 24 30 2013-08-02
查询结果为（名称个数自定）
时间 电流最大值 电流最小值 电流平均值 电压最大值 电压最小值 电压平均值
2013-08-01 5 2 3.5 230 180 205
2013-08-02 8 1 4.5 220 180 200
sql语句怎么写？各位大神帮帮忙
[解决办法]
create table tableA(Name nvarchar(10),MaxNum int,MinNum int,AVE float, [Time] dateTime)
insert into tableA values('电流',5,2,3.5,'2013-08-01')
insert into tableA values('电压',230,180,205,'2013-08-01')
insert into tableA values('温度',39,27,33,'2013-08-01')
insert into tableA values('电流',8,1,4.5,'2013-08-02')
insert into tableA values('电压',220,180,200,'2013-08-02')
insert into tableA values('温度',36,24,30,'2013-08-02')
select * from tableA
select a.[Time] as 时间,
电流最大值=(select MaxNum from tableA where Name='电流' and [Time]=a.[Time]),
电流最小值=(select MinNum from tableA where Name='电流' and [Time]=a.[Time]),
电流平均值=(select AVE from tableA where Name='电流' and [Time]=a.[Time]),
电压最大值=(select MaxNum from tableA where Name='电压' and [Time]=a.[Time]),
电压最小值=(select MinNum from tableA where Name='电压' and [Time]=a.[Time]),
电压平均值=(select AVE from tableA where Name='电压' and [Time]=a.[Time])
from tableA a
group by [Time]
/*
2013-08-01 00:00:00.000523.5230180205
2013-08-02 00:00:00.000814.5220180200
*/
create table #tb(名称 varchar(10),最大值 numeric(12,2),
最小值 numeric(12,2),平均值 numeric(12,2), 时间 datetime)
insert into #tb
select '电流',5,2,3.5,'2013-08-01'
union all select '电压',230,180,205,'2013-08-01'
union all select '温度',39,27,33,'2013-08-01'
union all select '电流',8,1,4.5,'2013-08-02'
union all select '电压',220,180,200,'2013-08-02'
union all select '温度',36,24,30,'2013-08-02'
select * from #tb
select 时间,
电流最大值=MAX(case when 名称='电流' then 最大值 else 0 end),
电流最小值=MAX(case when 名称='电流' then 最小值 else 0 end),
电流平均值=MAX(case when 名称='电流' then 平均值 else 0 end),
电压最大值=MAX(case when 名称='电压' then 最大值 else 0 end),
电压最小值=MAX(case when 名称='电压' then 最小值 else 0 end),
电压平均值=MAX(case when 名称='电压' then 平均值 else 0 end)
from #tb
group by 时间
/*
2013-08-01 00:00:00.0005.002.003.50230.00180.00205.00
2013-08-02 00:00:00.0008.001.004.50220.00180.00200.00
*/
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-08-09 17:22:40
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
--Jun 10 2013 20:09:10
--Copyright (c) Microsoft Corporation
--Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([名称] varchar(4),[最大值] int,[最小值] int,[平均值] numeric(4,1),[时间] datetime)
insert [A]
select '电流',5,2,3.5,'2013-08-01' union all
select '电压',230,180,205,'2013-08-01' union all
select '温度',39,27,33,'2013-08-01' union all
select '电流',8,1,4.5,'2013-08-02' union all
select '电压',220,180,200,'2013-08-02' union all
select '温度',36,24,30,'2013-08-02'
--------------开始查询--------------------------
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([名称]+'最大值')+'=max(case when [名称]='+quotename([名称],'''')+' then [最大值] else 0 end)'
+','+quotename([名称]+'最小值')+'=max(case when [名称]='+quotename([名称],'''')+' then [最小值] else 0 end)'
+','+quotename([名称]+'平均值')+'=max(case when [名称]='+quotename([名称],'''')+' then [平均值] else 0 end)'
from [A] group by [名称]
exec('select [时间]'+@s+' from [A] group by [时间]')
----------------结果----------------------------
/*
时间 电流最大值 电流最小值 电流平均值 电压最大值 电压最小值 电压平均值 温度最大值 温度最小值 温度平均值
----------------------- ----------- ----------- --------------------------------------- ----------- ----------- --------------------------------------- ----------- ----------- ---------------------------------------
2013-08-01 00:00:00.000 5 2 3.5 230 180 205.0 39 27 33.0
2013-08-02 00:00:00.000 8 1 4.5 220 180 200.0 36 24 30.0
*/
