刚才的双表查询的问题在 onlyliu 的热心帮助下,顺利完成了。现在我要再实现三表查询,麻烦大家看一下。表played:
bh_info..bh_bus....p_datetime
203......3........2011-3-1 18:32:07
205......3........2011-3-2 18:32:07
205......3........2011-3-3 18:32:07
205......3........2011-3-4 18:32:07
206......3........2011-3-5 18:32:07
表info:
bh.....name
203....名称1
204....名称2
表history:
bh.....name
205....名称3
206....名称4
表bus:
bh....id....busnumber
1.....56....川A12345
2.....38....川A12346
3.....38....川A12347
4.....56....川A12348说明:bh在info和history里都是唯一的。played表中bh_info是不唯一的。现在就是想输出表played的内容时,能显示出每一个编号所对应的name。bus中的bh是唯一的,played中的bh_bus来自于bus表。我现在要查询played表,输出bh对对应的名称,输出bus里的id和busnumber。在刚才 onlyliu 朋友有帮助下,我写了一个查询,已经实现了我的要求,但我不知道我这样做是否合适,感觉 二个查询里有一部分是重复的,会不会太浪费时间或什么的,谢谢大家的帮助。select played.bh_info as bh_info,info.name as name,played.p_datetime, bus.id as id,bus.busnumber as busnumber from info,played ,bus where played.bh_info= info.bh and bus.bh=played.bh_busunion select played.bh_info as bh_info,history.name as name,played.p_datetime, bus.id as id,bus.busnumber as busnumber from history,played,bus where played.bh_info= history.bh and bus.bh=played.bh_bus
bh_info..bh_bus....p_datetime
203......3........2011-3-1 18:32:07
205......3........2011-3-2 18:32:07
205......3........2011-3-3 18:32:07
205......3........2011-3-4 18:32:07
206......3........2011-3-5 18:32:07
表info:
bh.....name
203....名称1
204....名称2
表history:
bh.....name
205....名称3
206....名称4
表bus:
bh....id....busnumber
1.....56....川A12345
2.....38....川A12346
3.....38....川A12347
4.....56....川A12348说明:bh在info和history里都是唯一的。played表中bh_info是不唯一的。现在就是想输出表played的内容时,能显示出每一个编号所对应的name。bus中的bh是唯一的,played中的bh_bus来自于bus表。我现在要查询played表,输出bh对对应的名称,输出bus里的id和busnumber。在刚才 onlyliu 朋友有帮助下,我写了一个查询,已经实现了我的要求,但我不知道我这样做是否合适,感觉 二个查询里有一部分是重复的,会不会太浪费时间或什么的,谢谢大家的帮助。select played.bh_info as bh_info,info.name as name,played.p_datetime, bus.id as id,bus.busnumber as busnumber from info,played ,bus where played.bh_info= info.bh and bus.bh=played.bh_busunion select played.bh_info as bh_info,history.name as name,played.p_datetime, bus.id as id,bus.busnumber as busnumber from history,played,bus where played.bh_info= history.bh and bus.bh=played.bh_bus
解决方案 »
- 文件加密——CSDN第一次发帖
- 窗口左上角那个图标
- 求助:xtreme toolkit v9.60问题
- SDI能不能动态地改变框架大小
- 请问能不那将list control 控件做成QQ那样的抽屉模式?怎么做?
- 使用OLE DB连接访问SQL Server数据库
- 猛回头,DELPHI开发员学习VC,请大家帮忙!
- 重分求FileZilla的源代码。
- 来看看这个问题
- 难题,可能很简单,但我不知怎么解决!
- 我的vc程序一调试就出现user breakpoint called from code at 0x7c921230,几乎所有的工程都这样,重装vc不管用,请大侠们帮帮小女子!!!
- 程序在XP普通用户登陆情况下执行出错????
from
(select played.bh_info as bh_info,info.name as name,played.p_datetime, bus.id as id,bus.busnumber as busnumber from info,played ,bus where played.bh_info= info.bh and bus.bh=played.bh_busunion select played.bh_info as bh_info,history.name as name,played.p_datetime, bus.id as id,bus.busnumber as busnumber from history,played,bus where played.bh_info= history.bh and bus.bh=played.bh_bus) as a
分析你的语句,也就是说,union上面的是找到所有played.bh_info= info.bh的信息。其他的只是附加信息。 UNION下面的是找到 played.bh_info= history.bh的所有信息。当两个合并的时候就会自动过滤掉完全相同的数据。
你那个可以这么写。
select temp.bh_info,temp.name,temp.bh_bus,bus.id,bus.busnumber,temp.p_datetime from
(select played.bh_info as bh_info ,played.bh_bus as bh_bus ,info.name as name ,played.p_datetime as p_datetime from info,played where played.bh_info= info.bh
union
select played.bh_info as bh_info,played.bh_bus as bh_bus,history.name as name,played.p_datetime as p_datetime from history,played where played.bh_info= history.bh) temp,bus where temp.bh_bus=bus.bh