我想要得到在两个特定列之间的列的值

I have a table in which I have to select values of columns which are between two columns. Suppose column1, column2, column3 are between column_start and column_end in table1. Columns between column_start and column_end gets added as required.

我有一个表，我必须选择两个列之间的列的值。假设表1中的column1、column2、column3在column_start和column_end之间。根据需要添加column_start和column_end之间的列。

table1

表1

column_start | column1 | column2 | column3 | column_end
-------------------------------------------------------
     1       |    2    |    3    |    4    |     5
     8       |    5    |    6    |    4    |     2

Is it possible to get these column1, column2, column3 values?

有可能得到这些列n1，第n2，第3列的值吗?

Output:

输出:

 column1 | column2 | column3
----------------------------  
   2     |    3    |    4     
   5     |    6    |    4

3 个解决方案

#1

If I understand correctly, I don't think you can do it with a single query. You can try using the following. Added comment to the code.

如果我理解正确，我认为单凭一个查询是不行的。您可以尝试使用以下方法。添加注释到代码中。

SET @schema = 'database_name_here'; -- Database name
SET @table = 'table1';              -- Table name
SET @startColumn = 'column_start';
SET @endColumn = 'column_end';

-- First get the position of both columns
SELECT ORDINAL_POSITION FROM information_schema.COLUMNS 
    WHERE table_schema=@schema AND table_name=@table AND 
    column_name=@startColumn INTO @StartPosition;

SELECT ORDINAL_POSITION FROM information_schema.COLUMNS 
    WHERE table_schema=@schema AND table_name=@table AND 
    column_name=@endColumn INTO @endPosition;

-- get the column names from the position
SELECT GROUP_CONCAT(column_name) from information_schema.COLUMNS
    where table_schema=@schema AND table_name=@table
    AND ORDINAL_POSITION > @StartPosition AND ORDINAL_POSITION < @endPosition 
    INTO @colname;

-- create a sql statement prepare and execute
SET @query = CONCAT('SELECT ',@colname,' FROM ', @table);
PREPARE stmt FROM @query;
EXECUTE stmt;

#2

SELECT column1, column2, column3
FROM yourTable
WHERE
    column1 > column_start AND column1 < column_end AND
    column2 > column_start AND column2 < column_end AND
    column3 > column_start AND column3 < column_end

#3

Try this:

试试这个:

SELECT CASE WHEN column_start < column_end AND column1 BETWEEN column_start AND column_end THEN column1 
            WHEN column_end < column_start AND column1 BETWEEN column_end AND column_start THEN column1 
            ELSE NULL 
       END AS column1, 
       CASE WHEN column_start < column_end AND column2 BETWEEN column_start AND column_end THEN column2
            WHEN column_end < column_start AND column2 BETWEEN column_end AND column_start THEN column2 
            ELSE NULL 
       END AS column2,
       CASE WHEN column_start < column_end AND column3 BETWEEN column_start AND column_end THEN column3
            WHEN column_end < column_start AND column3 BETWEEN column_end AND column_start THEN column3 
            ELSE NULL 
       END AS column3
FROM table1;

#1