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 R1HM1002 20070605 0900 177 17 300 300 30
M1002 20070605 1000 209 22 312 312 31
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 R1HM1002 20070605 0900 177 17 300 300 30
M1002 20070605 1000 209 22 312 312 31
解决方案 »
- 求一SQL语句,在线等!
- 加急!sql 行转列问题
- 求大神们帮我把sql server存储过程的sql语句转成mysql的sql语句!!!
- 怎么创建临时表?
- 找得有点累了,也试累了。问一个关于select 和group by 的问题。
- 请问关于innerjoin的问题
- sql语句问题
- 急急急,修改之间的关系问题,给100分
- 在存储过程中如何把日期型变量加到拼得sql串中
- 为什么在sql2000中不能使用in子句?但在vb+mdb中可以这样啊?SELECT CustomerID FROM Customers IN 'C:\a.mdb'
- 关于四舍五入的问题请教?
- 请问这种情况是建聚集索引,还是建两个主健,还是什么都不建?
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
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
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.StationIDWHERE A.Time= B.Time AND A.TIME = C.TIME
M1002 20070605 0900 177 17 300 300 30
M1002 20070605 1000 209 22 312 312 31
这里是按StationID 为主键,如果是符合主键再加上其他的连接条件
select A.*,D.TT,D.max,D.r1h from A,(select * from B join C on B.StationID =C.StationID ) as D where A.StationID =D.StationID
from AAAAAAAAAAAA a
left join BBBBBBBBBBB b on a.StationID=b.StationID and a.Time=b.Time
left join CCCCCCCCCCCCC c on a.StationID=c.StationID and a.Time=c.Time
StationID Date Time Wd2m Ws2m TT TMax R1H
---------------------------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
M1002 20070605 0900 177 17 300 300 30
M1002 20070605 1000 209 22 312 312 31(2 row(s) affected)
FROM A LEFT JOIN B ON A.STATINONID=B.STATIONONID AND A.DATE=B.DATE AND A.TIME=B.TIME LEFT JOIN C ON A.STATINONID=C.STATINONID AND A.DATE=C.DATE AND A.TIME=C.TIME