在MS Access SQL中订购联合查询 [英] Ordering a Union Query in MS Access SQL

问题描述

好的，我有一个特别讨厌的工会订购问题，因此将不胜感激.

OK I have a particularly nasty union ordering problem so any help would be appreciated.

场景是这样的:

具有以下记录(实际数据)的成员表:

Member Table with the following records (actual data):

REI882
YUI987
POBO37
NUBS26
BTBU12
MZBY10
TYBW54

(这些按我希望它们从我的查询中返回的顺序列出.)

(These are listed in the order I want them back from my query.)

关于这些MemberID的构造，有许多业务规则，我认为这些规则与排序无关.它们具有悠久的历史，并被定为坚石.我坚持他们.它们表示成员的资历.

There are a number of business rules about the construction of these MemberIDs which I believe are unrelated to the sort. They're historic and set in stone. I'm stuck with them. They indicate seniority of the member.

从ID的最后4个字符开始升序排列.就排序而言，ID的前两个字符完全没有意义.

The ordering is done from the last 4 characters in the ID, ascending. The first two characters of the ID are completely meaningless as far as the sort is concerned.

所以最高的记录是A001(最高级别)，最低的记录是ZZ99(最低级别).

So the topmost possible record is ??A001 (most senior) and the lowest possible record is ??ZZ99 (least senior).

当我查询成员表时，返回的列表必须在最上方显示最高级...显然，标准排序不起作用.这是我迄今为止要完成的事情:

When I query my member table the list I get back must display most senior at top... Obviously a standard sort does not work. This is what I have to date:

这些查询中的第一个查询处理ID仅有1个前导字母的排序成员.第二个是处理前两个字母的人.

The first of these queries deals with sorting members whose ID only has 1 leading letter. The second deals with those with 2 leading letters.

SELECT * FROM (

    SELECT Member.ID
    FROM Member
    WHERE (((IsNumeric(Mid([Member.ID],4,1)))=-1)) **check the 4th character is a digit
    ORDER BY (Mid([Member.ID],3,1)), (Mid([Member.ID],4,1)), (Mid([Member.ID],5,1)), (Mid([Member.ID],6,1))
) t1

UNION

SELECT * FROM (
    SELECT Member.ID
    FROM Member
    WHERE (((IsNumeric(Mid([Member.ID],4,1)))=0)) **check the 4th character is a letter
    ORDER BY (Mid([Member.ID],3,1)), (Mid([Member.ID],4,1)), (Mid([Member.ID],5,1)), (Mid([Member.ID],6,1))
) t2

但是我得到工会的疯狂结果！如果我分别运行每个选择-没问题，我的时髦(在很大程度上依赖于访问中一些讨厌的字符串操作！)排序完全可以按照我的要求进行.

But I get CRAZY results with the union! If I run each of the selects individually - no problem my funky (heavily reliant on some nasty string manipulation in access!) sort works exactly as I want it.

我知道这很复杂，但我希望我已经清楚地解释了这一点，并且有人愿意为弄清楚这一点而表示敬意！

I understand this is pretty complicated but I hope I've explained it clearly and that someone is up for some kudos for figuring it out!!!

我的查询结果似乎是随机的:

YUI987
MZBY10
NUBS26
BTBU12
REI882
POBO37
TYBW54

推荐答案

ORDER BY在SELECT语句中指出UNION与另一个SELECT不正确.

ORDER BY in a SELECT statement that UNION with another SELECT is not correct.

请参见指定条件订单 此处

您可以使用此:

SELECT ID FROM(  
(SELECT Member.ID,1 AS T,Left([Member.ID],2) AS Part1, Right([Member.ID],4) AS Part2
  FROM Member
  WHERE (((IsNumeric(Mid([Member.ID],3,1)))=-1)))    
UNION    
  (SELECT Member.ID,2 AS T,Left([Member.ID],3) AS Part1, Right([Member.ID],3) AS Part2
  FROM Member
  WHERE (((IsNumeric(Mid([Member.ID],4,1)))=-1) and ((IsNumeric(Mid([Member.ID],3,1)))=0)))     
UNION    
  (SELECT Member.ID,3 AS T,Left([Member.ID],4) AS Part1, Right([Member.ID],2) AS Part2
  FROM Member
  WHERE (((IsNumeric(Mid([Member.ID],5,1)))=-1) and ((IsNumeric(Mid([Member.ID],4,1)))=0)))     

ORDER BY T,Part1,Part2)

@Justin Kirk:我不知道您到底是什么问题.但我希望它能为您提供帮助

@Justin Kirk: I don't know what is your problem exactly. But I hope it can help you

这篇关于在MS Access SQL中订购联合查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！