Oracle Union与order by子句问题

3 回答

• 2

  潜在的障碍是SQL数据集本质上是无序的 . 一旦您使用UNION两个数据集，您就会失去先前存在的订购保证 .

  您可以经常使用以下结构，但仍然无法保证......

  SELECT
      *
  FROM
  (
      (
          select to_char(first_name||'('||substr(last_name,0,1)||')') 
          from employees 
          order by first_name
      ) 
          Individuals
  
      UNION ALL
  
      (
          select to_char('There are total '||count(job_id)||' '||lower(job_id)||'s') 
          from employees
          group by job_id 
          order by count(job_id),job_id
      )
          Totals
  )
      Combined
  ;
  

  在实践中，您经常可以通过此结构获得所需内容 .

  括号确保在UNION ALL之前完成排序，并且数据库引擎PROBABLY不会按顺序进行操作 .

  但它可能 . 保证结果顺序的唯一方法是在外部查询上放置ORDER BY . 像下面这样的东西通常工作得很好......

  SELECT
      rowdata
  FROM
  (
      (
          select 1 AS myset, rownum AS myrow, to_char(first_name||'('||substr(last_name,0,1)||')') AS rowdata
          from employees 
          order by first_name
      ) 
          Individuals
  
      UNION ALL
  
      (
          select 2 AS myset, rownum AS myrow, to_char('There are total '||count(job_id)||' '||lower(job_id)||'s') 
          from employees
          group by job_id 
          order by count(job_id),job_id
      )
          Totals
  )
      Combined
  ORDER BY
      myset, myrow
  ;
  

  我在打电话，所以可能会有错别字，但情绪就在那里......

  使用ROWNUM或ROW_NUMBER（）在数据集中生成额外的字段 . 然后结合他们 . 然后按新字段排序 .

  回复于 2024-05-09T06:53:49+08:00
• 1

  这样做的方法通常是提供一个标识“union group”的列，其中应该出现一行：

  select name 
  from (
    select first_name||'('||substr(last_name,0,1)||')' as name, 
           1 as sort_order, 
           0 as counter, 
           0 as job_id
    from employees 
    union all 
    select 'There are total '||count(job_id)||' '||lower(job_id)||'s', 
           2, 
           count(job_id), 
           job_id
    from employees
    group by job_id 
  ) t
  order by sort_order, counter, job_id, name;
  

  由于您还希望通过不同的列对第二部分内的行进行排序，因此需要为其创建虚拟列，以便可以对整个结果应用正确的 order by .

  我还从您的查询中删除了无用的 to_char() 调用 .

  如果 job_id 实际上是一个 varchar 列（ lower(job_id) 似乎表示），则需要将 0 as job_id 替换为某个字符常量 . 它的 Value 并不重要 .

  在线示例：http://rextester.com/ZUPQ98121

  回复于 2024-05-09T06:53:49+08:00
• 1

  它应该是工作

  select to_char(first_name||'('||substr(last_name,0,1)||')') 
          from (select first_name, last_name from employees order by first_name)
      union
      select to_char('There are total '||cnt||' '||lower(job_id)||'s')
          from (select count(job_id) cnt, job_id from employees group by job_id 
  order by count(job_id), job_id)
  

  回复于 2024-05-09T06:53:49+08:00