RT!
我使用了这个语句,但是错误了,在本地调试的时候虽然错误但是还是可以运行的,但是上传到了远程之后就根本不行了,一点儿错误都不允许。设计的表read,jilu表read的结构
id,title,cnt,isShow表jilu的结构
id,rid,date,tn(tn的值有很多中,tn的值可以是ding ,unti,not)语句是SELECT *,
(SELECT COUNT(*) FROM jilu WHERE rid=read.id AND tn LIKE ding ) AS ding,
(SELECT COUNT(*) FROM jilu WHERE rid=read.id AND tn LIKE unti )AS unti,
(SELECT COUNT(*) FROM jilu WHERE rid=read.id AND tn LIKE not ) AS not
FROM read
WHERE isShow=1
ORDER BY ding+not-unti DESC
limit 0,100为什么这个语句错误了呢?
提示ding那里错误了。
当我把ORDER BY ding这个order 只写ding的时候确实正确的,但是只要再加其他的时候,比如变成
ORDER BY ding+unti 的时候就错误了。
反正只有一个的时候才会正确,而我要的是ding+unti+not.
如何才可以实现?
我使用了这个语句,但是错误了,在本地调试的时候虽然错误但是还是可以运行的,但是上传到了远程之后就根本不行了,一点儿错误都不允许。设计的表read,jilu表read的结构
id,title,cnt,isShow表jilu的结构
id,rid,date,tn(tn的值有很多中,tn的值可以是ding ,unti,not)语句是SELECT *,
(SELECT COUNT(*) FROM jilu WHERE rid=read.id AND tn LIKE ding ) AS ding,
(SELECT COUNT(*) FROM jilu WHERE rid=read.id AND tn LIKE unti )AS unti,
(SELECT COUNT(*) FROM jilu WHERE rid=read.id AND tn LIKE not ) AS not
FROM read
WHERE isShow=1
ORDER BY ding+not-unti DESC
limit 0,100为什么这个语句错误了呢?
提示ding那里错误了。
当我把ORDER BY ding这个order 只写ding的时候确实正确的,但是只要再加其他的时候,比如变成
ORDER BY ding+unti 的时候就错误了。
反正只有一个的时候才会正确,而我要的是ding+unti+not.
如何才可以实现?
语句是
SELECT *,
(SELECT COUNT(*) FROM jilu WHERE rid=read.id AND tn LIKE ding ) AS ding,
(SELECT COUNT(*) FROM jilu WHERE rid=read.id AND tn LIKE unti )AS unti,
(SELECT COUNT(*) FROM jilu WHERE rid=read.id AND tn LIKE not ) AS not
FROM read
WHERE isShow=1
ORDER BY ding+not-unti DESC
limit 0,100如何实现?
涉及的表read ,jilu
表read的结构
id,title,cnt,,....表jilu的结构
id,readid,tn,date,...
表read中的记录
id, title, cnt
10, 很好的天气, 文摘内容1
20, 不错,不错, 文章内容2
表jilu中的jilu
id, read_id, tn, date,
12, 120, ding, 1234567890
20, 30, unti, 2315648970
2, 10, not, 2510364789
1234, 30, ding , 5642317890
要得到的列:
id,title,cnt,ding,unti,not,order
10,标题,内容,1230,0,0,1230
13,标题,nr,0,10,0,-10
203,biaoti,nr,120,10,10,120
1236,bt,nr,20,10,30,0其中ding是SELECT COUNT(*) FROM jilu WHERE tn LIKE 'ding' AND read_id=read.id
其中not 是SELECT COUNT(*) FROM jillu WHERE tn LIKE 'not' AND read_id=read.id
unti类推order的值是ding+not-unti的值在SQL语句中我是这样用的:
SELECT *,
(SELECT COUNT(*) FROM jilu WHERE tn LIKE 'ding' AND read_id=read.id ) AS ding,
(SELECT COUNT(*) FROM jilu WHERE tn LIKE 'not' AND read_id=read.id ) AS `not`,
(SELECT COUNT(*) FROM jilu WHERE tn LIKE 'ding' AND read_id=read.id ) AS unti
FROM read
WHERE isShow=1
ORDER BY ding+`not`-unti DESC
limit 0,100
其中问题就出在了ORDER BY 后面这里了,反正提示没有ding这个。
当我把order by的背后的那一群给删除了之后或者换成表read中的列的时候就正常了。
SELECT *,
(SELECT COUNT(*) FROM jilu WHERE tn LIKE 'ding' AND read_id=read.id ) AS ding,
(SELECT COUNT(*) FROM jilu WHERE tn LIKE 'not' AND read_id=read.id ) AS `not`,
(SELECT COUNT(*) FROM jilu WHERE tn LIKE 'ding' AND read_id=read.id ) AS unti
FROM `read`
WHERE isShow=1
ORDER BY ding+`not`-unti DESC
limit 0,100测试如下。没有错误信息。
mysql> SELECT *,
-> (SELECT COUNT(*) FROM jilu WHERE tn LIKE 'ding' AND read_id=read.id ) AS ding,
-> (SELECT COUNT(*) FROM jilu WHERE tn LIKE 'not' AND read_id=read.id ) AS `not`,
-> (SELECT COUNT(*) FROM jilu WHERE tn LIKE 'ding' AND read_id=read.id ) AS unti
-> FROM `read`
-> WHERE isShow=1
-> ORDER BY ding+`not`-unti DESC
-> limit 0,100;
Empty set (0.06 sec)mysql> select version();
+----------------------+
| version() |
+----------------------+
| 5.1.33-community-log |
+----------------------+
1 row in set (0.00 sec)mysql>
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
版本。