查找字符串位置并加入另一个表的行 [英] Find string position and join another table's row

问题描述

关于获取2套桌子，我有两个主要问题.

I have two main questions on getting 2 set of tables.

table : room_type
rt_id   rt_title
1       Type A
2       Type B
3       Type C
4       Type D
5       Type E

这是另一张桌子.

表格:rate_cost

table: rate_cost

id     rate hotel cost                      date
2999    7   1   4700-5400-6100-6600-7300    2012-11-01
3000    7   1   4700-5400-6100-6600-7300    2012-11-02
3001    7   1   4700-5400-6100-6600-7300    2012-11-03
3002    7   1   4700-5400-6100-6600-7300    2012-11-04
3003    7   1   4700-5400-6100-6600-7300    2012-11-05
3004    7   1   4700-5400-6100-6600-7300    2012-11-06
3005    7   1   4700-5400-6100-6600-7300    2012-11-07
3006    7   1   4700-5400-6100-6600-7300    2012-11-08
3007    7   1   4700-5400-6100-6600-7300    2012-11-09
3008    7   1   4700-5400-6100-6600-7300    2012-11-10
3009    7   1   4700-5400-6100-6600-7300    2012-11-11
3010    7   1   4700-5400-6100-6600-7300    2012-11-12
3011    7   1   4700-5400-6100-6600-7300    2012-11-13
3012    7   1   4700-5400-6100-6600-7300    2012-11-14
3013    7   1   4700-5400-6100-6600-7300    2012-11-15
3014    7   1   4700-5400-6100-6600-7300    2012-11-16
3015    7   1   4700-5400-6100-6600-7300    2012-11-17
3016    7   1   4700-5400-6100-6600-7300    2012-11-18
3017    7   1   4700-5400-6100-6600-7300    2012-11-19
3018    7   1   4700-5400-6100-6600-7300    2012-11-20
3019    7   1   4700-5400-6100-6600-7300    2012-11-21
3020    7   1   4700-5400-6100-6600-7300    2012-11-22
3021    7   1   4700-5400-6100-6600-7300    2012-11-23
3022    7   1   4700-5400-6100-6600-7300    2012-11-24
3023    7   1   4700-5400-6100-6600-7300    2012-11-25
3024    7   1   4700-5400-6100-6600-7300    2012-11-26
3025    7   1   4700-5400-6100-6600-7300    2012-11-27
3026    7   1   4700-5400-6100-6600-7300    2012-11-28
3027    7   1   4700-5400-6100-6600-7300    2012-11-29
3028    7   1   4700-5400-6100-6600-7300    2012-11-30
3028    8   2   4700-5400-6100-6600-7300    2012-11-01
3028    9   3   4700-5400-6100-6600-7300    2012-11-01

每个破折号是根据每个房间类型的位置所对应的费率成本.例如，4700是类型A的费率成本(rt_id = 1)，5400是类型B的费率... 7300是类型E(rt_id = 5)的费率.

Each dash separated number is a rate cost regarding to each room type by its position. For example 4700 is a rate cost for Type A (rt_id=1), 5400 is Type B... 7300 is Type E(rt_id=5).

现在，我想创建一个如下所示的结果.

Now I'd like to create a result like following.

Day/Room type   Type A | Type B | Type C | Type D | Type E
1 Nov 2012      4700     5400     6100     6600     7300
2 Nov 2012      4700     5400     6100     6600     7300
3 Nov 2012      4700     5400     6100     6600     7300
4 Nov 2012      4700     5400     6100     6600     7300
...
30 Nov 2012     4700     5400     6100     6600     7300

我使这些结果可以在PHP中工作，但是完成它花了几秒钟.我已经尝试过自己做，但是我缺乏mySQL知识.所以请赐教.

I was made those result work in PHP but it cost seconds to complete. I've tried to do on my own but I lack of mySQL knowledge. So please enlighten me.

推荐答案

将字符串拆分为列:

select date,
       substring_index(cost, '-', 1) type_a,
       case when cost regexp '.*-' then
                 substring_index(substring_index(cost, '-', 2), '-', -1)
            else ''
       end type_b,
       case when cost regexp '.*-.*-' then
            substring_index(substring_index(cost, '-', 3), '-', -1)
            else ''
       end type_c,
       case when cost regexp '.*-.*-.*-' then
            substring_index(substring_index(cost, '-', 4), '-', -1)
            else ''
       end type_d,
       case when cost regexp '.*-.*-.*-.*-' then
            substring_index(substring_index(cost, '-', 5), '-', -1)
            else ''
       end type_e
from rate_cost;

如果可以修改表设计，最好创建多个列:

If you can modify the table design, it's better to create multiple columns:

create table rate_cost (
    id int,
    rate int,
    hotel int,
    cost_type_a int,
    cost_type_b int,
    cost_type_c int,
    cost_type_d int,
    cost_type_e int,
    date date);

这篇关于查找字符串位置并加入另一个表的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！