这么烂的sql语句如何优化呢

岭南六少 发布于 2012/03/06 20:30
阅读 2K+
收藏 1
SQL

如题:

select *,(select terminaltypename from tk_terminaltype where bk_terminaltype.id=bk_terminal.terminaltypeid) as terminaltypename,(select roomno from bk_roominfo where bk_roominfo.id=bk_terminal.roomid) as roomno,(select firl from bk_updatelogsdetail where bk_updatelogsdetail.serial=bk_terminal.mac order by update_time desc limit 0,1) as currentversion from bk_terminal where hid='10000'order by roomno

加载中
2
岭南六少
岭南六少

select 
*,
(select terminaltypename from tk_terminaltype where bk_terminaltype.id=bk_terminal.terminaltypeid) as terminaltypename,
(select roomno from bk_roominfo where bk_roominfo.id=bk_terminal.roomid) as roomno,
(select firl from bk_updatelogsdetail where bk_updatelogsdetail.serial=bk_terminal.mac order by update_time desc limit 0,1) as currentversion 
from bk_terminal 
where hid='10000'order by roomno

1
liuex
liuex

1、排版

2、看起来是mysql吧?使用explain分析执行计划

0
G.
G.
估计写这条语句的人, 当时可能还心中暗喜:  "这么复杂的关系我一条语句就能搞定."
0
Hycomer
Hycomer
select 

 *, 
(select terminaltypename from tk_terminaltype where 
  bk_terminaltype.id=bk_terminal.terminaltypeid ) as terminaltypename ,

(select roomno           from bk_roominfo     where 
  bk_roominfo.id=bk_terminal.roomid)  as roomno ,

(select firl from bk_updatelogsdetail where 
  bk_updatelogsdetail.serial=bk_terminal.mac order by update_time desc limit 0,1) 
  as  currentversion 

from bk_terminal

where hid='10000'  

order by  roomno

tk_terminaltype(终端类别表)         id

bk_roominfo(房间信息表)             id

bk_updatelogsdetail(日志详情表)     serial

bk_terminal(终端表)                 id , mac


终端类别  和 终端是  :  1对多的关系
房间 和终端 是      :  1对1的关系么,还是1对多 ?????  
终端和日志详情表是   :  1对多的关系
岭南六少
岭南六少
一个房间有多个终端 所以一对多
0
岭南六少
岭南六少

蛋疼啊

看到这条sql语句我就头疼、

才1k的数据量,查询速度就你慢啦!!!!!

mark35
mark35
每一行的那几个字段都会做同样的查询,不慢才怪
0
wx---每日佳选
wx---每日佳选
哥们,重新写吧. 
0
超级奶爸老谭
超级奶爸老谭
不是很难,可以用表连接搞掂。不明白为什么这样写。一点也不负责任!
everyx
everyx
+1 写在select 里面效率很低
0
Hycomer
Hycomer

bk_updatelogsdetail(日志详情表) :  对于每个终端,每天的日志记录应该有很多吧,不知道有没有猜错,

我觉得,这4个表组合成联合查询,很累人,还是拆分后再查询,放到数组中,再组装成所需要的数据吧。

还是拆分为2次查询吧。

0
RickyFeng
RickyFeng
不要在select内关联表,用joinleft,还有分析开索引用上没有
返回顶部
顶部