Question

mysql镶嵌查询语句提示错误1054 - Unknown column 'm.id' in 'where clause'

SELECT d.*,pm.label,(SELECT COUNT(*) from (select js,jifen from deal where uid=m.id and time>DATE_FORMAT(time,'$Y-$d-$m') and state!=1 and state!=6 GROUP BY stoptime) as jys) from deal as d,members as m,pricemsg as pm where d.state<>1 and d.uid=m.id and d.time>DATE_SUB(now(),INTERVAL 1 MONTH) and m.username='111111111' and m.lx=1 and pm.name=d.commodity order by d.id desc

必须需要那个语句，怎么在子查询(select js,jifen from deal where uid=中等于m.id的置，求解！

CREATE TABLE `deal` (
`id` int(11) unsigned zerofill NOT NULL auto_increment,
`uid` int(11) NOT NULL,
`time` datetime NOT NULL,
`type` int(11) NOT NULL,
`commodity` varchar(255) NOT NULL,
`class` varchar(255) NOT NULL,
`price` double NOT NULL,
`jifen` double(11,2) NOT NULL,
`stoptime` datetime NOT NULL,
`stopprice` double default '0',
`js` double(11,2) default NULL,
`state` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=96 DEFAULT CHARSET=gb2312;
32) NOT NULL,
`lx` int(11) NOT NULL,
`name` varchar(20) NOT NULL,
`sex` char(1) NOT NULL,
`phone` char(11) NOT NULL,
`phonev` char(1) default NULL,
`email` varchar(50) NOT NULL,
`emailev` char(1) default NULL,
`regtime` datetime NOT NULL,
`regip` varchar(255) NOT NULL,
`jifen` double(11,2) NOT NULL default '0.00',
`je` double(11,2) NOT NULL default '0.00',
`viptime` datetime default NULL,
`tj` int(11) default NULL,
PRIMARY KEY (`id`,`username`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=gb2312;
CREATE TABLE `pricemsg` (
`name` varchar(255) NOT NULL,
`label` varchar(255) NOT NULL,
`class` varchar(255) default NULL,
`win` int(3) NOT NULL default '85',
`loss` int(3) NOT NULL default '15',
`price` double default NULL,
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

Answer 1

源代码如下：

SELECT d.*,pm.label,
( SELECT COUNT(*)
from (select js,jifen
from deal

where uid=m.id and time>DATE_FORMAT(time,'$Y-$d-$m')

and state!=1 and state!=6 GROUP BY stoptime

)

) as jys

from deal as d,members as m,pricemsg as pm

where d.state<>1 and d.uid=m.id and d.time>DATE_SUB(now(),INTERVAL 1 MONTH)

and m.username='111111111' and m.lx=1 and pm.name=d.commodity

order by d.id desc

运行结果如下：

扩展资料：

mysql的另一种查询方式-嵌套查询表达形式

SELECT <目标表达式1>[，...]

FROM <表或视图名1>

WHERE [表达式] （SELECT <目标表达式2>[,...]

FROM <表或视图名2>）

[GROUP BY <分组条件>

HAVING [<表达式>比较运算符] （SELECT <目标表达式2>[,...]

FROM <表或视图名2> ）]

1、返回一个值的子查询

当子查询的返回值只有一个时，可以使用比较运算符如=、<、>、>=、<=、!=等将富查询和子查询连接起来。

2、返回一组值的子查询

如果子查询的返回值不止一个，而是一个集合时，则不能直接使用比较运算符，可以在比较运算符和子查询之间插入ANY、SOME或ALL。其中等值关系可以用IN操作符。

扩展资料来源：百度百科-嵌套查询

Answer 2

SELECT d.*,pm.label,
( SELECT COUNT(*)
from (select js,jifen
from deal
where uid=m.id and time>DATE_FORMAT(time,'$Y-$d-$m')
and state!=1 and state!=6 GROUP BY stoptime
)
) as jys
from deal as d,members as m,pricemsg as pm
where d.state<>1 and d.uid=m.id and d.time>DATE_SUB(now(),INTERVAL 1 MONTH)
and m.username='111111111' and m.lx=1 and pm.name=d.commodity
order by d.id desc

追问

依旧： Unknown column 'm.id' in 'where clause'

追答

实在是没看懂那个子查询
select js,jifen
from deal
where uid=m.id and time>DATE_FORMAT(time,'$Y-$d-$m') and state!=1 and state!=6
GROUP BY stoptime
既然按stoptime分组，咋能选出js与jifen呢