下面是个查询语句,现在我想加点条件不知道怎么下手
部分字段userinfo userID 主键 username(姓名)
cardinfo id 主键 userid外键
carinfo id 主键 cardid外键 userid外键 color(车子颜色)select a.* ,carinfo.*,userinfo.* from (select * from cardinfo ) a
join userinfo on a.userid=userinfo.userid join carinfo on a.ID=carinfo.cardid and a.userID=carinfo.userid如果只在cardinfo 上加条件比较简单只要在(select * from cardinfo ) 中加where 就行了
但是如果想在userinfo、carinfo表加条件就不知道怎么写了
举个例子
要查询 姓名='王*' color='blue'的3个表中所有字段 怎么写
部分字段userinfo userID 主键 username(姓名)
cardinfo id 主键 userid外键
carinfo id 主键 cardid外键 userid外键 color(车子颜色)select a.* ,carinfo.*,userinfo.* from (select * from cardinfo ) a
join userinfo on a.userid=userinfo.userid join carinfo on a.ID=carinfo.cardid and a.userID=carinfo.userid如果只在cardinfo 上加条件比较简单只要在(select * from cardinfo ) 中加where 就行了
但是如果想在userinfo、carinfo表加条件就不知道怎么写了
举个例子
要查询 姓名='王*' color='blue'的3个表中所有字段 怎么写
join userinfo on a.userid=userinfo.userid join carinfo on a.ID=carinfo.cardid and a.userID=carinfo.userid
where username like '王%' and color='blue'
select a.* ,carinfo.*,userinfo.* from (select * from cardinfo ) a
join userinfo on a.userid=userinfo.userid join carinfo on a.ID=carinfo.cardid and a.userID=carinfo.userid
where userinfo.username = '王*'
and carinfo.color='blue'
join userinfo on cardinfo.userid=userinfo.userid join carinfo on cardinfo.ID=carinfo.cardid and cardinfo.userID=carinfo.userid and userinfo.username like'王%' and carinfo.color='blue'
比如
cardinfo 中有个开卡时间
现在条件是 开卡时间在2008年之后 姓王的 车子颜色是blue的select a.* ,carinfo.*,userinfo.* from cardinfo a
join userinfo on a.userid=userinfo.userid join carinfo on a.ID=carinfo.cardid and a.userID=carinfo.userid
where username like '王%' and color='blue' and a.time>'20080000000000'
select a.* ,carinfo.*,userinfo.* from cardinfo a
join userinfo on a.userid=userinfo.userid join carinfo on a.ID=carinfo.cardid and a.userID=carinfo.userid
where username like '王%' and color='blue'