求一SQL语句

[浩子]

现有两张表T_A和T_B,如下
T_A
LogTime FValue
2007-4-18 9:30:00 1.1
2007-4-19 9:30:00 1.3

T_B
LogTime FCode FValue
2007-4-18 9:30:00 F1 10.15
2007-4-18 9:30:00 F2 10.25
2007-4-19 9:30:00 F1 10.35
2007-4-19 9:30:00 F2 10.45

我想通过一条SQL语句变成：
T_C
LogTime F0 F1 F2
2007-4-18 9:30:00 1.1 10.15 10.35
2007-4-18 9:30:00 1.3 10.25 10.45

也即将T_A中的原来数据都直接对应到T_C中的F0,谢谢！

[青锋-SS]

看的还晕了.

[青锋-SS]

T_A和T_B是怎么对应的?

[gahade]

create table T_A(LogTime datetime,FValue numeric(18,2))
insert into T_A
select '2007-4-18 9:30:00',1.1
union all select '2007-4-19 9:30:00',1.3','
create table T_B(LogTime datetime,FCode varchar(10),FValue numeric(18,2))
insert into T_B
select '2007-4-18 9:30:00','F1',10.15
union all select '2007-4-18 9:30:00','F2',10.25
union all select '2007-4-19 9:30:00','F1',10.35
union all select '2007-4-19 9:30:00','F2',10.45

select a.LogTime,
FValue as 'F0',
b.F1,
b.F2
from T_A a
left join (select LogTime,min(FValue) as F1,max(FValue) as F2 from T_B group by LogTime)b on a.LogTime=b.LogTime

/*
LogTime F0 F1 F2
------------------------------------------------------ -------------------- -------------------- --------------------
2007-04-18 09:30:00.000 1.10 10.15 10.25
2007-04-19 09:30:00.000 1.30 10.35 10.45

（所影响的行数为 2 行）
*/

[青锋-SS]

T_C的数据也不好琢磨,好像乱套了.

[浩子]

是麻烦了一些，我原来用两个数据集去操作，慢死了，没办法，我对ＳＱＬ又不精通，只好求各位高手了，
T_A 和T_B没有直接对应关系，唯一的就是时间是关联的，以T_A为主，将所有T_B中在T_A中有的时间的数据都转换到T_C,麻烦了

[中国风]

T_B取大的用left join T_B

[浩子]

谢谢，gahade(与君共勉) ，我忘了说一个重点了，T_B中的FCode并不固定，可能还有F3,F4，刚才我只是举例，没有考虑到这点，不好意思，

[leo_lesley]

CREATE TABLE T_A(LogTime datetime,FValue numeric(9,2))
insert T_A
select '2007-4-18 9:30:00', 1.1 union all
select '2007-4-19 9:30:00', 1.3

CREATE TABLE T_B(LogTime datetime,FCode varchar(10),FValue numeric(9,2))
INSERT T_B SELECT '2007-4-18 9:30:00','F1',10.15
UNION ALL SELECT '2007-4-18 9:30:00','F2',10.25
UNION ALL SELECT '2007-4-19 9:30:00','F1',10.35
UNION ALL SELECT '2007-4-19 9:30:00','F2',10.45

select b.LogTime,F0=a.FValue,b.FCode,b.FValue
from T_A a,T_B b
where a.LogTime=b.LogTime

