select base.*,m.value from (select a.userid as userid,b.name as shortname,d.name as web_style_name,e.name as channel_name from web_position as a,sys_contact as b,web_style as d,channel_list as e where a.userid = b.user_id and a.web_style = d.id and a.channel = e.id) as base inner join (select value,userid from reputation where base.userid = userid order by id desc limit 1) as m on (base.userid = m.userid) 这样看看行不,再把a表中的字段一个一个加上去。
TO:昨日如梦 这个方法我试过了,还是报Unknown column 'base.userid' in 'where clause'
select base.*,m.value from (select a.*,b.name as shortname,d.name as web_style_name,e.name as channel_name from web_position as a,sys_contact as b,web_style as d,channel_list as e where a.userid = b.user_id and a.web_style = d.id and a.channel = e.id) as base inner join (select value,userid from reputation where base.userid = userid order by id desc limit 1) as m on (base.userid = m.userid) select value,userid from reputation where base.userid = userid order by id desc limit 1 这样写是不对的, 在这个表达式里,不存在base.userid这个值.
select base.*, m.value from (select a.*,b.name as shortname,d.name as web_style_name,e.name as channel_name from web_position as a,sys_contact as b,web_style as d,channel_list as e where a.userid = b.user_id and a.web_style = d.id and a.channel = e.id) as base inner join reputation as m on base.userid = reputation.userid改成这样就好了,看是不是你要的结果
select base.*,m.value from (select a.userid as userid,b.name as shortname,d.name as web_style_name,e.name as channel_name from web_position as a,sys_contact as b,web_style as d,channel_list as e where a.userid = b.user_id and a.web_style = d.id and a.channel = e.id) as base inner join reputation as m on base.userid = m.userid order by m.id desc limit 1
TO:昨日如梦 select base.*,m.value from (select a.userid as userid,b.name as shortname,d.name as web_style_name,e.name as channel_name from web_position as a,sys_contact as b,web_style as d,channel_list as e where a.userid = b.user_id and a.web_style = d.id and a.channel = e.id) as base inner join reputation as m on base.userid = m.userid order by m.id desc limit 1 这样取出的记录 只有一条,不是我想要的结果集
select base.*,m.value from (select a.*,b.name as shortname,d.name as web_style_name,e.name as channel_name from web_position as a,sys_contact as b,web_style as d,channel_list as e where a.userid = b.user_id and a.web_style = d.id and a.channel = e.id) as base inner join (select value,userid from reputation where base.userid = userid order by id desc limit 1) as m on (base.userid = m.userid) ============================================================ 问题出在这里,2个子查询是相互独立的,不能这么用
删掉where base.userid = userid order by id desc limit 1即可
字段 类型 整理 属性 Null 默认 额外 操作
id int(11) 否 auto_increment
userid int(11) 否
articleid int(11) 否
vtime datetime 否
change varchar(100) utf8_general_ci 否
value int(11) 否
字段 类型 整理 属性 Null 默认 额外 操作
id int(11) 否 auto_increment
web_name varchar(255) utf8_general_ci 否
position_name varchar(100) utf8_general_ci 否
web_style int(11) 否
position_style int(1) 否
userid int(11) 否
alexa int(11) 否
channel int(11) 否
is_index int(1) 否
is_first int(1) 否
price double 否
mintime int(11) 否
maxtime int(11) 否
img varchar(255) utf8_general_ci 否
comment varchar(255) utf8_general_ci 否
vtime datetime 否
state int(1) 否
把所有的项目都列出来用as定义名称
base.*,m.value
from
(select a.userid as userid,b.name as shortname,d.name as web_style_name,e.name as channel_name
from
web_position as a,sys_contact as b,web_style as d,channel_list as e
where a.userid = b.user_id and a.web_style = d.id and a.channel = e.id) as
base
inner join
(select value,userid from reputation where base.userid = userid order by id desc limit 1)
as m on (base.userid = m.userid)
这样看看行不,再把a表中的字段一个一个加上去。
这个方法我试过了,还是报Unknown column 'base.userid' in 'where clause'
select base.*,m.value from (select a.*,b.name as shortname,d.name as web_style_name,e.name as channel_name from web_position as a,sys_contact as b,web_style as d,channel_list as e where a.userid = b.user_id and a.web_style = d.id and a.channel = e.id) as base inner join (select value,userid from reputation where base.userid = userid order by id desc limit 1) as m on (base.userid = m.userid) select value,userid from reputation where base.userid = userid order by id desc limit 1
这样写是不对的, 在这个表达式里,不存在base.userid这个值.
base.*, m.value
from
(select a.*,b.name as shortname,d.name as web_style_name,e.name as channel_name from web_position as a,sys_contact as b,web_style as d,channel_list as e where a.userid = b.user_id and a.web_style = d.id and a.channel = e.id)
as base
inner join reputation as m
on
base.userid = reputation.userid改成这样就好了,看是不是你要的结果
(select a.userid as userid,b.name as shortname,d.name as web_style_name,e.name as channel_name
from web_position as a,sys_contact as b,web_style as d,channel_list as e
where a.userid = b.user_id and a.web_style = d.id and a.channel = e.id)
as base
inner join
reputation as m on base.userid = m.userid
order by m.id desc limit 1
select base.*,m.value from
(select a.userid as userid,b.name as shortname,d.name as web_style_name,e.name as channel_name
from web_position as a,sys_contact as b,web_style as d,channel_list as e
where a.userid = b.user_id and a.web_style = d.id and a.channel = e.id)
as base
inner join
reputation as m on base.userid = m.userid
order by m.id desc limit 1
这样取出的记录 只有一条,不是我想要的结果集
都不如取出来操作或者两条sql语句快些.你这个查询肯定是能写出来,但肯定会很复杂.
============================================================
问题出在这里,2个子查询是相互独立的,不能这么用