Question

SQL查询10种最贵产品语句

PRODUCT表里有77行数据，按单价由高到低查询10种最贵产品
select top 10 "productname","price" from PRODUCT where "Provider"="null" order by "price" DESC;
我这样写怎么不对？
都不行
ORA-00923: FROM keyword not found where expected

Answer 1

mysql里有top这种写法，语句写成select top 10 productname,price from PRODUCT where Provider=null order by price DESC；但是sybase里面没有，可以用set rowcount 10代替，语句则写成set rowcount 10 select productname,price from PRODUCT where Provider=null order by price DESC。注意字段名不能用引号括起来。至于判断null值，可以用=，也可以用is null。 分号加不加无所谓。另外，Provider是判空还是判无值也有区别，一个是=null，一个是=''。

Answer 2

sql语句中没有双引号，只有更新值或判断时加单引号！你改成：
select top 10 productname,price from PRODUCT where Provider=’‘ order by price DESC
语句结束后没有分号

Answer 3

select top 10 productname, price from PRODUCT where Provider is null order by price DESC
如果是mysql 的话
select productname, price from PRODUCT where Provider is null order by price DESC limit 0,10

Answer 4

select x.productname, count(y.*)
from product x inner join product y on y.price > x.price
group by x.productname
having count(y.*) < 10

Answer 5

select top 10 productname,price
from PRODUCT
where Provider is null 这里用is null
order by "price" DESC
这样应该就可以了