别名是否要在声明它们的 Select 之外使用? [英] Are Aliases to be used outside the Select in which they are declared?

问题描述

everyone. I have a table with one of the fields (seq) declared as varchar(14), containing a string of numbers (five 2digits numbers separated by whitespaces). I'd like to get, for each row, the 5 possible combination of 4 of those numbers Union All into a single column. My first attempt leads to the following query, which 'should' work (although i admit it is 'a bit' awful!) :

SELECT id,ordered_seq FROM(
    SELECT id,
           seq,
           MIN(SUBSTR(seq,1,2),SUBSTR(seq,4,2),SUBSTR(seq,7,2),SUBSTR(seq,10,2),SUBSTR(seq,13,2)) AS A1,
           MAX(SUBSTR(seq,1,2),SUBSTR(seq,4,2),SUBSTR(seq,7,2),SUBSTR(seq,10,2),SUBSTR(seq,13,2)) AS E1,
           trim(replace(replace(replace(replace(seq,A1,''),'  ',' '),E1,''),'  ','')) AS T1,

           MIN(SUBSTR(T1,1,2),SUBSTR(T1,4,2),SUBSTR(T1,7,2)) AS B1,
           MAX(SUBSTR(T1,1,2),SUBSTR(T1,4,2),SUBSTR(T1,7,2)) AS D1,
           replace(replace(replace(T1,B1,''),D1,''),'  ','') AS C1,
           A1||' '||B1||' '||C1||' '||D1||' '||E1 AS ordered_seq
    FROM source_table
)
WHERE(seq<>'00 00 00 00 00')

This one should return the original column where each row is sorted in ascending order, while providing 'indexes' for the final part of the job: selecting all the possible combinations of 4 numbers per row, as i said before. Problem is, i get a 'no such column' error on first attempt to use an alias inside the select where it has been declared (i.e. select col1 as c1, count(c1) raises 'no such column : C1' error). Is this a normal behaviour?

My usual luck.

Updated query -----------------------------

