主页 / dba / 问题 / 50460
Accepted
Praveen Prasannan
Asked: 2013-09-25 23:50:59 +0800 CST

按顺序排序，然后是 Concat

  • 772

我有一张table1像这样的桌子：

col1  col2

A1    A2
A1    B1
A1    B2
A2    A1
A2    B1
A2    B2
B1    A1
B1    A2
B1    B2
B2    A1
B2    A2
B2    B1

我需要 concatcol1并且col2应该首先对其进行排序。

预期结果：

col1  col2  ConcatResult

A1    A2    A1-A2
A1    B1    A1-B1
A1    B2    A1-B2
A2    A1    A1-A2
A2    B1    A2-B1
A2    B2    A2-B2
B1    A1    A1-B1
B1    A2    A2-B1
B1    B2    B1-B2
B2    A1    A1-B2
B2    A2    A2-B2
B2    B1    B1-B2

另一个要求：如果可能，我想选择与列不同的ConcatResult列。

mysql sorting

2 个回答

  1. Best Answer

    你可以做

    SELECT col1, col2,
       CONCAT(LEAST(col1, col2), '-', 
              GREATEST(col1, col2)) concat_result
  FROM table1
 ORDER BY concat_result

    输出：

    | COL1 | COL2 | CONCAT_RESULT |
|--------|------|----------------|
| A1 | A2 | A1-A2 |
| A2 | A1 | A1-A2 |
| B1 | A1 | A1-B1 |
| A1 | B1 | A1-B1 |
| A1 | B2 | A1-B2 |
| B2 | A1 | A1-B2 |
| A2 | B1 | A2-B1 |
| B1 | A2 | A2-B1 |
| A2 | B2 | A2-B2 |
| B2 | A2 | A2-B2 |
| B1 | B2 | B1-B2 |
| B2 | B1 | B1-B2 |

    从连接结果中获取不同的值

    SELECT DISTINCT 
       CONCAT(LEAST(col1, col2), '-', 
              GREATEST(col1, col2)) concat_result
  FROM table1
 ORDER BY concat_result

    输出：

    | CONCAT_RESULT |
|---------------|
| A1-A2 |
| A1-B1 |
| A1-B2 |
| A2-B1 |
| A2-B2 |
| B1-B2 |

    这是SQLFiddle演示

    • 2

  2. 可以通过CASE表情来完成。

    MySQL 还允许将字符串二进制表示与<,>和=运算符进行比较。另一种可能性是STRCMP()功能。

    SELECT
    `col1`,
    `col2`,
    CASE
        WHEN `col1` > `col2` THEN CONCAT_WS('-', `col2`, `col1`)
        WHEN `col1` < `col2` THEN CONCAT_WS('-', `col1`, `col2`)
    END as `result`
FROM
    -- replace bottom part with table name.
    -- I've used UNION ALL for debugging.
    (
        SELECT 'A1' as `col1`, 'A2' as `col2` UNION ALL
        SELECT 'A1' as `col1`, 'B1' as `col2` UNION ALL
        SELECT 'A1' as `col1`, 'B2' as `col2` UNION ALL
        SELECT 'A2' as `col1`, 'A1' as `col2` UNION ALL
        SELECT 'A2' as `col1`, 'B1' as `col2` UNION ALL
        SELECT 'A2' as `col1`, 'B2' as `col2` UNION ALL
        SELECT 'B1' as `col1`, 'A1' as `col2` UNION ALL
        SELECT 'B1' as `col1`, 'A2' as `col2` UNION ALL
        SELECT 'B1' as `col1`, 'B2' as `col2` UNION ALL
        SELECT 'B2' as `col1`, 'A1' as `col2` UNION ALL
        SELECT 'B2' as `col1`, 'A2' as `col2` UNION ALL
        SELECT 'B2' as `col1`, 'B1' as `col2`
    ) `sub`

    结果：

    col1 col2 result
----------------
A1   A2   A1-A2
A1   B1   A1-B1
A1   B2   A1-B2
A2   A1   A1-A2
A2   B1   A2-B1
A2   B2   A2-B2
B1   A1   A1-B1
B1   A2   A2-B1
B1   B2   B1-B2
B2   A1   A1-B2
B2   A2   A2-B2
B2   B1   B1-B2

    例如DISTINCT，需要 exclude col1and col2fromSELECT语句：

    SELECT DISTINCT
    CASE
        WHEN `col1` > `col2` THEN CONCAT_WS('-', `col2`, `col1`)
        WHEN `col1` < `col2` THEN CONCAT_WS('-', `col1`, `col2`)
    END as `result`
FROM
    `YourTable`;

    结果：

    result
------
A1-A2
A1-B1
A1-B2
A2-B1
A2-B2
B1-B2
    • 1

相关问题