一个联合查询的问题
表1 t1： 管子类型， 管子数量
AA 3
A5 5
表2 t2: 管子类型 每周差值 时间
AA 1 2012-11-11
AA 2 2012-11-18
A5 1 2012-11-12
A5 3 2012-11-19
要得到这样一张表，含有 管子类型，管子数量，最近一周差值的表
表3 t3 管子类型 管子数量 最近差值 时间
AA 3 2 2012-11-18
A5 1 3 2012-11-19
[最优解释]
if(object_id('t1') is not null) drop table t1
go
create table t1
(
[管子类型] varchar(10),
[管子数量] int
)
go
insert into t1
select 'AA',3 union all
select 'A5',5
go
if(object_id('t2')is not null)drop table t2
go
create table t2
(
[管子类型] varchar(10),
[每周差值] int,
[时间] datetime
)
go
insert into t2
select 'AA',1,'2012-11-11' union all
select 'AA',2,'2012-11-18' union all
select 'A5',1,'2012-11-12' union all
select 'A5',3,'2012-11-19'
go
select t1.[管子类型],t1.[管子数量]-t2.[每周差值]as[管子数量],t2.[每周差值]as [最近差值],t2.[时间]
from t1 inner join (select [管子类型], [每周差值],[时间]
from t2 where exists(select 1 from t2 as t
where t2.[管子类型]=[管子类型] and t2.[时间]>[时间])) as t2 on t2.[管子类型]=t1.[管子类型]
/*
管子类型 管子数量 最近差值 时间
---------- ----------- ----------- -----------------------
AA 1 2 2012-11-18 00:00:00.000
A5 2 3 2012-11-19 00:00:00.000
(2 行受影响)
*/
[其他解释]
create table t1 (管子类型 varchar(10),管子数量 int)
create table t2 (管子类型 varchar(10),每周差值 int,时间 datetime)
insert into t1 select 'AA',3 union all select 'A5',5
insert into t2
select 'AA',1,'2012-11-11' union all
select 'AA',2,'2012-11-18' union all
select 'A5',1,'2012-11-12' union all
select 'A5',3,'2012-11-19'
select * from t1 AS A,t2 AS B where A.管子类型 =B.管子类型 and not exists(select 1 from t2 AS C where C.管子类型 =B.管子类型 and C.时间 >B.时间 )
[其他解释]
CREATE TABLE t1
(
style VARCHAR(2),
total INT
)
INSERT INTO t1
SELECT 'AA',3 UNION ALL
SELECT 'A5',5
CREATE TABLE t2
(
style VARCHAR(2),
diff INT,
diffdate DATE
)
INSERT INTO t2
SELECT 'AA',1,'2012-11-11' UNION ALL
SELECT 'AA',2,'2012-11-18' UNION ALL
SELECT 'A5',1,'2012-11-12' UNION ALL
SELECT 'A5',3,'2012-11-19'
SELECT * FROM t1
SELECT * FROM t2
SELECTA.style AS [管子类型],
A.total AS [管子数量],
A1.diff AS [最近差值],
A1.diffdate AS [时间]
FROMt1 AS A WITH(NOLOCK) INNER JOIN
(
SELECT*
FROMt2 AS B WITH(NOLOCK)
WHERENOT EXISTS (
SELECT1
FROMt2 AS C WITH(NOLOCK)
WHEREB.style=C.style
ANDC.diffdate>b.diffdate
)
) AS A1 ON A.style=A1.style
------------------------------
管子类型管子数量最近差值时间
AA322012-11-18
A5532012-11-19
[其他解释]
select a.*,b.每周差值 as 最近差值 ,b.时间
from t1 a left join t2 b
on a.管子类型 = b.管子类型
and not exists (
select 1 from t2 c
where c.管子类型 = b.管子类型
and c.时间 > b.时间
)
[其他解释]
这个SQL语句怎么写，谁来帮我想想。
[其他解释]
select 管子类型,
管子数量=sum(每周差值),
最近差值=(select top 1 每周差值 from t2 where 管子类型=a.管子类型 order by 时间 desc),
时间=max(时间)
from t2 a group by 管子类型
[其他解释]
路过，看看，上面已经解决
[其他解释]
表1 t1： 管子类型， 管子数量
AA 3
A5 5
表2 t2: 管子类型 每周差值 时间
AA 1 2012-11-11
AA 2 2012-11-18
A5 1 2012-11-12
A5 3 2012-11-19
select * from t1,t2 where t1.管子类型=t2.管子类型