SELECT id, fir||' '||sec||' '||thi||' '||fou||' '||fif AS ordered_list 
FROM (
   SELECT id,
          Date,
          seq,
          MIN(SUBSTR(seq,1,2),SUBSTR(seq,4,2),SUBSTR(seq,7,2),SUBSTR(seq,10,2),SUBSTR(seq,13,2)) AS fir,
          MIN( SUBSTR(trim(replace(replace(replace(replace(seq,MIN(SUBSTR(seq,1,2),SUBSTR(seq,4,2),SUBSTR(seq,7,2),SUBSTR(seq,10,2),SUBSTR(seq,13,2)),''),'  ',' '),MAX(SUBSTR(seq,1,2),SUBSTR(seq,4,2),SUBSTR(seq,7,2),SUBSTR(seq,10,2),SUBSTR(seq,13,2)),''),'  ',' ')),1,2), SUBSTR(trim(replace(replace(replace(replace(seq,MIN(SUBSTR(seq,1,2),SUBSTR(seq,4,2),SUBSTR(seq,7,2),SUBSTR(seq,10,2),SUBSTR(seq,13,2)),''),'  ',' '),MAX(SUBSTR(seq,1,2),SUBSTR(seq,4,2),SUBSTR(seq,7,2),SUBSTR(seq,10,2),SUBSTR(seq,13,2)),''),'  ',' ')),4,2), SUBSTR(trim(replace(replace(replace(replace(seq,MIN(SUBSTR(seq,1,2),SUBSTR(seq,4,2),SUBSTR(seq,7,2),SUBSTR(seq,10,2),SUBSTR(seq,13,2)),''),'  ',' '),MAX(SUBSTR(seq,1,2),SUBSTR(seq,4,2),SUBSTR(seq,7,2),SUBSTR(seq,10,2),SUBSTR(seq,13,2)),''),'  ',' ')),7,2) ) AS sec,
          trim( replace(replace(trim(replace(replace(replace(replace(seq,MIN(SUBSTR(seq,1,2),SUBSTR(seq,4,2),SUBSTR(seq,7,2),SUBSTR(seq,10,2),SUBSTR(seq,13,2)),''),'  ',' '),MAX(SUBSTR(seq,1,2),SUBSTR(seq,4,2),SUBSTR(seq,7,2),SUBSTR(seq,10,2),SUBSTR(seq,13,2)),''),'  ',' ')),MIN( SUBSTR(trim(replace(replace(replace(replace(seq,MIN(SUBSTR(seq,1,2),SUBSTR(seq,4,2),SUBSTR(seq,7,2),SUBSTR(seq,10,2),SUBSTR(seq,13,2)),''),'  ',' '),MAX(SUBSTR(seq,1,2),SUBSTR(seq,4,2),SUBSTR(seq,7,2),SUBSTR(seq,10,2),SUBSTR(seq,13,2)),''),'  ',' ')),1,2), SUBSTR(trim(replace(replace(replace(replace(seq,MIN(SUBSTR(seq,1,2),SUBSTR(seq,4,2),SUBSTR(seq,7,2),SUBSTR(seq,10,2),SUBSTR(seq,13,2)),''),'  ',' '),MAX(SUBSTR(seq,1,2),SUBSTR(seq,4,2),SUBSTR(seq,7,2),SUBSTR(seq,10,2),SUBSTR(seq,13,2)),''),'  ',' ')),4,2), SUBSTR(trim(replace(replace(replace(replace(seq,MIN(SUBSTR(seq,1,2),SUBSTR(seq,4,2),SUBSTR(seq,7,2),SUBSTR(seq,10,2),SUBSTR(seq,13,2)),''),'  ',' '),MAX(SUBSTR(seq,1,2),SUBSTR(seq,4,2),SUBSTR(seq,7,2),SUBSTR(seq,10,2),SUBSTR(seq,13,2)),''),'  ',' ')),7,2) ),''),MAX( SUBSTR(trim(replace(replace(replace(replace(seq,MIN(SUBSTR(seq,1,2),SUBSTR(seq,4,2),SUBSTR(seq,7,2),SUBSTR(seq,10,2),SUBSTR(seq,13,2)),''),'  ',' '),MAX(SUBSTR(seq,1,2),SUBSTR(seq,4,2),SUBSTR(seq,7,2),SUBSTR(seq,10,2),SUBSTR(seq,13,2)),''),'  ',' ')),1,2), SUBSTR(trim(replace(replace(replace(replace(seq,MIN(SUBSTR(seq,1,2),SUBSTR(seq,4,2),SUBSTR(seq,7,2),SUBSTR(seq,10,2),SUBSTR(seq,13,2)),''),'  ',' '),MAX(SUBSTR(seq,1,2),SUBSTR(seq,4,2),SUBSTR(seq,7,2),SUBSTR(seq,10,2),SUBSTR(seq,13,2)),''),'  ',' ')),4,2), SUBSTR(trim(replace(replace(replace(replace(seq,MIN(SUBSTR(seq,1,2),SUBSTR(seq,4,2),SUBSTR(seq,7,2),SUBSTR(seq,10,2),SUBSTR(seq,13,2)),''),'  ',' '),MAX(SUBSTR(seq,1,2),SUBSTR(seq,4,2),SUBSTR(seq,7,2),SUBSTR(seq,10,2),SUBSTR(seq,13,2)),''),'  ',' ')),7,2) ),'')) AS thi,
          MAX( SUBSTR(trim(replace(replace(replace(replace(seq,MIN(SUBSTR(seq,1,2),SUBSTR(seq,4,2),SUBSTR(seq,7,2),SUBSTR(seq,10,2),SUBSTR(seq,13,2)),''),'  ',' '),MAX(SUBSTR(seq,1,2),SUBSTR(seq,4,2),SUBSTR(seq,7,2),SUBSTR(seq,10,2),SUBSTR(seq,13,2)),''),'  ',' ')),1,2), SUBSTR(trim(replace(replace(replace(replace(seq,MIN(SUBSTR(seq,1,2),SUBSTR(seq,4,2),SUBSTR(seq,7,2),SUBSTR(seq,10,2),SUBSTR(seq,13,2)),''),'  ',' '),MAX(SUBSTR(seq,1,2),SUBSTR(seq,4,2),SUBSTR(seq,7,2),SUBSTR(seq,10,2),SUBSTR(seq,13,2)),''),'  ',' ')),4,2), SUBSTR(trim(replace(replace(replace(replace(seq,MIN(SUBSTR(seq,1,2),SUBSTR(seq,4,2),SUBSTR(seq,7,2),SUBSTR(seq,10,2),SUBSTR(seq,13,2)),''),'  ',' '),MAX(SUBSTR(seq,1,2),SUBSTR(seq,4,2),SUBSTR(seq,7,2),SUBSTR(seq,10,2),SUBSTR(seq,13,2)),''),'  ',' ')),7,2) ) AS fou,
          MAX(SUBSTR(seq,1,2),SUBSTR(seq,4,2),SUBSTR(seq,7,2),SUBSTR(seq,10,2),SUBSTR(seq,13,2)) AS fif
   FROM source_table
)
WHERE (seq<>'00 00 00 00 00' AND Date BETWEEN '1939-01-07' AND '2012-12-24')

解决方案

Column aliases define names for the output of the SELECT.

To use them in the SELECT clause itself, you have to add another layer of indirection:

SELECT c1 FROM (SELECT 42 AS c1)

这篇关于别名是否要在声明它们的 Select 之外使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！