在oracle的BETWEEN子句中为多个范围运行一个查询

Question

我在oracle中使用下面的sql查询来获取一个范围内的行数，这个范围可以很好地进行

SELECT count(SERIALNR)
FROM my_tab
WHERE SERIALNR BETWEEN '93266070760000' AND '93266070809999';

但是，如果我必须为多个范围运行一个查询，比如下面的一体式查询，那么该如何进行查询

93266070560000  93266070609999
93266070610000  93266070659999
93266070660000  93266070709999
93266070710000  93266070759999
93266070760000  93266070809999

注：SERIALNR的类型为NUMBER

Answer 1

您似乎需要UNION ALL来分别获得每个范围的

SELECT 'Range 1' AS "Range", count (SERIALNR) AS "Count"
FROM tab
WHERE SERIALNR BETWEEN 93266070560000 AND 93266070809999
UNION ALL 
SELECT 'Range 2', count (SERIALNR)
FROM tab
WHERE SERIALNR BETWEEN 93266070610000 AND 93266070659999
UNION ALL 
SELECT 'Range 3', count (SERIALNR)
FROM tab
WHERE SERIALNR BETWEEN 93266070660000 AND 93266070709999
UNION ALL 
SELECT 'Range 4', count (SERIALNR)
FROM tab
WHERE SERIALNR BETWEEN 93266070710000 AND 93266070759999
UNION ALL 
SELECT 'Range 5', count (SERIALNR)
FROM tab
WHERE SERIALNR BETWEEN 93266070760000 AND 93266070809999

并且您不需要引用SERIALNR作为具有数字数据类型。

Answer 2

关于多重或条件-

SELECT  count (SERIALNR)
FROM my_tab
WHERE (SERIALNR BETWEEN '93266070760000' AND   '93266070809999'
OR SERIALNR BETWEEN '93266070560000' AND   '93266070609999'
OR SERIALNR BETWEEN '93266070610000' AND   '93266070659999'
OR SERIALNR BETWEEN '93266070660000' AND   '93266070709999'
OR SERIALNR BETWEEN '93266070710000' AND   '93266070759999'
OR SERIALNR BETWEEN '93266070760000' AND   '93266070809999');

Answer 3

您已经使用了OR子句，

SELECT  count (SERIALNR) FROM my_tab WHERE 
(SERIALNR  BETWEEN '93266070760000' AND   '93266070809999')
OR
(SERIALNR  BETWEEN '93266070610000' AND   '93266070659999')
OR
(SERIALNR  BETWEEN '93266070660000' AND   '93266070709999')
OR
(SERIALNR  BETWEEN '93266070710000' AND   '93266070759999')
OR
(SERIALNR  BETWEEN '93266070760000' AND   '93266070809999');

如果这些值存储在任何其他表中，会更方便

Answer 4

我想你需要在每个语句之间使用括号，用OR进行条件反射，就像下面的一样

( 
SERIALNR BETWEEN '93266070760000' AND   '93266070809999'
OR
..... other ranges comes here
) and
OTHER cONDITIONS

您可以查看有关逻辑条件的文档

Answer 5

如果需要列中的计数，则需要条件聚合：

SELECT 
COUNT(CASE WHEN serialnr BETWEEN '93266070760000' AND '93266070809999' THEN 1 END) AS c1,
COUNT(CASE WHEN serialnr BETWEEN '93266070610000' AND '93266070659999' THEN 1 END) AS c2,
COUNT(CASE WHEN serialnr BETWEEN '93266070660000' AND '93266070709999' THEN 1 END) AS c3,
COUNT(CASE WHEN serialnr BETWEEN '93266070710000' AND '93266070759999' THEN 1 END) AS c4,
COUNT(CASE WHEN serialnr BETWEEN '93266070760000' AND '93266070809999' THEN 1 END) AS c5
FROM my_tab 
WHERE serialnr BETWEEN '93266070560000' AND '93266070809999';

或者对于UNION ALL，如果您希望按行计数：

WITH ranges AS
(
SELECT '93266070760000' AS s1, '93266070809999' AS s2 FROM DUAL
UNION ALL
SELECT '93266070610000' AS s1, '93266070659999' AS s2 FROM DUAL
UNION ALL
SELECT '93266070660000' AS s1, '93266070709999' AS s2 FROM DUAL
UNION ALL
SELECT '93266070710000' AS s1, '93266070759999' AS s2 FROM DUAL
UNION ALL
SELECT '93266070760000' AS s1, '93266070809999' AS s2 FROM DUAL
)
SELECT
s1,
s2,
(SELECT COUNT(*) FROM my_tab WHERE serialnr BETWEEN r.s1 AND r.s2) AS cnt
FROM ranges r
ORDER BY s1;

Answer 6

@thorsten，或者不是所有的unionall语句，也许是这样的：

SELECT CASE WHEN serialnr BETWEEN '93266070760000' AND '93266070809999' THEN c1
WHEN serialnr BETWEEN '93266070610000' AND '93266070659999' THEN c2 
WHEN serialnr BETWEEN '93266070660000' AND '93266070709999' THEN c3 
WHEN serialnr BETWEEN '93266070710000' AND '93266070759999' THEN c4 
WHEN serialnr BETWEEN '93266070760000' AND '93266070809999' THEN c5 
end Category, count(serialnr) total
FROM my_tab 
group by CASE WHEN serialnr BETWEEN '93266070760000' AND '93266070809999' THEN c1
WHEN serialnr BETWEEN '93266070610000' AND '93266070659999' THEN c2 
WHEN serialnr BETWEEN '93266070660000' AND '93266070709999' THEN c3 
WHEN serialnr BETWEEN '93266070710000' AND '93266070759999' THEN c4 
WHEN serialnr BETWEEN '93266070760000' AND '93266070809999' THEN c5 
end;

我不确定哪一个更有效：