mysql语句发布

Question

我有一个MySQL语句，它在我的查询程序(Navicat)中工作得很好，但是当我尝试将它放入一个水晶命令中时，它会抛出一个错误。

声明是

set @rownum=0
Select 
  @rownum := @rownum +1 as ranknum
  , cust_name
  ,  count(distinct callref) as rank 
from opencall 
where logdate like '%/08/2011%' 
  and companyname = "McDonald's Nederland" 
group by cust_name 
order by rank desc

错误是

无法从数据库检索数据。详细信息42000 mysqld-4.0.16您的SQL语法有一个错误。检查与您的MYSQL服务器版本相对应的手册，以获得正确的语法，使用“Select@rownum :=@rownum +1 as ranknum，cust_name，count”(dist数据库供应商代码:1064

)

Answer 1

您需要使用;分隔语句

set @rownum=0;
SELECT
  @rownum := @rownum +1 as ranknum
  , cust_name
  , COUNT(distinct callref) as rank 
FROM opencall 
WHERE logdate LIKE '%/08/2011%'    <<-- ?? dates are stored YYYY/MM/DD in a DB.
  AND companyname = "McDonald's Nederland" 
GROUP BY cust_name 
ORDER BY ranknum DESC   <<-- ranknum, **not** rank

或者，您可以在联接中进行初始化：

SELECT
  @rownum := @rownum +1 as ranknum
  , cust_name
  , COUNT(distinct callref) as rank 
FROM opencall 
INNER JOIN (SELECT @rownum := 0) AS initializaton
WHERE logdate LIKE '%/08/2011%' 
  AND companyname = "McDonald's Nederland" 
GROUP BY cust_name 
ORDER BY ranknum DESC

你确定你不是有意：

SELECT
  @rownum := @rownum +1 as ranknum
  , cust_name
  , COUNT(distinct callref) as rank 
FROM opencall  
CROSS JOIN (SELECT @rownum := 0) AS initializaton
WHERE logdate BETWEEN '2011-08-01' AND '2011-08-31' 
  AND companyname LIKE "McDonald's Nederland"  <<-- like is case-insensitive
GROUP BY cust_name                             <<-- `=` can be case sensitive
ORDER BY ranknum DESC