日期:2014-05-17  浏览次数:20975 次

access 多表联合查询问题
例如我有三个表分别为A,B,C。其中A表有字段
id,title, date, pic
1 同样人员 2011-5-9 广发华福
2 红歌会 2010-9-5 规范法官

B表有字段
id,title,date, pic, color
1 嘎嘎服 2011-3-5 发给 red  
2 哦哦看 2011-9-2 共方法 blue  
C表有字段
id,title, date, color,user
1 吞又吐 2011-8-3 red 222
2 嫁汉嫁汉 2011-9-8 blue 272

我想将三个表联合查询按date排序,同时能够显示所有表中的字段,然后添加一个表名。所需查询结果如下图所示
ID title date pic color user name
1 同样人员 2011-5-9 广发华福 A
2 哦哦看 2011-9-2 共方法 blue B
2 嫁汉嫁汉 2011-9-8 blue 272 C
谢谢大家!

------解决方案--------------------
select id,title,date, color,pic,'A' as name from A
union all
select id,title,date,color,pic,'B' as name from B
union all
select id,title,date,color,pic,'C' as name from C
------解决方案--------------------
你最外面用select top 3 *当然就取到了所有结果集的前3条了啊,改成这样
SELECT * FROM (SELECT top 3 id,title,date,color,pic,'A' as name FROM A where pass=true ORDER BY id DESC union all SELECT top 3 id,title,date,color,pic,'B' as name FROM B where pass=true ORDER BY id DESC union all SELECT top 3 id,title,date,color,pic,'C' as name FROM C where pass=true ORDER BY id DESC ) order by date desc