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起来

------解决方案--------------------
不是没水平,而是这个题目挺拗的,同一个查询,作为子查询做了两遍,看了一下,好像又不得不这么做.
如果是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.userid
WHERE a.D_PrimaryID=2