主页 / dba / 问题 / 50510
Accepted
AaronJAnderson
Asked: 2013-09-26 09:15:23 +0800 CST

mysql ...比较和匹配同一列中的项目

  • 772

我有一列充满服务器名称。例如;

LouServer01
VgsServer01
LouServer02
VgsServer02

我需要/希望能够根据 Lou/Vgs 之后的所有内容创建一个可以匹配这两个名称的查询，并给我一些如下所示的内容：

Louisville  | Vegas
____________|____________
LouServer01 | VgsServer01
LouServer02 | VgsServer02
mysql

2 个回答

  1. 这是您需要的查询

    SELECT
    Louisville.name LouisvilleServer,
    Vegas.name VegasServer
FROM
(
    SELECT name,SUBSTR(name,LOCATE('0',name)) num FROM servers
    WHERE LEFT(name,LOCATE('0',name)-1)='LouServer'
) Louisville
INNER JOIN
(
    SELECT name,SUBSTR(name,LOCATE('0',name)) num FROM servers
    WHERE LEFT(name,LOCATE('0',name)-1)='VgsServer'
) Vegas
ON Louisville.num = Vegas.num;

    这是一个示例表

    mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS servers;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE servers
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     name VARCHAR(20),
    ->     PRIMARY KEY (id),
    ->     KEY (name)
    -> );
Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO servers (name) VALUES
    -> ('LouServer01'),('VgsServer01'),
    -> ('LouServer02'),('VgsServer02');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM servers;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | LouServer01 |
|  3 | LouServer02 |
|  2 | VgsServer01 |
|  4 | VgsServer02 |
+----+-------------+
4 rows in set (0.00 sec)

mysql>

    这是您需要执行的查询

    mysql> SELECT
    ->     Louisville.name LouisvilleServer,
    ->     Vegas.name VegasServer
    -> FROM
    -> (
    ->     SELECT name,SUBSTR(name,LOCATE('0',name)) num FROM servers
    ->     WHERE LEFT(name,LOCATE('0',name)-1)='LouServer'
    -> ) Louisville
    -> INNER JOIN
    -> (
    ->     SELECT name,SUBSTR(name,LOCATE('0',name)) num FROM servers
    ->     WHERE LEFT(name,LOCATE('0',name)-1)='VgsServer'
    -> ) Vegas
    -> ON Louisville.num = Vegas.num;
+------------------+-------------+
| LouisvilleServer | VegasServer |
+------------------+-------------+
| LouServer01      | VgsServer01 |
| LouServer02      | VgsServer02 |
+------------------+-------------+
2 rows in set (0.00 sec)

mysql>

    试试看 ！！！

    • 2
  2. Best Answer

    如果服务器命名格式/约定是固定的，那么您可以使用条件聚合获得所需的输出

    SELECT 
       MAX(CASE WHEN LEFT(name, 3) = 'Lou' THEN name END) Louisville,
       MAX(CASE WHEN LEFT(name, 3) = 'Vgs' THEN name END) Vegas
  FROM servers
 GROUP BY RIGHT(name, 2)

    输出：

    | 路易斯维尔 | 拉斯维加斯 |
|-------------|-------------|
| 楼服务器01 | VgsServer01 |
| 楼服务器02 | VgsServer02 |

    这是SQLFiddle演示

    • 0

相关问题