SQL多表查询问题

[longjun1627]

有三个关联表，分别为a（id,code,name），b(aid,cid)，c(id,code,name)
b为a,c的关系表，a与b为一对多，b与c为多对多，想以a为主表，查询显示出带c的一条信息（c中可能有多条，但只显示出一条就行），比如有如下数据：
a表：
1，a1，a1
2，a2，a2
3，a2，a2
b表：
1，1
1，2
2，3
2，4
c表：
1，c1，c1
2，c2，c2
3，c3，c3
4，c4，c4
想查询后显示的结果为：
1，a1，a1，c1，c1
2，a2，a2，c3，c3
3，a2，a2，null，null
各位大虾有什么高招没？请指教一二，谢谢！

[在路上_-]

试试这个

select a.id, a.code, a.name, c.code, c.name
from a left join (select aid, min(cid) cid from b group by aid) t on t.aid=a.id
  left join c on c.id=t.cid

[godbless_zf]

楼主可以讲b表按aid分组然后查询出每个分组中最小的cid作为子查询，然后sql就好些了，代码：

SELECT a.id,a.code,a.name,c.code,c.name 
FROM #a AS a
LEFT JOIN (
SELECT aid,MIN(cid) AS cid FROM #b GROUP BY aid
) AS b ON a.id=b.aid
LEFT JOIN #c AS c ON b.cid=c.id

输出结果： id code name code name 1 a1 a1 c1 c1 2 a2 a2 c3 c3 3 a3 a3 NULL NULL

[jiajiaren]

IF OBJECT_ID('[A]') IS NOT  NULL 
DROP TABLE [A]
GO
CREATE TABLE [A](id INT,code VARCHAR(10),NAME  VARCHAR(10))
INSERT INTO [A] SELECT 1,'a1','a1'  UNION ALL SELECT  2,'a2','a2'  UNION ALL SELECT  3,'a2','a2'

IF OBJECT_ID('[B]') IS NOT  NULL 
DROP TABLE [B]
GO
CREATE TABLE [B](aid INT ,cid INT )
INSERT INTO [B] SELECT 1,1  UNION ALL SELECT 1,2  UNION ALL SELECT 2,3 UNION ALL SELECT 2,4

IF OBJECT_ID('[C]') IS NOT  NULL 
DROP TABLE [C]
GO
CREATE TABLE [C](id INT,code VARCHAR(10),NAME  VARCHAR(10))
INSERT INTO [C] SELECT 1,'c1','c1'  UNION ALL SELECT  2,'c2','c2'  UNION ALL SELECT  3,'c3','c3'   UNION ALL SELECT   4,'c4','c4'


------------------------------------------------查询--------------------------------------------------------------------------
SELECT b.id,b.code,b.name,c.code,c.name FROM(
SELECT id,MIN(code) code,MIN(name) name,MIN(cid) cid FROM [A] a LEFT JOIN [B] b ON a.id=b.aid GROUP BY a.id)b LEFT JOIN [C] c ON  b.cid=c.id
 
-----------------------------------------------结果--------------------------------------------------------------------------
/*
 id          code       NAME       code       name
----------- ---------- ---------- ---------- ----------
1           a1         a1         c1         c1
2           a2         a2         c3         c3
3           a2         a2         NULL       NULL
(3 行受影响)
*/