• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

怎么连接两个select语句

andrew_zx 2008-03-25 10:58:07
(select do.id, c.start_Time, c.ani, c.calling_Device, c.callee_Device, c.answer_Duration,
c.call_Type, c.ani_Area, c.undesktop_Notify_Missed_Call FROM Call_Master as c, Device_Info do
WHERE c.answer_Duration=0 AND do.apply_State=2 and c.call_Type in (1,3)
AND do.device = c.callee_Device and (do.device in ('8760') )
AND c.start_Time_Yy=2008 and c.start_Time_Mm=2 and c.start_Time_Dd=18
order by c.start_Time desc)
union
(
select di.id,b.start_Time, b.ani, b.calling_Device, b.callee_Device, b.answer_Duration,
b.call_Type, b.ani_Area, b.undesktop_Notify_Missed_Call FROM Call_detail as b, Device_Info di
WHERE b.answer_Duration=0 AND di.apply_State=2 and b.call_Type in (1,3)
AND di.device = c.callee_Device and (di.device in ('8760') )
AND b.start_Time_Yy=2008 and b.start_Time_Mm=2 and b.start_Time_Dd=18
order by b.start_Time desc
)


里面 call_mater 和 call_detail 表的结构是一样的

我想连接两个select 用Union 老是出错 应该改成什么样啊
...全文
304 点赞 收藏 6
写回复
6 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
andrew_zx 2008-03-25
谢谢 熊,

ojuju10 你的这么写order by还是会出错的
回复
ojuju10 2008-03-25


---加上top 100 percent
(select top 100 percent do.id, c.start_Time, c.ani, c.calling_Device, c.callee_Device, c.answer_Duration,
c.call_Type, c.ani_Area, c.undesktop_Notify_Missed_Call FROM Call_Master as c, Device_Info do
WHERE c.answer_Duration=0 AND do.apply_State=2 and c.call_Type in (1,3)
AND do.device = c.callee_Device and (do.device in ('8760') )
AND c.start_Time_Yy=2008 and c.start_Time_Mm=2 and c.start_Time_Dd=18
order by c.start_Time desc)
union
(
select top 100 percent di.id,b.start_Time, b.ani, b.calling_Device, b.callee_Device, b.answer_Duration,
b.call_Type, b.ani_Area, b.undesktop_Notify_Missed_Call FROM Call_detail as b, Device_Info di
WHERE b.answer_Duration=0 AND di.apply_State=2 and b.call_Type in (1,3)
AND di.device = c.callee_Device and (di.device in ('8760') )
AND b.start_Time_Yy=2008 and b.start_Time_Mm=2 and b.start_Time_Dd=18
order by b.start_Time desc
)

回复
dobear_0922 2008-03-25
select do.id, c.start_Time as st, c.ani, c.calling_Device, c.callee_Device, c.answer_Duration, 
c.call_Type, c.ani_Area, c.undesktop_Notify_Missed_Call
FROM Call_Master as c, Device_Info do
WHERE c.answer_Duration=0 AND do.apply_State=2 and c.call_Type in (1,3)
AND do.device = c.callee_Device and (do.device in ('8760') )
AND c.start_Time_Yy=2008 and c.start_Time_Mm=2 and c.start_Time_Dd=18
union
select di.id,b.start_Time as st, b.ani, b.calling_Device, b.callee_Device, b.answer_Duration,
b.call_Type, b.ani_Area, b.undesktop_Notify_Missed_Call
FROM Call_detail as b, Device_Info di
WHERE b.answer_Duration=0 AND di.apply_State=2 and b.call_Type in (1,3)
AND di.device = c.callee_Device and (di.device in ('8760') )
AND b.start_Time_Yy=2008 and b.start_Time_Mm=2 and b.start_Time_Dd=18
order by st desc
回复
andrew_zx 2008-03-25
还是出错

消息 156,级别 15,状态 1,第 10 行
关键字 'order' 附近有语法错误。
消息 156,级别 15,状态 1,第 24 行
关键字 'order' 附近有语法错误。


难道union连接结果集除了结构列一样,有什么需要主要的吗
回复
wlinglong 2008-03-25
(select do.id as a, c.start_Time as b, c.ani as c, c.calling_Device as d, c.callee_Device as e, c.answer_Duration as f,
c.call_Type as g, c.ani_Area as h, c.undesktop_Notify_Missed_Call as i
FROM Call_Master as c, Device_Info do
WHERE c.answer_Duration=0 AND do.apply_State=2 and c.call_Type in (1,3)
AND do.device = c.callee_Device and (do.device in ('8760') )
AND c.start_Time_Yy=2008 and c.start_Time_Mm=2 and c.start_Time_Dd=18
order by c.start_Time desc)
union
(
select di.id as a,b.start_Time as b, b.ani as c, b.calling_Device as d, b.callee_Device as e, b.answer_Duration as f,
b.call_Type as g, b.ani_Area as h, b.undesktop_Notify_Missed_Call as i


FROM Call_detail as b, Device_Info di



WHERE b.answer_Duration=0 AND di.apply_State=2 and b.call_Type in (1,3)
AND di.device = c.callee_Device and (di.device in ('8760') )
AND b.start_Time_Yy=2008 and b.start_Time_Mm=2 and b.start_Time_Dd=18
order by b.start_Time desc
)
回复
wangjianming45 2008-03-25
UnionUnion All不但要求筛选列列一致,还有很重要一点(我们平时都没有太注意到的)就是字段的COLLATE也必须一致。你试着看看两个表字段的COLLATE是否一致?
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-03-25 10:58
社区公告
暂无公告