超难的sql语句，关于union all

[willbrow]

有两个表，一个是接受短信表一个是发送的表，想由这两个表得出聊天记录，以一个手机号为例
select detail,mobile,timee from(
select detail,mobile ,sendtime as timee from sendsms where mobile like (cast 13815002455 as nvarchar
union all
select detail,mobile ,recetime as timee from recesms where mobile like
(cast 13815002455 as nvarchar))a order by timee
最后加了order by timee就出错了，不能有order by嘛？但这个必须要以时间来排序的，怎么弄呢？甚至开始怀疑人生。。。

[willbrow]

顶一下，可有人知呀

[jinjazz]

select detail,mobile,timee from
(select detail,mobile ,sendtime as timee from sendsms where mobile ='13815002455'
union all
select detail,mobile ,recetime as timee from recesms where mobile ='13815002455' )a
order by timee

[spring_504]

select detail,mobile,timee from(
select detail,mobile ,sendtime as timee from sendsms where mobile like (cast 13815002455 as nvarchar)
union all
select detail,mobile ,recetime as timee from recesms where mobile like
(cast 13815002455 as nvarchar))a order by timee
上面的一个cast少了一个括号吧！

[willbrow]

不是这个原因没加挎号是笔误，这个句子用了order by timee就出错，不用就是对的，sendtime和recetime的格式都是smalldatetime的

[spring_504]

时间的格式确定是 yyyy-MM-dd ？

[willbrow]

select detail,mobile,timee from(
select detail,mobile ,sendtime as timee from sendsms where mobile like cast (13817002487 as nvarchar)
union all
select detail,mobile ,recetime as timee from recesms where mobile like cast (13817002487 as nvarchar))a order by timee
服务器: 消息 8626，级别 16，状态 1，行 1
在工作表中只允许有 text 指针，而决不允许有 text、ntext 或 image 列。查询处理器生成的查询计划要求在工作表中有 text、ntext 或 image 列。

[willbrow]

时间格式如2005-03-09 11:58:00 表的时间字段不存在问题，因为单独搜各个表都是正确的

[yongyupost2000]

两个表的结构完全一样吗？（包括数据类型）

[willbrow]

是的，一样的，结构完全一样

[LIHY70]

同意jinjazz

[talantlee]

select detail,mobile,timee from(
select detail,mobile ,sendtime as timee from sendsms where mobile like cast( 13815002455 as nvarchar)) as T
union all
select detail,mobile ,recetime as timee from recesms where mobile like
cast (13815002455 as nvarchar) order by timee

[天地客人]

UP

[NewQger]

把排序加到括号里边去：
select detail,mobile,timee from(（
select detail,mobile ,sendtime as timee from sendsms where mobile like (cast 13815002455 as nvarchar)
union all
select detail,mobile ,recetime as timee from recesms where mobile like
(cast 13815002455 as nvarchar)）order by timee)a

[lishengyu]

select detail,mobile ,sendtime as timee from sendsms
where mobile like ='13815002455'
union all
select detail,mobile ,recetime as timee from recesms
where mobile like ='13815002455' order by timee