各位大神，怎么在sql语句union中使用order by？

各位大神，怎么在sql语句union中使用order by？

select * from (
select u.*, r.statusname, p.*, c.*
from UserLogin U, RepairStatus R, Product P, Customer C
where U.status = R.statusid
and U.ProductName = P.ProductName
and U.BoatName = C.BoatName
and U.RepairDate <= '2011-05-25'
and R.statusname = '待维修'
union
select u.*, r.statusname, p.*, c.*
from UserLogin U, RepairStatus R, Product P, Customer C
where U.status = R.statusid
and U.ProductName = P.ProductName
and U.BoatName = C.BoatName
and U.RepairDate <= '2011-05-25'
and R.statusname = '待检测'
) a
order by RepairDate desc追问最外面的select * ，提示为明确定义的列

数据库是oralce
我想这个应该不是问题，
sql语句该是一样的追答可能是你的几个表，列名有重复导致的，
比如UserLogin 表有RepairDate 字段，Product 表也有RepairDate 字段造成的
在内层的select不要用*，改用具体的列名

--我举个例子你就懂了
CREATE TABLE ts
(
id INT,
VALUE INT
)
GO
INSERT ts
SELECt 1,2 UNIOn ALL
SELECT 1,3 UNIOn ALL
SELECT 1,2 UNIOn ALL
SELECT 2,4 UNIOn ALL
SELECT 2,21 UNIOn ALL
SELECT 3,2 UNIOn ALL
SELECT 3,2
GO
SELECT id ,sum(value) AS s_v
FROM ts
GROUP BY id
ORDER BY sum(VALUE)


就是通过分组聚合后 对分组元素进行排序
本例中就是对ID分组 然后按照id对应的value总和进行排序

试试把order by 提出来。
（select u.*, r.statusname, p.*, c.*
from UserLogin U, RepairStatus R, Product P, Customer C
where U.status = R.statusid
and U.ProductName = P.ProductName
and U.BoatName = C.BoatName
and U.RepairDate <= '2011-05-25'
and R.statusname = '待维修'
union

select u.*, r.statusname, p.*, c.*
from UserLogin U, RepairStatus R, Product P, Customer C
where U.status = R.statusid
and U.ProductName = P.ProductName
and U.BoatName = C.BoatName
and U.RepairDate <= '2011-05-25'
and R.statusname = '待检测'）
order by u.RepairDate desc

如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！

点此我要举报以上问答信息