--查询处理
DECLARE @s nvarchar(4000)
--交叉报表处理代码头
SET @s='SELECT B.LogTime,F0=A.FValue'
--生成列记录水平显示的处理代码拼接(处理Item列)
SELECT @s=@s
+','+QUOTENAME(FCode)
+N'=SUM(CASE b.FCode WHEN '+QUOTENAME(FCode,N'''')
+N' THEN b.FValue END)'
FROM T_B
GROUP BY FCode

--拼接交叉报表处理尾部,并且执行拼接后的动态SQL语句

EXEC(@s+N'
FROM T_B B,T_A A
WHERE A.LogTime=B.LogTime
GROUP BY B.LogTime,A.FValue')

drop table T_B
drop table T_A

[子陌红尘]

create table T_A(LogTime datetime,FValue numeric(3,1))
insert into T_A select '2007-4-18 9:30:00',1.1
insert into T_A select '2007-4-19 9:30:00',1.3

create table T_B(LogTime datetime,FCode varchar(4),Value numeric(5,2))
insert into T_B select '2007-4-18 9:30:00','F1',10.15
insert into T_B select '2007-4-18 9:30:00','F2',10.25
insert into T_B select '2007-4-19 9:30:00','F1',10.35
insert into T_B select '2007-4-19 9:30:00','F2',10.45
go

declare @sql varchar(2000)
set @sql=''

select @sql=@sql+',['+FCode+']=max(case b.FCode when '''+FCode+''' then b.value end)'
from T_B group by FCode

set @sql= 'select a.LogTime,a.FValue as F0'+@sql
+' from T_A a,T_B b where a.LogTime=b.LogTime group by a.LogTime,a.FValue'

exec(@sql)
/*
LogTime F0 F1 F2
-------------------------- ----- ------- -------
2007-04-18 09:30:00.000 1.1 10.15 10.25
2007-04-19 09:30:00.000 1.3 10.35 10.45
*/
go

drop table T_A,T_B
go

[qw_study]

CREATE FUNCTION dbo.ISOweek (@LogTime datetime,@FCode char(2))
RETURNS decimal
as
begin
DECLARE @re decimal
if(@FCode='F1' )
begin
SELECT @re=FValue FROM T_B WHERE LogTime=@LogTime and FCode='F1'
end
else
begin
SELECT @re=FValue FROM T_B WHERE LogTime=@LogTime and FCode='F2'
end

return @re
end
go

select
LogTime, FValue AS F0 ,GetF(LogTime,'F1') as F1,GetF(LogTime,'F2') as F2
from T_A

[qw_study]

CREATE FUNCTION GetF(@LogTime datetime,@FCode char(2))
RETURNS decimal
as
begin
DECLARE @re decimal
if(@FCode='F1' )
begin
SELECT @re=FValue FROM T_B WHERE LogTime=@LogTime and FCode='F1'
end
else
begin
SELECT @re=FValue FROM T_B WHERE LogTime=@LogTime and FCode='F2'
end

return @re
end
go

select
LogTime, FValue AS F0 ,GetF(LogTime,'F1') as F1,GetF(LogTime,'F2') as F2
from T_A

[中国风]

2007-04-19 09:30:00.000 1.3 10.35 10.45 与楼主的没相符
2007-4-18 9:30:00 1.3 10.25 10.45

[中国风]

create table t_a(LogTime datetime,FValue numeric(15,2))
insert T_A select '2007-4-18 9:30:00', 1.1
insert T_A select '2007-4-19 9:30:00', 1.3
go
create table T_B (LogTime datetime, FCode varchar(2),FValue numeric(15,2))
insert T_B
select '2007-4-18 9:30:00','F1',10.15
union all select '2007-4-18 9:30:00','F2',10.25
union all select '2007-4-19 9:30:00','F1',10.35
union all select '2007-4-19 9:30:00','F2',10.45

go
select LogTime=min(a1.LogTime),a.FValue,id=identity(int,1,1)
into #a
from t_a a cross join t_a a1
group by a.FValue


select b.LogTime,b.FCode,FValue=max(b1.FValue),id=1
into #b
from t_b b join t_b b1 on b.fcode=b1.fcode
group by b.LogTime,b.FCode,b.FValue order by b.LogTime

declare @date datetime,@i int
update #b
set @i=case when logtime=@date then @i+1 else 1 end,id=@i,@date=logtime

select *
from #a a join #b b on a.id=b.id and a.logtime=b.logtime

2007-04-18 09:30:00.000 1.10 1 2007-04-18 09:30:00.000 F1 10.35 1
2007-04-18 09:30:00.000 1.30 2 2007-04-18 09:30:00.000 F2 10.45 2

[中国风]

只有通过临时表处理多对多关系