表A与表B,表A内有字段aaa、bbb、ccc,表B内有字段ddd、eee、fff。
要求在A.aaa='123'和B.ddd='456'的两个结果中查询,条件为A.bbb=B.eee,结果列出A.aaa、A.bbb、A.ccc、B.ddd、B.eee、B.fff及重复记录的数目(就是说有几条A.bbb=B.eee的结果)这七项,结果按照重复记录的数目从大往小排列。
要求在A.aaa='123'和B.ddd='456'的两个结果中查询,条件为A.bbb=B.eee,结果列出A.aaa、A.bbb、A.ccc、B.ddd、B.eee、B.fff及重复记录的数目(就是说有几条A.bbb=B.eee的结果)这七项,结果按照重复记录的数目从大往小排列。
FROM A INNER JOIN B ON A.bbb=B.eee
WHERE A.aaa='123'和B.ddd='456'
GROUP BY A.aaa,A.bbb,A.ccc,B.ddd,B.eee,B.fff
ORDER BY CNT DESC
A.aaa,
A.bbb,
A.ccc,
B.ddd,
B.eee,
B.fff
From
A
Inner Join
B
On A.bbb=B.eee
Where A.aaa='123' And B.ddd='456'
FROM A INNER JOIN B ON A.bbb=B.eee
WHERE A.aaa='123' and B.ddd='456'
GROUP BY A.aaa,A.bbb,A.ccc,B.ddd,B.eee,B.fff
ORDER BY CNT DESC
A.aaa,
A.bbb,
A.ccc,
B.ddd,
B.eee,
B.fff,
Count(*) As Count
From
A
Inner Join
B
On A.bbb=B.eee
Where A.aaa='123' And B.ddd='456'
Group By
A.aaa,
A.bbb,
A.ccc,
B.ddd,
B.eee,
B.fff
Order By
Count Desc
paoluo(一天到晚游泳的鱼)
写全了, sorry
虽然和普通的关联查询效果一样,
但看起来直观明了,而且少写很多别名,省事
现在结贴,
因为rockyljt(江濤) 先给出正确答案,所以分数高点,呵呵。