- 论坛徽章:
- 93
|
回复 1# lanfeng356
大概参考一下吧:
- mysql> select * from tb5;
- +---------+------+----------------------+--------+----------+
- | cg_name | rset | type | volume | vol_size |
- +---------+------+----------------------+--------+----------+
- | name1 | set1 | name1_local | dev1 | 20 |
- | name1 | set1 | name1_prod | dev2 | 20 |
- | name1 | set1 | name1_remote | dev3 | 20 |
- | name1 | N/A | name1_local_journal | dev4 | 100 |
- | name1 | N/A | name1_local_journal | dev5 | 100 |
- | name1 | N/A | name1_prod_journal | dev6 | 50 |
- | name1 | N/A | name1_remote_journal | dev7 | 80 |
- | name1 | N/A | name1_remote_journal | dev8 | 100 |
- +---------+------+----------------------+--------+----------+
- 8 rows in set
- mysql> select
- cg_name,
- rset,
- max(if(type like "%_local", volume, "")) as local,
- max(if(type like "%_prod", volume, "")) as prod,
- max(if(type like "%_remote", volume, "")) as remote,
- max(if(type like "%_remote", vol_size, 0)) as remote_size,
- max(if(type like "%_local_journal", volume, "")) as local_journal,
- max(if(type like "%_local_journal", vol_size, 0)) local_journal_size,
- max(if(type like "%_prod_journal", volume, "")) as prod_journal,
- max(if(type like "%_prod_journal", vol_size, 0)) as prod_journal_size,
- max(if(type like "%_remote_journal", volume, "")) as local_journal,
- max(if(type like "%_remote_journal", vol_size, 0)) as local_journal_size
- from tb5
- group by cg_name, rset;
- +---------+------+-------+------+--------+-------------+---------------+--------------------+--------------+-------------------+---------------+--------------------+
- | cg_name | rset | local | prod | remote | remote_size | local_journal | local_journal_size | prod_journal | prod_journal_size | local_journal | local_journal_size |
- +---------+------+-------+------+--------+-------------+---------------+--------------------+--------------+-------------------+---------------+--------------------+
- | name1 | N/A | | | | 0 | dev5 | 100 | dev6 | 50 | dev8 | 100 |
- | name1 | set1 | dev1 | dev2 | dev3 | 20 | | 0 | | 0 | | 0 |
- +---------+------+-------+------+--------+-------------+---------------+--------------------+--------------+-------------------+---------------+--------------------+
- 2 rows in set
复制代码
|
|