Почему XMLAGG дает мне другой порядок, чем мой запрос, хотя у них одинаковый ORDER BY?
Я не могу понять, как получить мой XML в том же порядке, что и мой запрос. Я пробовал использовать CONVERT(file_name, 'US7ASCII') вместе с LOWER, чтобы игнорировать акцентированные символы и прописные буквы, что сделало его лучше, но все равно это другое.
Я тоже пытался использовать COLLATE, но, возможно, я просто не понимаю, как это работает и как его использовать, потому что оно абсолютно ничего не дало.
Так что же мне не хватает? Как я могу заставить XMLAGG дать мне тот же результат, что и запрос?
В идеале я бы предпочел не изменять этот запрос, если только нет способа сгенерировать XML в том же порядке.
SELECT id, file_name
FROM test_files
ORDER BY file_name;
Результат:
ПРИМЕЧАНИЕ. Это касается CONVERT и LOWER, которые наиболее близки к желаемому результату.
SELECT XMLELEMENT("files",
XMLAGG(XMLELEMENT("file",
XMLFOREST(id AS "id",
file_name AS "file_name"))
ORDER BY LOWER(CONVERT(file_name, 'US7ASCII'))))
FROM test_files;
Результат:
<files>
<file><id>22</id><file_name>!.ppt</file_name></file>
<file><id>31</id><file_name>!0.ppt</file_name></file>
<file><id>11</id><file_name>!é.ppt</file_name></file>
<file><id>23</id><file_name>_.ppt</file_name></file>
<file><id>32</id><file_name>_0.ppt</file_name></file>
<file><id>12</id><file_name>_é.ppt</file_name></file>
<file><id>4</id> <file_name>doc-01.doc</file_name></file>
<file><id>5</id> <file_name>Doc-02.doc</file_name></file>
<file><id>6</id> <file_name>doc-03.doc</file_name></file>
<file><id>1</id> <file_name>docx-01.docx</file_name></file>
<file><id>2</id> <file_name>Docx-02.docx</file_name></file>
<file><id>3</id> <file_name>docx-03.docx</file_name></file>
<file><id>7</id> <file_name>E .ppt</file_name></file>
<file><id>8</id> <file_name>e .ppt</file_name></file>
<file><id>9</id> <file_name>É .ppt</file_name></file>
<file><id>10</id><file_name>é .ppt</file_name></file>
<file><id>13</id><file_name>E.ppt</file_name></file>
<file><id>14</id><file_name>e.ppt</file_name></file>
<file><id>15</id><file_name>É.ppt</file_name></file>
<file><id>16</id><file_name>é.ppt</file_name></file>
<file><id>17</id><file_name>e0.ppt</file_name></file>
<file><id>18</id><file_name>é0.ppt</file_name></file>
<file><id>19</id><file_name>pdf-01.pdf</file_name></file>
<file><id>20</id><file_name>pdf-02.pdf</file_name></file>
<file><id>21</id><file_name>pdf-03.pdf</file_name></file>
<file><id>30</id><file_name>ppt-01.ppt</file_name></file>
<file><id>24</id><file_name>ppt-e .ppt</file_name></file>
<file><id>25</id><file_name>ppt-é .ppt</file_name></file>
<file><id>26</id><file_name>ppt-e.ppt</file_name></file>
<file><id>27</id><file_name>ppt-é.ppt</file_name></file>
<file><id>28</id><file_name>ppt-e0.ppt</file_name></file>
<file><id>29</id><file_name>ppt-é0.ppt</file_name></file>
</files>
Мне удалось заставить мой базовый запрос дать мне тот же порядок, что и запрос XML, добавив COLLATE binary_ai в предложение order by. Однако я бы все же предпочел не вносить никаких изменений в мой основной запрос.
Я также был бы признателен за объяснение того, почему ORDER BY внутри XMLAGG не ведет себя так же, как при запросе.
🤔 А знаете ли вы, что...
SQL имеет стандарт ANSI SQL, который определяет базовый синтаксис для всех реляционных баз данных.
Судя по порядку, в котором отображаются результаты вашего исходного запроса, для NLS_SORT и NLS_COMP установлено значение BINARY по умолчанию, а для NLS_LANGUAGE установлено значение французского (спасибо за упоминание вашего языка в вашем профиле!).
Похоже, что предложение order by в XMLAGG не учитывает настройки NLS.
Вместо того, чтобы пытаться манипулировать значениями вручную для упорядочения, вы можете использовать функцию NLSSORT:
XMLAGG(... ORDER BY NLSSORT(file_name, 'NLS_SORT=XFRENCH'))
Используя это и добавив XMLSERIALIZE только для форматирования для удобства чтения, вы получите:
SELECT XMLSERIALIZE(DOCUMENT XMLELEMENT("files",
XMLAGG(XMLELEMENT("file",
XMLFOREST(id AS "id",
file_name AS "file_name"))
ORDER BY NLSSORT(file_name, 'NLS_SORT=XFRENCH'))) INDENT SIZE = 2)
FROM test_files;
Вы также можете упорядочить результаты запроса перед агрегированием:
SELECT XMLSERIALIZE(DOCUMENT XMLELEMENT("files",
XMLAGG(XMLELEMENT("file",
XMLFOREST(id AS "id",
file_name AS "file_name"))
)) INDENT SIZE = 2)
FROM test_files
ORDER BY file_name;
... который Oracle рекомендует в примечании о поддержке 1407946.1; но я не знаю, действительно ли гарантирован порядок элементов, если вы это сделаете. Возможно, это так, но я могу представить себе будущие изменения в оптимизаторе или, может быть, просто параллельный запрос, который на него повлияет.