查询1:
SELECT cust_last_name, cust_credit_limit
FROM customers
WHERE (UPPER(cust_last_name) LIKE 'A%' OR
UPPER(cust_last_name) LIKE 'B%' OR
UPPER(cust_last_name) LIKE 'C%' AND cust_credit_limit < 10000;
查询2:
SELECT cust_last_name,cust_credit_limit
FROM customers
WHERE UPPER(cust_last_name) BETWEEN 'A' AND 'C' AND
cust_credit_limit < 10000;
我正在尝试生成所有姓氏以a,b或c开头的客户的姓氏和信用限额的报告,信用额度低于10000。哪个是正确的方式?
您不能直接在A和C之间使用,因为以C类CDE开头的值将不包括在内。由于您需要从a开始的所有行使用C,您可以使用自定义范围:
select cust_last_name, cust_credit_limit
from customers
where UPPER(cust_last_name) >= 'A'
and UPPER(cust_last_name) < 'D'
and cust_credit_limit < 10000
它找到了(包含)和D(独家)之间的所有字符串。
演示
或使用substr:
select cust_last_name, cust_credit_limit
from customers
where UPPER(substr(cust_last_name,1,1)) between 'A' and 'C'
and cust_credit_limit < 10000
demo
oracle可能会在以下查询中使用索引:
select cust_last_name, cust_credit_limit
from customers
where ((cust_last_name >= 'A' and cust_last_name < 'D') or
(cust_last_name >= 'a' and cust_last_name < 'd')
) and
cust_credit_limit < 10000;
对于性能,我建议您使用upper()
版本并在customers(upper(cust_last_name), cust_credit_limit)
上创建索引。