sql语句中union不能和order by同时用吗？(30分)

I

ifcansee

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-21

• #1

select * from table1.dbf where name=1 order by a union select * from table1.dbf
where name=2 order by a
这样不能运行，union 是不是不能和order by同时用，我要想实现上面语句要求的结果，
应该怎么做？

 

B

bes96261

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-21

• #2

select *
from (select a.* from tablea union select * from tableb ) as V
order by 字段名，字段名 --請不要再加別名

 

I

ifcansee

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-21

• #3

中间一共需要43个union，该如何做？
另外union能和group by 同时用吗？

 

I

ifcansee

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-21

• #4

我想实现的结果是取name=1的按照a字段排序的前5个
取name=2的按照a字段排序的前5个
取name=3的按照a字段排序的前5个
取name=4的按照a字段排序的前5个
取name=5的按照a字段排序的前5个
....
我用的sql语句是
select top 5 * from table1.dbf where name=1 order by a desc union
select top 5 * from table1.dbf where name=2 order by a desc union
select top 5 * from table1.dbf where name=3 order by a desc union
select top 5 * from table1.dbf where name=4 order by a desc union
select top 5 * from table1.dbf where name=5 order by a desc union
.....
不过不能执行，应该是union不能和order by同时用吧，可是我该怎么做实现这样的结果？

 

小

小猪

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-21

• #5

只能在最后一段sql后面加order by.

 

I

ifcansee

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-21

• #6

那不是所有的结果排序了吗？这不是我想要的结果

 

人

人在昆明

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-21

• #7

select a1 from
( select a1 a1 .... union select a1 a1.... )
order by a1

 

E

eyangyj

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-21

• #8

试使所有SQL语句Union最后加Order By 字段序号,...我记得Oracle数据库这样可以

 

W

wm1010

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-21

• #9

是呀, 只能在最後一sql語句中用order by ,且每個sql語句中的field的個數及field name都要
相同才行.

 

小

小猪

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-21

• #10

迂腐!!!!
see following:

select top 5 * from table1.dbf where name=1
select top 5 * from table1.dbf where name=2
select top 5 * from table1.dbf where name=3
select top 5 * from table1.dbf where name=4
select top 5 * from table1.dbf where name=5
order by name,a desc

 

I

ifcansee

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-21

• #11

小猪，结果是明显不对的

 

R

Richard3000

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-21

• #12

order by 和 Group by都只能用在最後

 

I

ifcansee

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-21

• #13

请注意，要取每个select中的排序过后的前5个数据。小猪的做法是取了前5个数据然后才
排序的，结果是不对的

 

B

blue_morning

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-21

• #14

我看到你的另一个问题了。如果你的union的name是从另一个表选出来的话
建议你使用cursor来循环向临时表中插入数据。

 

I

ifcansee

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-21

• #15

除了用临时表，直接用sql语句不能实现码

 

小

小猪

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-22

• #16

唉,都写到这份儿上了,有错自己改改也行啊.

select * from
(select top 5 * from table1.dbf where name=1 order by a desc)
union
select * from
(select top 5 * from table1.dbf where name=2 order by a desc)
union
select * from
(select top 5 * from table1.dbf where name=3 order by a desc)
union
select * from
(select top 5 * from table1.dbf where name=4 order by a desc)
union
select * from
(select top 5 * from table1.dbf where name=5 order by a desc)
order by name,a desc

(这个查询效率太低了,我觉得你需要重新考虑一下你的查询要求究竟有没有必要
作这样的查询)

 

I

ifcansee

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-22

• #17

谢谢，我在本地建了一个临时表格

 

I

ifcansee

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-22

• #18

接受答案了.