不能在SQL语句中使用分号。我该怎么办呢？
不能在SQL语句中使用分号。我该怎么办呢？
提问于 2016-07-05 06:52:56
sql接口不允许我在SQL查询中添加分号。我该怎么做才能避开这一切？
select country.country, count(customer.customer_id) as country_count
from customer
join address on customer.address_id = address.address_id
join city on address.city_id = city.city_id
join country on city.country_id = country.country_id
group by country.country_id
order by country_count desc;
limit 10如果我杀死分号，界面就会给出：
错误: ORA-00907:缺少右括号。
如果我插入分号，界面就会给出：
错误: ORA-00911:无效字符
在我的sqllite程序中，没有;，查询工作得很好。
回答 3
Stack Overflow用户
发布于 2016-07-05 06:55:29
将分号放在LIMIT之后，分号总是出现在查询的末端。
order by country_count desc
limit 10;请注意，这是MySQL语法，所以这个查询引发错误并不奇怪。您应该使用rownum：
SELECT * FROM (
select country.country, count(customer.customer_id) as country_count
from customer
join address on customer.address_id = address.address_id
join city on address.city_id = city.city_id
join country on city.country_id = country.country_id
group by country.country_id
order by country_count desc) t
WHERE rownum < 11;Stack Overflow用户
发布于 2016-07-05 06:56:44
你在找这个
SELECT *
FROM (SELECT country.country,
Count(customer.customer_id) AS country_count
FROM customer
JOIN address
ON customer.address_id = address.address_id
JOIN city
ON address.city_id = city.city_id
JOIN country
ON city.country_id = country.country_id
GROUP BY country.country_id
ORDER BY country_count DESC)
WHERE ROWNUM <= 10 在Oracle中，不支持LIMIT关键字，而是使用ROWNUM来限制记录
Stack Overflow用户
发布于 2016-07-05 07:29:53
如果您有Oracle12，可以使用先取
SELECT country.country, COUNT (customer.customer_id) AS country_count
FROM customer
JOIN address ON customer.address_id = address.address_id
JOIN city ON address.city_id = city.city_id
JOIN country ON city.country_id = country.country_id
GROUP BY country.country_id
ORDER BY country_count DESC
FETCH FIRST 10 ROWS ONLY如果没有，实际上首先获取的是语法糖：
SELECT country, country_count
FROM ( SELECT country.country,
COUNT (customer.customer_id) AS country_count,
ROW_NUMBER () OVER (ORDER BY COUNT (customer.customer_id) DESC) rn
FROM customer
JOIN address ON customer.address_id = address.address_id
JOIN city ON address.city_id = city.city_id
JOIN country ON city.country_id = country.country_id
GROUP BY country.country_id)
WHERE rn <= 10
ORDER BY country_count DESC您可以在Oracle版本< 12中使用它
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/38196970
复制相关文章
相似问题
腾讯云开发者
