問(wèn)題描述

問(wèn)題如下:

查詢出現(xiàn)笛卡兒積過(guò)濾的問(wèn)題 例子: A B C D E Q表

select e.name, e.age, e.phone, q.something from a a, b b, c c, d d, e e, q q where a.id = ’zhejiushiyige id’ and b.id = a.bid and c.id = b.cid and d.id = c.did and e.id = d.eid and q.aid = a.id

就是表e是從a開(kāi)始一級(jí)一級(jí)比較下來(lái)的,最終得到的e的結(jié)果是正確的,但是q的結(jié)果會(huì)出現(xiàn)兩次,并且q只跟a有關(guān)聯(lián),請(qǐng)問(wèn)怎么查詢才能解決這個(gè)問(wèn)題呢?

問(wèn)題解答

q表和a表是一對(duì)多的關(guān)系，如果q表的結(jié)果只想出一條，可以在關(guān)聯(lián)前先把q表按照aid字段進(jìn)行匯總，保證每個(gè)aid只有一條，如：

select e.name, e.age, e.phone, q.something from a a, b b, c c, d d, e e, (select aid, max(something)) from q group by aid) table_q where a.id = ’zhejiushiyige id’ and b.id = a.bid and c.id = b.cid and d.id = c.did and e.id = d.eid and table_q.aid = a.id