CREATE TABLE `money` (
`id` int(10) NOT NULL auto_increment,
`comid` int(10) NOT NULL COMMENT '公司的ID',
`postdate` date NOT NULL COMMENT '缴费日期',
`money` float NOT NULL COMMENT '缴费总金额',
`startdate` date NOT NULL COMMENT '费用开始日期',
`enddate` date NOT NULL COMMENT '费用用完日期',
PRIMARY KEY (`id`),
UNIQUE KEY `comid` (`comid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;CREATE TABLE `com` (
`id` int(10) unsigned NOT NULL auto_increment,
`cname` varchar(250) NOT NULL,
PRIMARY KEY (`id`)
);--------------------------------------------------------------------------------------------------
--插入数据
INSERT INTO `com` VALUES (1, '中国公司');
INSERT INTO `com` VALUES (2, '美国公司');
INSERT INTO `ltd_money` VALUES (1, 2, '2009-09-26', 600, '2005-01-01', '2015-05-06');--------------------------------------------------------------------------------------------------
--SQL语句
SELECT c.*,
COUNT(m.comid)*1 AS `show2`
FROM `com` c
LEFT JOIN `money` m ON m.comid=c.id AND m.`money` > 0 AND m.startdate > CURDATE() AND m.enddate <= CURDATE()
WHERE `show2` > 0
--------------------------------------------------------------------------------------------------
--错误
--#1054 - Unknown column 'show2' in 'where clause'
--------------------------------------------------------------------------------------------------
难道不能这样用吗?
但是有几次我却可以这样用的啊。今天却有不能了,真的让人头都大了。
`id` int(10) NOT NULL auto_increment,
`comid` int(10) NOT NULL COMMENT '公司的ID',
`postdate` date NOT NULL COMMENT '缴费日期',
`money` float NOT NULL COMMENT '缴费总金额',
`startdate` date NOT NULL COMMENT '费用开始日期',
`enddate` date NOT NULL COMMENT '费用用完日期',
PRIMARY KEY (`id`),
UNIQUE KEY `comid` (`comid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;CREATE TABLE `com` (
`id` int(10) unsigned NOT NULL auto_increment,
`cname` varchar(250) NOT NULL,
PRIMARY KEY (`id`)
);--------------------------------------------------------------------------------------------------
--插入数据
INSERT INTO `com` VALUES (1, '中国公司');
INSERT INTO `com` VALUES (2, '美国公司');
INSERT INTO `ltd_money` VALUES (1, 2, '2009-09-26', 600, '2005-01-01', '2015-05-06');--------------------------------------------------------------------------------------------------
--SQL语句
SELECT c.*,
COUNT(m.comid)*1 AS `show2`
FROM `com` c
LEFT JOIN `money` m ON m.comid=c.id AND m.`money` > 0 AND m.startdate > CURDATE() AND m.enddate <= CURDATE()
WHERE `show2` > 0
--------------------------------------------------------------------------------------------------
--错误
--#1054 - Unknown column 'show2' in 'where clause'
--------------------------------------------------------------------------------------------------
难道不能这样用吗?
但是有几次我却可以这样用的啊。今天却有不能了,真的让人头都大了。
COUNT(m.comid)*1 AS `show2`
FROM `com` c
LEFT JOIN `money` m ON m.comid=c.id AND m.`money` > 0 AND m.startdate > CURDATE() AND m.enddate <= CURDATE()
WHERE `show2` > 0 -------------
不能在where后面引用`show2`
改成这样:SELECT c.*,
COUNT(m.comid)*1 AS `show2`
FROM `com` c
LEFT JOIN `money` m ON m.comid=c.id AND m.`money` > 0 AND m.startdate > CURDATE() AND m.enddate <= CURDATE()
WHERE COUNT(m.comid)*1 > 0
+------+------+
| rid | con |
+------+------+
| 2 | b |
| 3 | b |
+------+------+
2 rows in set (0.00 sec)mysql> select tb.rid as test,tb.con from tb left join tb t on tb.rid=t.rid wher
e test>3;
ERROR 1054 (42S22): Unknown column 'test' in 'where clause'
mysql> select tb.rid as test,tb.con from tb left join tb t on tb.rid=t.rid havi
ng test>2;
+------+------+
| test | con |
+------+------+
| 3 | b |
+------+------+
1 row in set (0.00 sec)
COUNT(m.comid)*1 AS `show2`
FROM `com` c
LEFT JOIN `money` m ON m.comid=c.id AND m.`money` > 0 AND m.startdate > CURDATE() AND m.enddate <= CURDATE()
WHERE `show2` > 0
MySQL (或者说很多数据库产品)都不支持这样用,因为WHERE是在谓词上,换成实际的列名。
SELECT c.*,
COUNT(m.comid)*1 AS `show2`
FROM `com` c
LEFT JOIN `money` m ON m.comid=c.id AND m.`money` > 0 AND m.startdate > CURDATE() AND m.enddate <= CURDATE()
having COUNT(m.comid)*1>0另外建议写成标准的SQL语句 用GROUP BY,否则会有不可预测的问题当移植的时候。或者你的DBA突然把MYSQL的模式换了。