怎么连接两个select语句

[andrew_zx]

(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 老是出错 应该改成什么样啊

[wangjianming45]

UnionUnion All不但要求筛选列列一致，还有很重要一点（我们平时都没有太注意到的）就是字段的COLLATE也必须一致。你试着看看两个表字段的COLLATE是否一致？

[wlinglong]

(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
)

[andrew_zx]

还是出错

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


难道union连接结果集除了结构列一样，有什么需要主要的吗

[dobear_0922]

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 
 

[ojuju10]

---加上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 
)  

[andrew_zx]

谢谢 熊，

ojuju10 你的这么写order by还是会出错的