如何让mysql的join的连接查询中的子表多加个条件

Question

table1:
id      bgcolor
1       #333333
2       #666666
3       #999999


table2
id      table1_id    clothes
5       1            男式T恤
8       2            女式T恤
3       3            中性T恤


table3
id      table2_id    mytime         color
1       5            2014-06-01     白色
2       5            2014-06-02     灰色
3       5            2014-06-03     绿色


table4
id      table2_id    mytime         styles
3       5            2014-06-13     中国风
4       5            2014-06-29     欧美式
5       5            2014-07-15     怀旧式

如何让mysql的join的连接查询中的子表多加个条件

如果

table2.id=table3.table2_id
table2.id=table4.table2_id

因为table3和table4中的table2_id是有重复的

所以查询出的数据也会重复，如何再进一步根据mytime取出最新数据

下面是实计项目中的尝试:

例子1：$sql = "SELECT `score_size`.`size_disc`, `score_disc`.`Dish`, `category`.`bgcolor`, `data`.`id`, `data`.`Weeks`, `data`.`League`, `data`.`The_main`, `data`.`The_guest`, `data`.`The_game_time`, `data`.`The_weather`, `data`.`Half_score`, `data`.`Full_score`, `data`.`Half_size`, `data`.`Full_size`, `data`.`Disc_Half_score`, `data`.`Disc_Full_score`, `data`.`Disc_Half_size`, `data`.`Disc_Full_size` FROM `score_size`, `score_disc`, `data`, `category` WHERE `data`.`id`=(SELECT `id` FROM score_size ORDER BY id DESC LIMIT 0, 1) AND `data`.`id`=(SELECT `id` FROM score_disc ORDER BY id DESC LIMIT 0, 1) AND `category`.`id`=`data`.`category_id` ORDER BY `Ydata`.`The_game_time` ASC";

查询结果为空....

例子2：基于CI框架
$select = 'score_1size.size_disc, score_disc.Dish, category.bgcolor, data.id, data.Weeks, data.League, data.The_main, data.The_guest, data.The_game_time, data.The_weather, data.Half_score, data.Full_score, data.Half_size, data.Full_size, data.Disc_Half_score, data.Disc_Full_score, data.Disc_Half_size, data.Disc_Full_size';
        $where = array(
            'score_size.create_time'=>'(select max(create_time) from score_size)',
            'score_disc.create_time'=>'(select max(create_time) from score_disc)'
            );
        $D = $this->db->where($where)->select($select)->join('score_size', 'data.id=score_size.data_id')->join('score_disc', 'data.id=score_disc.data_id')->join('category', 'category.id=data.category_id', 'left')->order_by('data.The_game_time asc, score_disc.id desc, score_size.id desc')->get('data');

把->where($where)去掉的情况

只要data.id=score_disc.data_id和data.id=score_size.data_id都成立的将会全部查询出来

所以例子2会查询出重复值

Answer 1

试试这个。
要以 颜色表作为基准去join其他表。

    $this->load->database();
    $this->db->select('T.TID,T.name,C.color,T.ccid as color_code,S.style');
    $this->db->from('color as C');
    $this->db->join('tshit as T', 'T.TID = C.tid');
    $this->db->join('color_code as CC', 'T.ccid = CC.CCID');
    $this->db->join('style as S', 'T.TID = S.tid');
    $this->db->order_by("time", "desc"); 
    $query = $this->db->get();
    $result = $query->result();
    $this->LOG($query);
    $this->LOG($result);
    // SELECT `T`.`TID`, `T`.`name`, `C`.`color`, `T`.`ccid` as color_code, `S`.`style`, `S`.`s_date` as time FROM (`color` as C) JOIN `tshit` as T ON `T`.`TID` = `C`.`tid` JOIN `color_code` as CC ON `T`.`ccid` = `CC`.`CCID` JOIN `style` as S ON `T`.`TID` = `S`.`tid` ORDER BY `time`

按照你的数据输出了，有9条， 3(style) x 3(color) x 1(tshit) = 9。

Array
(
    [0] => stdClass Object
        (
            [TID] => 5
            [name] => 男式T恤
            [color] => 灰色
            [color_code] => 1
            [style] => 欧美式
            [time] => 2014-07-30 00:00:00
        )

    [1] => stdClass Object
        (
            [TID] => 5
            [name] => 男式T恤
            [color] => 白色
            [color_code] => 1
            [style] => 欧美式
            [time] => 2014-07-30 00:00:00
        )

    [2] => stdClass Object
        (
            [TID] => 5
            [name] => 男式T恤
            [color] => 绿色
            [color_code] => 1
            [style] => 欧美式
            [time] => 2014-07-30 00:00:00
        )

    [3] => stdClass Object
        (
            [TID] => 5
            [name] => 男式T恤
            [color] => 白色
            [color_code] => 1
            [style] => 怀旧式
            [time] => 2014-07-29 00:00:00
        )

    [4] => stdClass Object
        (
            [TID] => 5
            [name] => 男式T恤
            [color] => 绿色
            [color_code] => 1
            [style] => 怀旧式
            [time] => 2014-07-29 00:00:00
        )

    [5] => stdClass Object
        (
            [TID] => 5
            [name] => 男式T恤
            [color] => 灰色
            [color_code] => 1
            [style] => 怀旧式
            [time] => 2014-07-29 00:00:00
        )

    [6] => stdClass Object
        (
            [TID] => 5
            [name] => 男式T恤
            [color] => 灰色
            [color_code] => 1
            [style] => 中国风
            [time] => 2014-07-24 00:00:00
        )

    [7] => stdClass Object
        (
            [TID] => 5
            [name] => 男式T恤
            [color] => 白色
            [color_code] => 1
            [style] => 中国风
            [time] => 2014-07-24 00:00:00
        )

    [8] => stdClass Object
        (
            [TID] => 5
            [name] => 男式T恤
            [color] => 绿色
            [color_code] => 1
            [style] => 中国风
            [time] => 2014-07-24 00:00:00
        )

    [9] => stdClass Object
        (
            [TID] => 5
            [name] => 男式T恤
            [color] => 灰色
            [color_code] => 1
            [style] => 甜美风
            [time] => 2014-07-17 00:00:00
        )

    [10] => stdClass Object
        (
            [TID] => 5
            [name] => 男式T恤
            [color] => 白色
            [color_code] => 1
            [style] => 甜美风
            [time] => 2014-07-17 00:00:00
        )

    [11] => stdClass Object
        (
            [TID] => 5
            [name] => 男式T恤
            [color] => 绿色
            [color_code] => 1
            [style] => 甜美风
            [time] => 2014-07-17 00:00:00
        )

)

Answer 2

不知道你想要的是不是要查看这个效果?

我是这样写的:

select t2.clothes,t1.bgcolor,t3.color,t4.styles
from table2 as t2
inner join table1 as t1 on t1.id=t2.table1_id
left join table3 as t3 on t3.table2_id=t2.id
left join table4 as t4 on t4.table2_id=t2.id;