34,590
社区成员
发帖
与我相关
我的任务
分享
我需要这样的一个查询,它是由多个查询联合起来的。
TB表结构是这样的:
typeid,time,value1,value2,value3,value4
1,1,1,2,3,4
1,2,1,2,3,4
1,4,1,2,3,4
2,1,A,B,C,D
2,4,A,B,C,D
2,5,A,B,C,D
3,1,E,F,G,H
3,2,I,J,K,L
3,3,M,N,O,P
……
查询的结果是以time为关系列,列出其中的value如:
SLEECT TIME,VALUE1 FROM TB WHERE TYPEID=1
联合
SELECT TIME,VALUE1 FROM TB WHERE TYPEID=2
联合
SELECT TIME,VALUE1 FROM TB WHERE TYPEID=3
得到的结果应是这样的:
TIME,VALUE1_1,VALUE1_2,VALUE1_3
1 ,1 ,A ,E
2 ,1 ,'' ,''
3 ,'' ,'' ,M
4 ,1 ,A ,''
5 ,'' ,A ,''
其中TIME的数据肯定是1-999直接的数据,表示的是多少小时。可以先创建一个TIME表,填充1-999的TIME值,然后让每个查询都连接TIME表,然后再联合。
declare @sql varchar(8000)
set @sql = 'select time '
select @sql = @sql + ' , max(case typeid when ''' + ltrim(typeid) + ''' then value1 else '''' end) [value' + ltrim(typeid) + ']'
from (select distinct typeid from tb) as a
set @sql = @sql + ' from tb where pici='x' group by time'
exec(@sql)
declare @sql varchar(8000)
set @sql = 'select time '
select @sql = @sql + ' , max(case typeid when ''' + ltrim(typeid) + ''' then value1 else '''' end) [value' + ltrim(typeid) + ']'
from (select distinct typeid from tb) as a
set @sql = @sql + ' from tb where pici='x' group by time'
exec(@sql)
select time,
max(case typeid when 1 then value1 end) as VALUE1_1,
max(case typeid when 2 then value1 end) as VALUE1_2,
max(case typeid when 3 then value1 end) as VALUE1_3
from TB
where pici(批号)='x'
group by time
另外建议提问时一次把需求全部提出。否则回答一个,紧跟着又是一个这种效果并不好。最好一开始就把问题完整的描述出来。--动态的
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-12-20 20:25:52
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([typeid] int,[time] int,[value1] varchar(1),[value2] varchar(1),[value3] varchar(1),[value4] varchar(1))
insert [tb]
select 1,1,'1','2','3','4' union all
select 1,2,'1','2','3','4' union all
select 1,4,'1','2','3','4' union all
select 2,1,'A','B','C','D' union all
select 2,4,'A','B','C','D' union all
select 2,5,'A','B','C','D' union all
select 3,1,'E','F','G','H' union all
select 3,2,'I','J','K','L' union all
select 3,3,'M','N','O','P'
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql = 'select time '
select @sql = @sql + ' , max(case typeid when ''' + ltrim(typeid) + ''' then value1 else '''' end) [value' + ltrim(typeid) + ']'
from (select distinct typeid from tb) as a
set @sql = @sql + ' from tb group by time'
exec(@sql)
----------------结果----------------------------
/* time value1 value2 value3
----------- ------ ------ ------
1 1 A E
2 1 I
3 M
4 1 A
5 A
(5 行受影响)
*/
typeid |time |value1|value2|value3|value4|pici(批号)
-----------|-----------|------|------|------|------|------
1| 1|1 |2 |3 |4 |x
1| 2|1 |2 |3 |4 |x
1| 4|1 |2 |3 |4 |z
2| 1|A |B |C |D |z
2| 4|A |B |C |D |x
2| 5|A |B |C |D |x
3| 1|E |F |G |H |z
3| 2|I |J |K |L |z
3| 3|M |N |O |P |x
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-12-20 20:25:52
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([typeid] int,[time] int,[value1] varchar(1),[value2] varchar(1),[value3] varchar(1),[value4] varchar(1))
insert [tb]
select 1,1,'1','2','3','4' union all
select 1,2,'1','2','3','4' union all
select 1,4,'1','2','3','4' union all
select 2,1,'A','B','C','D' union all
select 2,4,'A','B','C','D' union all
select 2,5,'A','B','C','D' union all
select 3,1,'E','F','G','H' union all
select 3,2,'I','J','K','L' union all
select 3,3,'M','N','O','P'
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql = 'select time '
select @sql = @sql + ' , max(case typeid when ''' + ltrim(typeid) + ''' then value1 else '''' end) [' + ltrim(typeid) + ']'
from (select distinct typeid from tb) as a
set @sql = @sql + ' from tb group by time'
exec(@sql)
----------------结果----------------------------
/* time 1 2 3
----------- ---- ---- ----
1 1 A E
2 1 I
3 M
4 1 A
5 A
(5 行受影响)
*/
1> select * from tb;
2> go
typeid |time |value1|value2|value3|value4
-----------|-----------|------|------|------|------
1| 1|1 |2 |3 |4
1| 2|1 |2 |3 |4
1| 4|1 |2 |3 |4
2| 1|A |B |C |D
2| 4|A |B |C |D
2| 5|A |B |C |D
3| 1|E |F |G |H
3| 2|I |J |K |L
3| 3|M |N |O |P
(9 rows affected)
1> select time,
2> max(case typeid when 1 then value1 end) as VALUE1_1,
3> max(case typeid when 2 then value1 end) as VALUE1_2,
4> max(case typeid when 3 then value1 end) as VALUE1_3
5> from TB
6> group by time
7> go
time |VALUE1_1|VALUE1_2|VALUE1_3
-----------|--------|--------|--------
1|1 |A |E
2|1 |NULL |I
3|NULL |NULL |M
4|1 |A |NULL
5|NULL |A |NULL
Warning: Null value is eliminated by an aggregate or other SET operation.
1>
select time,
max(case typeid when 1 then value1 end) as VALUE1_1,
max(case typeid when 2 then value1 end) as VALUE1_2,
max(case typeid when 3 then value1 end) as VALUE1_3
from TB
group by time