初学者问题,sql 语句写法和索引问题。
数据库中有五个表,分别是 a1,a2,a3,a4,a4,每个表中都有字段 banji,bainhao ,我想把这几个表关联起来
请问这样写对吗,或者有其他更好的写法,怎写执行速度能快
select * from a1 a
inner join a2 b on a.banji=b.banji
inner join a3 c on a.banji=c.banji
inner join a4 d on a.banji=d.banji
inner join a5 e on a.banji=e.banji
where a.bianhao=b.bianhao
and a.bianhao=c.bianhao
and a.bianhao=d.bianhao
and a.bianhao=e.bianhao
数据库中 表是否该建索引,或主键。这两个字段 banji,bainhao怎么建,建了查询速度能快吗。谢谢
------解决方案--------------------select * from a1 a
inner join a2 b on a.banji=b.banji
inner join a3 c on a.banji=c.banji
inner join a4 d on a.banji=d.banji
inner join a5 e on a.banji=e.banji
或者
select * from a1 a
, a2 b
, a3 c
, a4 d
, a5 e
where a.bianhao=b.bianhao
and a.bianhao=c.bianhao
and a.bianhao=d.bianhao
and a.bianhao=e.bianhao
------解决方案--------------------改成这样,更加清晰一点:
select * from a1 a
inner join a2 b on a.banji=b.banji and a.bianhao=b.bianhao
inner join a3 c on a.banji=c.banji and a.bianhao=c.bianhao
inner join a4 d on a.banji=d.banji and a.bianhao=d.bianhao
inner join a5 e on a.banji=e.banji and a.bianhao=e.bianhao
------解决方案--------------------另外,可以建聚集索引,能加快速度:
create clustered index a1 on a1(banji,bianhao)
create clustered index a2 on a1(banji,bianhao)
create clustered index a3 on a1(banji,bianhao)
create clustered index a4 on a1(banji,bianhao)
create clustered index a5 on a1(banji,bianhao)
------解决方案--------------------看错了,要两个列的话用这种
select * from a1 a
inner join a2 b on a.banji=b.banji and a.bianhao=b.bianhao
inner join a3 c on a.banji=c.banji and a.bianhao=c.bianhao
inner join a4 d on a.banji=d.banji and a.bianhao=d.bianhao
inner join a5 e on a.banji=e.banji and a.bianhao=e.bianhao
或者:
select * from a1 a&nb