3个表连接的SQL怎么写？
3个表连接的SQL怎么写？
A表
StationID Date Time Wd2m Ws2m
M1002 20070605 0900 177 17
M1002 20070605 1000 209 22
B表
StationID Date Time TT TMax
M1002 20070605 0900 300 300
M1002 20070605 1000 312 312
M1001 20070605 1100 342 354
C 表
StationID Date Time R1H
M1002 20070605 0900 30
M1002 20070605 1000 31
M1001 20070605 1100 34
写成这样
StationID Date Time Wd2m Ws2m TT TMax R1H
M1002 20070605 0900 177 17 300 300 30
M1002 20070605 1000 209 22 312 312 31
[解决办法]
最简单的就是用视图
[解决办法]
select a.StationID, a.Date , a.Time , a.Wd2m , a.Ws2m ,b.TT, b.TMax, c.R1H
from 表a a inner join 表b b on a.StationID=b.StationID and a.Date=b.Date and a.Time=b.Time
inner join 表c c on a.StationID=c.StationID and a.Date=c.Date and a.Time=c.Time
[解决办法]
select
A.*,B.TT,B.TMax,C.R1H
from
A,B,C
where
A.StationID=B.StationID and A.Date=B.Date and A.Time=B.Time
and
A.StationID=C.StationID and A.Date=C.Date and A.Time=C.Time
[解决办法]
select A.StationID as StationID ,a.Date as Date,a.Time as Time ,a.Wd2m as Wd2m,
a.Ws2m as Ws2m ,b.TT as TT b.TMax as TMax,c.R1H as R1H
where a.StationID=b.StationID and a.Date=b.Date and a.StationID =c.StationID and a.Date = c.Date and a.StationID = "M1002 " and a.Date= "20070605 "
?????
[解决办法]
create table AAAAAAAAAAAA
(
StationID VARCHAR(100),
Date VARCHAR(10),
Time VARCHAR(10),
Wd2m VARCHAR(10),
Ws2m VARCHAR(10)
)
create table BBBBBBBBBBB
(
StationID VARCHAR(100),
Date VARCHAR(10),
Time VARCHAR(10),
TT VARCHAR(10),
TMax VARCHAR(10)
)
create table CCCCCCCCCCCCC
(
StationID VARCHAR(100),
Date VARCHAR(10),
Time VARCHAR(10),
R1H VARCHAR(10)
)
INSERT INTO AAAAAAAAAAAA
SELECT 'M1002 ' , '20070605 ', '0900 ' , '177 ' , '17 ' UNION ALL SELECT
'M1002 ' , '20070605 ', '1000 ' , '209 ' , '22 '
INSERT INTO BBBBBBBBBBB SELECT
'M1002 ' , ' 20070605 ', '0900 ' , '300 ' , '300 ' UNION ALL SELECT
'M1002 ' , '20070605 ', '1000 ', '312 ', '312 ' UNION ALL SELECT
'M1001 ' , '20070605 ', '1100 ', '342 ' , '354 '
INSERT INTO CCCCCCCCCCCCC SELECT
'M1002 ' , '20070605 ', '0900 ', '30 ' UNION ALL SELECT
'M1002 ' , '20070605 ', '1000 ', '31 ' UNION ALL SELECT
'M1001 ' , '20070605 ', '1100 ', ' 34 '
SELECT A.* ,B.TT,B.TMax,C.R1H
FROM AAAAAAAAAAAA A INNER JOIN BBBBBBBBBBB B
ON A.StationID =B.StationID
INNER JOIN CCCCCCCCCCCCC C ON A.StationID = C.StationID
WHERE A.Time= B.Time AND A.TIME = C.TIME
