化繁为简，有本事的来拿分！

[Sandyguoguo]

以下是一条多表联查的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

[Sandyguoguo]

三楼挺好，把代码写出来了，虽然没简化多少。

[--小F--]

连接字段加索引。

[koumingjie]

把子查询部分改成视图

[-晴天]

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 userinfo
on 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 userinfo
on AP_Detail .D_DUserID=userinfo.duserid
) as duserinfo
) on AP_Detail.AP_DetailID =duserinfo.adID where D_PrimaryID =2

[勿勿]

分成一个个小视图来jion起来

[中国风]

這樣改

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

[中国风]

sys_Group 同 sys_User 的關系時，可改為inner join

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.userid

[中国风]

改改 where 條件漏了加

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.userid
WHERE a.D_PrimaryID=2

[Lucifer825]

看到lz这语句，我有种蛋蛋的忧伤~

[Sandyguoguo]

是不是这样的问题没什么水平啊？

[-晴天]

不是没水平,而是这个题目挺拗的,同一个查询,作为子查询做了两遍,看了一下,好像又不得不这么做.
如果是2005及以上的话,可以用公用表达式做那个子查询.

[Sandyguoguo]

为什么？
[Quote=引用 13 楼 lucifer825 的回复:]
看到lz这语句，我有种蛋蛋的忧伤~
[/Quote]