代码之家 › 专栏 › 技术社区 › Marquake

ORDER BY值在Generics SQL和Informix中的子句“in()”中指定

informix sql

Marquake · 技术社区 · 9 年前

我希望不要再重复另一个问题……我找不到解决方案。所有解决方案都适用于MySql。

我想根据子句in中的值对查询结果进行排序。但在互联网和stackoverflow中,只需找到“MySql”的解决方案,如下所示:

Order by FIELD in MYSQL

Ordering by specific field value first

MySQL ORDER BY FIELD with %

使用子句FIELD或FIELD_IN_SET。我可以用类似的东西吗。

谢谢

我只是试着…但没用。这是我的疑问。

select * from (
    select 4 as dato1 from systables where tabid = 1 union
    select 2 as dato1 from systables where tabid = 1 union
    select 1 as dato1  from systables where tabid = 1 union
    select 3 as dato1  from systables where tabid = 1 
)
where dato1 in (4,2,1,3)
order by instr('4,2,1,3', dato1)

这是查询显示的:

dato1
1
2
4
3

我不会轻描淡写。。。

3 回复 | 直到 7 年前

Gordon Linoff 9 年前

一种适用于许多数据库的方法如下:

where x in ('a', 'b', 'c')
order by instr('a,b,c', x)

当然,分隔符可能会导致问题,因此这更安全:

where x in ('a', 'b', 'c')
order by instr(',a,b,c,', ',' || x || ',', )

Luís Marques 9 年前

对于informix 12.10(开发者版) INSTR() 似乎没有正确地将函数参数转换为字符类型。

我给VARCHAR和 INSTR() 函数开始返回正确的值。

select
    dato1
from (
    select 4 as dato1 from systables where tabid = 1 union
    select 2 as dato1 from systables where tabid = 1 union
    select 1 as dato1  from systables where tabid = 1 union
    select 3 as dato1  from systables where tabid = 1
)
where dato1 in (4,2,1,3)
order by instr('4,2,1,3', CAST(dato1 AS VARCHAR(255)))

退货:

编辑:

阐明 INSTR() 功能:

select
    dato1
  , instr('4213', CAST(dato1 AS VARCHAR(255))) AS position
from (
    select 4 as dato1 from systables where tabid = 1 union
    select 2 as dato1 from systables where tabid = 1 union
    select 1 as dato1 from systables where tabid = 1 union
    select 3 as dato1 from systables where tabid = 1
)
where dato1 in (4,2,1,3)
order by instr('4213', CAST(dato1 AS VARCHAR(255)))

退货:

dato1    position
    4           1
    2           2
    1           3
    3           4

话虽如此 DECODE() 里卡多的建议似乎是一个更好的选择。

Community CDub 7 年前

此答案仅用于解释 @luÃs-marques 和 @gordon-linoff .

自从版本11.70以来,informix INSTR 函数,该函数获取字符串并查找一个子字符串(也是给定的),并返回该字符串中出现该子字符串的位置( 国际商用机器公司 文档说这是结束,但这是一个文档错误)。

您使用的解决方案是:

SELECT
    dato1
FROM (
    SELECT 4 AS dato1 FROM systables WHERE tabid = 1 UNION
    SELECT 2 AS dato1 FROM systables WHERE tabid = 1 UNION
    SELECT 1 AS dato1 FROM systables WHERE tabid = 1 UNION
    SELECT 3 AS dato1 FROM systables WHERE tabid = 1
)
WHERE dato1 IN (4,2,1,3)
ORDER BY INSTR('4,2,1,3', CAST(dato1 AS VARCHAR(255)));

要了解正在发生的事情,您可以:

SELECT
    dato1,
    INSTR('4,2,1,3', CAST(dato1 AS VARCHAR(255))) AS instr_res
FROM (
    SELECT 4 AS dato1 FROM systables WHERE tabid = 1 UNION
    SELECT 2 AS dato1 FROM systables WHERE tabid = 1 UNION
    SELECT 1 AS dato1 FROM systables WHERE tabid = 1 UNION
    SELECT 3 AS dato1 FROM systables WHERE tabid = 1
)
WHERE dato1 IN (4,2,1,3)
ORDER BY 2;

这将输出:

  dato1   instr_res

      4           1
      2           3
      1           5
      3           7

但请记住,您可能会在使用流量计时遇到问题 @戈登·林诺夫 .

例如:

    SELECT
            dato1,
            INSTR('444,44,4', CAST(dato1 AS VARCHAR(255))) AS instr_res
    FROM (
            SELECT   4 AS dato1 FROM systables WHERE tabid = 1 UNION
            SELECT  44 AS dato1 FROM systables WHERE tabid = 1 UNION
            SELECT 444 AS dato1 FROM systables WHERE tabid = 1
    )
    WHERE dato1 IN (444,44,4)
    ORDER BY 2;

  dato1   instr_res

      4           1
     44           1
    444           1

要进行排序,请始终分隔所需值的开始和结束,在这种情况下,将是:

    SELECT
            dato1,
            INSTR(',444,44,4,', ','||CAST(dato1 AS VARCHAR(255))||',') AS instr_res
    FROM (
            SELECT   4 AS dato1 FROM systables WHERE tabid = 1 UNION
            SELECT  44 AS dato1 FROM systables WHERE tabid = 1 UNION
            SELECT 444 AS dato1 FROM systables WHERE tabid = 1
    )
    WHERE dato1 IN (444,44,4)
    ORDER BY 2;

  dato1   instr_res

    444           1
     44           5
      4           8

另一种方法是使用 CHARINDEX 函数,从11.70开始也可用。请注意参数的顺序是相反的;首先传递要查找的子字符串,然后传递源字符串。使用的好处 CHARINDEX 不需要铸造。

SELECT
        dato1,
        CHARINDEX(','||dato1||',', ',444,4,44,') AS charindex_res
FROM (
        SELECT   4 AS dato1 FROM systables WHERE tabid = 1 UNION
        SELECT  44 AS dato1 FROM systables WHERE tabid = 1 UNION
        SELECT 444 AS dato1 FROM systables WHERE tabid = 1
)
WHERE dato1 IN (444,4,44)
ORDER BY 2;

  dato1 charindex_res

    444             1
      4             5
     44             7

当使用旧版本的Informix时 INSTR 可以使用 DECODE :

SELECT
    dato1
FROM (
    SELECT 4 AS dato1 FROM systables WHERE tabid = 1 UNION
    SELECT 2 AS dato1 FROM systables WHERE tabid = 1 UNION
    SELECT 1 AS dato1 FROM systables WHERE tabid = 1 UNION
    SELECT 3 AS dato1 FROM systables WHERE tabid = 1
)
WHERE dato1 IN (4,2,1,3)
ORDER BY DECODE(
            dato1, 
            4, 1,   
            2, 2, 
            1, 3, 
            3, 4
);