tbl_A:
STCD STNM
10001 站点A
10002 站点B
10003 站点C
10004 站点D
10005 站点Etbl_B:
STCD TM VAL1 VAL2
...
10003 2008-12-01 1 1
10003 2008-12-03 3 3
10003 2008-12-07 7 7
...tbl_C:
STCD TM VAL3 VAL4
...
10003 2008-12-02 4 4
10003 2008-12-03 6 6
10003 2008-12-04 8 8
10003 2008-12-07 14 14
...tbl_A:站点代码、站点名称的索引表
tbl_B:存储所有站点的某一类监测数据(如:VAL1、VAL2)
tbl_C:存储所有站点的另一类监测数据(如:VAL3、VAL4)tbl_B和tbl_C的TM(时间)字段数值不一定相同问题是如何通过查询,得到下边这样一张表(以查询"站点C"为例):
STNM STCD TM VAL1 VAL2 VAL3 VAL4
站点C 10003 2008-12-01 1 2 - -
站点C 10003 2008-12-02 - - 4 4
站点C 10003 2008-12-03 3 3 6 6
站点C 10003 2008-12-04 - - 8 8
站点C 10003 2008-12-07 7 7 14 14结果表中,VAL1~4的任何值为空可以用""或者"-"代替;
如果上述结果表不好实现,用下边的结果表代替也可以:STNM STCD TM VAL1 VAL2 VAL3 VAL4
站点C 10003 2008-12-01 1 2 - -
站点C 10003 2008-12-02 - - 4 4
站点C 10003 2008-12-03 3 3 - -
站点C 10003 2008-12-03 - - 6 6
站点C 10003 2008-12-04 - - 8 8
站点C 10003 2008-12-07 7 7 - -
站点C 10003 2008-12-07 - - 14 14即:日期相同的两类监测数据可以不合并也可以~~~
先多谢了!~~~~~
STCD STNM
10001 站点A
10002 站点B
10003 站点C
10004 站点D
10005 站点Etbl_B:
STCD TM VAL1 VAL2
...
10003 2008-12-01 1 1
10003 2008-12-03 3 3
10003 2008-12-07 7 7
...tbl_C:
STCD TM VAL3 VAL4
...
10003 2008-12-02 4 4
10003 2008-12-03 6 6
10003 2008-12-04 8 8
10003 2008-12-07 14 14
...tbl_A:站点代码、站点名称的索引表
tbl_B:存储所有站点的某一类监测数据(如:VAL1、VAL2)
tbl_C:存储所有站点的另一类监测数据(如:VAL3、VAL4)tbl_B和tbl_C的TM(时间)字段数值不一定相同问题是如何通过查询,得到下边这样一张表(以查询"站点C"为例):
STNM STCD TM VAL1 VAL2 VAL3 VAL4
站点C 10003 2008-12-01 1 2 - -
站点C 10003 2008-12-02 - - 4 4
站点C 10003 2008-12-03 3 3 6 6
站点C 10003 2008-12-04 - - 8 8
站点C 10003 2008-12-07 7 7 14 14结果表中,VAL1~4的任何值为空可以用""或者"-"代替;
如果上述结果表不好实现,用下边的结果表代替也可以:STNM STCD TM VAL1 VAL2 VAL3 VAL4
站点C 10003 2008-12-01 1 2 - -
站点C 10003 2008-12-02 - - 4 4
站点C 10003 2008-12-03 3 3 - -
站点C 10003 2008-12-03 - - 6 6
站点C 10003 2008-12-04 - - 8 8
站点C 10003 2008-12-07 7 7 - -
站点C 10003 2008-12-07 - - 14 14即:日期相同的两类监测数据可以不合并也可以~~~
先多谢了!~~~~~
--环境
create table #tbl_A
(
STCD varchar(5),
STNA varchar(10)
)create table #tbl_B
(
STCD varchar(5),
TM varchar(10),
VAL1 int,
VAL2 int
)create table #tbl_C
(
STCD varchar(5),
TM varchar(10),
VAL3 int,
VAL4 int
)insert into #tbl_A select '10001','站点A'
insert into #tbl_A select '10002','站点B'
insert into #tbl_A select '10003','站点C'
insert into #tbl_A select '10004','站点D'
insert into #tbl_A select '10005','站点E'insert into #tbl_B select '10003', '2008-12-01', 1 , 1
insert into #tbl_B select '10003', '2008-12-03', 3 , 3
insert into #tbl_B select '10003', '2008-12-07', 7 , 7insert into #tbl_C select '10003', '2008-12-02', 4 , 4
insert into #tbl_C select '10003', '2008-12-03', 6 , 6
insert into #tbl_C select '10003', '2008-12-04' , 8 , 8
insert into #tbl_C select '10003', '2008-12-07', 14 , 14 --查询
select isnull(b.stcd,c.stcd) as stcd,isnull(b.tm,c.tm) as tm,
isnull(cast(b.VAL1 as varchar(5)),'-') as VAL1,
isnull(cast(b.VAL2 as varchar(5)),'-') as VAL2,
isnull(cast(c.VAL3 as varchar(5)),'-') as VAL3,
isnull(cast(c.VAL4 as varchar(5)),'-') as VAL4
from #tbl_B b full join #tbl_C c on b.STCD = c.STCD and b.tm = c.tm
inner join #tbl_a a on isnull(b.stcd,c.stcd) = a.STCD--结果
/*
stcd tm VAL1 VAL2 VAL3 VAL4
----- ---------- ----- ----- ----- -----
10003 2008-12-01 1 1 - -
10003 2008-12-03 3 3 6 6
10003 2008-12-07 7 7 14 14
10003 2008-12-02 - - 4 4
10003 2008-12-04 - - 8 8(5 行受影响)
*/
还有一问题请教
TM字段可否排序显示
--重新整理一下
--加排序/查询条件/结果集create table #tbl_A
(
STCD varchar(5),
STNA Nvarchar(10)
)create table #tbl_B
(
STCD varchar(5),
TM varchar(10),
VAL1 int,
VAL2 int
)create table #tbl_C
(
STCD varchar(5),
TM varchar(10),
VAL3 int,
VAL4 int
)insert into #tbl_A select '10001',N'站点A'
insert into #tbl_A select '10002',N'站点B'
insert into #tbl_A select '10003',N'站点C'
insert into #tbl_A select '10004',N'站点D'
insert into #tbl_A select '10005',N'站点E'insert into #tbl_B select '10003', '2008-12-01', 1 , 1
insert into #tbl_B select '10003', '2008-12-03', 3 , 3
insert into #tbl_B select '10003', '2008-12-07', 7 , 7insert into #tbl_C select '10003', '2008-12-02', 4 , 4
insert into #tbl_C select '10003', '2008-12-03', 6 , 6
insert into #tbl_C select '10003', '2008-12-04' , 8 , 8
insert into #tbl_C select '10003', '2008-12-07', 14 , 14
select a.STNA,
isnull(b.stcd,c.stcd) as stcd,isnull(b.tm,c.tm) as tm,
isnull(cast(b.VAL1 as varchar(5)),'-') as VAL1,
isnull(cast(b.VAL2 as varchar(5)),'-') as VAL2,
isnull(cast(c.VAL3 as varchar(5)),'-') as VAL3,
isnull(cast(c.VAL4 as varchar(5)),'-') as VAL4
from #tbl_B b full join #tbl_C c on b.STCD = c.STCD and b.tm = c.tm
inner join #tbl_a a on isnull(b.stcd,c.stcd) = a.STCD
WHERE a.stcd = '10003'
order by a.STCD,isnull(b.tm,c.tm)/*
STNA stcd tm VAL1 VAL2 VAL3 VAL4
---------- ----- ---------- ----- ----- ----- -----
站点C 10003 2008-12-01 1 1 - -
站点C 10003 2008-12-02 - - 4 4
站点C 10003 2008-12-03 3 3 6 6
站点C 10003 2008-12-04 - - 8 8
站点C 10003 2008-12-07 7 7 14 14(5 行受影响)
*/
--> 测试数据:[tbl_A]
if object_id('[tbl_A]') is not null drop table [tbl_A]
create table [tbl_A]([STCD] int,[STNM] varchar(5))
insert [tbl_A]
select 10001,'站点A' union all
select 10002,'站点B' union all
select 10003,'站点C' union all
select 10004,'站点D' union all
select 10005,'站点E'
--> 测试数据:[tbl_B]
if object_id('[tbl_B]') is not null drop table [tbl_B]
create table [tbl_B]([STCD] int,[TM] datetime,[VAL1] int,[VAL2] int)
insert [tbl_B]
select 10003,'2008-12-01',1,1 union all
select 10003,'2008-12-03',3,3 union all
select 10003,'2008-12-07',7,7
--> 测试数据:[tbl_C]
if object_id('[tbl_C]') is not null drop table [tbl_C]
create table [tbl_C]([STCD] int,[TM] datetime,[VAL3] int,[VAL4] int)
insert [tbl_C]
select 10003,'2008-12-02',4,4 union all
select 10003,'2008-12-03',6,6 union all
select 10003,'2008-12-04',8,8 union all
select 10003,'2008-12-07',14,14select A.STCD, A.STNM, TM
, VAL1=isnull(rtrim(VAL1), ''), VAL2=isnull(rtrim(VAL2), '')
, VAL3=isnull(rtrim(VAL3), ''), VAL4=isnull(rtrim(VAL4), '')
from tbl_A A join
(select STCD=isnull(B.STCD,C.STCD), TM=isnull(B.TM,C.TM), VAL1,VAL2,VAL3,VAL4
from tbl_B B full join tbl_C C on B.stcd=C.stcd and B.TM=C.TM
) T
on A.stcd=T.stcd
where A.stnm='站点C'
order by TM/*
STCD STNM TM VAL1 VAL2 VAL3 VAL4
----------- ----- ----------------------- ------------ ------------ ------------ ------------
10003 站点C 2008-12-01 00:00:00.000 1 1
10003 站点C 2008-12-02 00:00:00.000 4 4
10003 站点C 2008-12-03 00:00:00.000 3 3 6 6
10003 站点C 2008-12-04 00:00:00.000 8 8
10003 站点C 2008-12-07 00:00:00.000 7 7 14 14(5 行受影响)
*/drop table [tbl_A],[tbl_B],[tbl_C]
, VAL1=isnull(rtrim(VAL1), ''), VAL2=isnull(rtrim(VAL2), '')
, VAL3=isnull(rtrim(VAL3), ''), VAL4=isnull(rtrim(VAL4), '')
from tbl_A A join
(select STCD=isnull(B.STCD,C.STCD), TM=isnull(B.TM,C.TM), VAL1,VAL2,VAL3,VAL4
from tbl_B B full join tbl_C C on B.stcd=C.stcd and B.TM=C.TM
) T
on A.stcd=T.stcd
where A.stnm='站点C'
order by TM/*
STCD STNM TM VAL1 VAL2 VAL3 VAL4
----------- ----- ---------- ------------ ------------ ------------ ------------
10003 站点C 2008-12-01 1 1
10003 站点C 2008-12-02 4 4
10003 站点C 2008-12-03 3 3 6 6
10003 站点C 2008-12-04 8 8
10003 站点C 2008-12-07 7 7 14 14(5 行受影响)
*/
create table B(STCD varchar(5),TM varchar(10),VAL1 int,VAL2 int)
create table C(STCD varchar(5),TM varchar(10),VAL3 int,VAL4 int)
insert into A select '10001','站点A'
insert into A select '10002','站点B'
insert into A select '10003','站点C'
insert into A select '10004','站点D'
insert into A select '10005','站点E'
insert into B select '10003', '2008-12-01', 1 , 1
insert into B select '10003', '2008-12-03', 3 , 3
insert into B select '10003', '2008-12-07', 7 , 7
insert into C select '10003', '2008-12-02', 4 , 4
insert into C select '10003', '2008-12-03', 6 , 6
insert into C select '10003', '2008-12-04' , 8 , 8
insert into C select '10003', '2008-12-07', 14 , 14 select a.STNA , a.stcd , m.tm , m.val1 , m.val2 , m.val3 , m.val4 from a ,
(
select isnull(b.STCD , c.STCD) STCD ,
isnull(b.tm , c.tm) tm,
isnull(cast(b.val1 as varchar), '-') val1,
isnull(cast(b.val2 as varchar), '-') val2,
isnull(cast(c.val3 as varchar), '-') val3,
isnull(cast(c.val4 as varchar), '-') val4
from b full join c on b.STCD = c.STCD and b.tm = c.tm
) m
where a.stcd = m.stcd and a.stna = '站点C'
order by stna , a.stcd , tmdrop table a,b,c/*
STNA stcd tm val1 val2 val3 val4
---------- ----- ---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
站点C 10003 2008-12-01 1 1 - -
站点C 10003 2008-12-02 - - 4 4
站点C 10003 2008-12-03 3 3 6 6
站点C 10003 2008-12-04 - - 8 8
站点C 10003 2008-12-07 7 7 14 14(所影响的行数为 5 行)*/
select isnull(b.stcd,c.stcd) as stcd,isnull(b.tm,c.tm) as tm,
isnull(cast(b.VAL1 as varchar(5)),'-') as VAL1,
isnull(cast(b.VAL2 as varchar(5)),'-') as VAL2,
isnull(cast(c.VAL3 as varchar(5)),'-') as VAL3,
isnull(cast(c.VAL4 as varchar(5)),'-') as VAL4
from #tbl_B b full join #tbl_C c on b.STCD = c.STCD and b.tm = c.tm
inner join #tbl_a a on isnull(b.stcd,c.stcd) = a.STCD order by b.tmstcd tm VAL1 VAL2 VAL3 VAL4
----- ---------- ----- ----- ----- -----
10003 2008-12-01 1 1 - -
10003 2008-12-02 - - 4 4
10003 2008-12-03 3 3 6 6
10003 2008-12-04 - - 8 8
10003 2008-12-07 7 7 14 14(所影响的行数为 5 行)
create table #tbl_A
(
STCD varchar(5),
STNA varchar(10)
)create table #tbl_B
(
STCD varchar(5),
TM varchar(10),
VAL1 int,
VAL2 int
)create table #tbl_C
(
STCD varchar(5),
TM varchar(10),
VAL3 int,
VAL4 int
)insert into #tbl_A select '10001','站点A'
insert into #tbl_A select '10002','站点B'
insert into #tbl_A select '10003','站点C'
insert into #tbl_A select '10004','站点D'
insert into #tbl_A select '10005','站点E'insert into #tbl_B select '10003', '2008-12-01', 1 , 1
insert into #tbl_B select '10003', '2008-12-03', 3 , 3
insert into #tbl_B select '10003', '2008-12-07', 7 , 7insert into #tbl_C select '10003', '2008-12-02', 4 , 4
insert into #tbl_C select '10003', '2008-12-03', 6 , 6
insert into #tbl_C select '10003', '2008-12-04' , 8 , 8
insert into #tbl_C select '10003', '2008-12-07', 14 , 14 select STNA,t.*
from (
select isnull(b.STCD,c.STCD) as STCD,isnull(b.tm,c.tm) as tm,ISNULL(RTRIM(VAL1),'-') AS VAL1,ISNULL(RTRIM(VAL2),'-') AS VAL2,ISNULL(RTRIM(VAL3),'-') VAL3,ISNULL(RTRIM(VAL4),'-') VAL4
from #tbl_b as b full join #tbl_c as c
on b.STCD=b.STCD and b.tm=c.tm
) t join #tbl_a as a
on a.STCD=t.STCD
order by tm
/*
STNA STCD tm VAL1 VAL2 VAL3 VAL4
---------- ----- ---------- ------------ ------------ ------------ ------------
站C 10003 2008-12-01 1 1 - -
站C 10003 2008-12-02 - - 4 4
站C 10003 2008-12-03 3 3 6 6
站C 10003 2008-12-04 - - 8 8
站C 10003 2008-12-07 7 7 14 14
*/drop table #tbl_a
drop table #tbl_b
drop table #tbl_c