以下是一条多表联查的SQL语句，大家看看能不能简化一下。 具体实现了什么功能，我就不说了，还劳烦大家分析。
select * from AP_Detail
left join (select UserID,u_cname,(select G_CName from sys_Group where GroupID =sys_User .U_GroupID) as G_CName from sys_User) as userinfo(userid,username,gropname)
on AP_Detail .D_UserID=userinfo.userid
left join (select AP_DetailID,userinfo.duserid ,userinfo .dusername,userinfo .dgropname from AP_Detail
left join (select UserID,u_cname,(select G_CName from sys_Group where GroupID =sys_User .U_GroupID) as G_CName from sys_User) as userinfo(duserid,dusername,dgropname)
on AP_Detail .D_DUserID=userinfo.duserid) as duserinfo(adID,duserid,dusername,dgropname) on AP_Detail .AP_DetailID =duserinfo.adID where D_PrimaryID =2
------解决方案--------------------
连接字段加索引。
------解决方案--------------------
把子查询部分改成视图
------解决方案--------------------
- SQL code
select * from AP_Detail left join (select a.UserID,a.u_cname,b.GCName as groupname from sys_User a inner join sys_Group b on a.U_GroupID=b.GroupID) as userinfoon AP_Detail .D_UserID=userinfo.userid left join (select AP_DetailID adID, userinfo.duserid, userinfo.dusername, userinfo.dgropname from AP_Detail left join (select a.UserID,a.u_cname,b.GCName as groupname from sys_User a inner join sys_Group b on a.U_GroupID=b.GroupID) as userinfoon AP_Detail .D_DUserID=userinfo.duserid) as duserinfo) on AP_Detail.AP_DetailID =duserinfo.adID where D_PrimaryID =2
------解决方案--------------------
分成一个个小视图来jion起来
------解决方案--------------------
不是没水平,而是这个题目挺拗的,同一个查询,作为子查询做了两遍,看了一下,好像又不得不这么做.
如果是2005及以上的话,可以用公用表达式做那个子查询.
------解决方案--------------------
這樣改
- SQL code
SELECT a.*, c.u_cname AS username, b.G_CName AS gropname, e.u_cname AS dusername, d.G_CName AS dgropname FROM AP_Detail AS a LEFT JOIN ( sys_Group AS b LEFT JOIN sys_User AS c ON b.GroupID=c.U_GroupID) ON a.D_UserID=b.userid LEFT JOIN ( sys_Group AS d LEFT JOIN sys_User AS e ON d.GroupID=e.U_GroupID) ON a.D_DUserID=e.userid
------解决方案--------------------
改改 where 條件漏了加
- SQL code
SELECT a.*, c.u_cname AS username, b.G_CName AS gropname, e.u_cname AS dusername, d.G_CName AS dgropname FROM AP_Detail AS a LEFT JOIN ( sys_Group AS b INNER JOIN sys_User AS c ON b.GroupID=c.U_GroupID) ON a.D_UserID=b.userid LEFT JOIN ( sys_Group AS d INNER JOIN sys_User AS e ON d.GroupID=e.U_GroupID) ON a.D_DUserID=e.useridWHERE a.D_PrimaryID=2