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会查询出重复值
试试这个。
要以 颜色表作为基准去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
)
)
不知道你想要的是不是要查看这个效果?
我是这样写的:
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;