我应该使用哪个函数来解决

Question

假设我有以下

400001 - 400104 
400601 - 400614 
400701 - 400710 
401101 
401104 
401105 
401107 
401201 
401202 
401203 
401207 
401209 
401301 
401303 
421001 - 421005 
421201 
421202 
421203 
421204 
421301 
421304 
421306 

给出一个范围，我必须找到以下的范围不是告诉我做的最简单的方法给出的数据。 我使用功能和解决，但如果有更多的范围内它会变得更加繁琐

IN 
      (400001, 400002, 400003, 400004, 400005, 400006, 400007, 400008, 
      400009, 400010, 400011, 400012, 400013, 400014, 400015, 400016, 
      400017, 400018, 400019, 400020, 400021, 400022, 400023, 400024, 
      400025, 400026, 400027, 400028, 400029, 400030, 400031, 400032, 
      400033, 400034, 400035, 400036, 400037, 400038, 400039, 400040, 
      400041, 400042, 400043, 400044, 400045, 400046, 400047, 400048, 
      400049, 400050, 400051, 400052, 400053, 400054, 400055, 400056, 
      400057, 400058, 400059, 400060, 400061, 400062, 400063, 400064, 
      400065, 400066, 400067, 400068, 400069, 400070, 400071, 400072, 
      400073, 400074, 400075, 400076, 400077, 400078, 400079, 400080, 
      400081, 400082, 400083, 400084, 400085, 400086, 400087, 400088, 
      400089, 400090, 400091, 400092, 400093, 400094, 400095, 400096, 
      400097, 400098, 400099, 400100, 400101, 400102, 400103, 400104, 
      400601, 400602, 400603, 400604, 400605, 400606, 400607, 400608, 
      400609, 400610, 400611, 400612, 400613, 400614, 400701, 400702, 
      400703, 400704, 400705, 400706, 400707, 400708, 400709, 400710, 
      401101, 401104, 401105, 401107, 401201, 401202, 401203, 401207, 
      401209, 401301, 401303, 421001, 421002, 421003, 421004, 421005, 
      421201, 421202, 421203, 421204, 421301, 421304, 421306) 

Answer 1

除了作为乏味，in有1000个值，如果你有大范围你可能会触及的极限。

如果你能得到的Excel数据导入的格式，你可以生成文本，形成CTE，你也许可以做的公式，你可以做这样的事情：

with tmp_tab as (
    select 400001 as range_from, 400104 as range_to from dual 
    union all select 400601, 400614 from dual 
    union all select 400701, 400710 from dual 
    union all select 401101, null from dual 
    union all select 401104, null from dual 
    union all select 401105, null from dual 
    union all select 401107, null from dual 
    union all select 401201, null from dual 
    union all select 401202, null from dual 
    union all select 401203, null from dual 
    union all select 401207, null from dual 
    union all select 401209, null from dual 
    union all select 401301, null from dual 
    union all select 401303, null from dual 
    union all select 421001, 421005 from dual 
    union all select 421201, null from dual 
    union all select 421202, null from dual 
    union all select 421203, null from dual 
    union all select 421204, null from dual 
    union all select 421301, null from dual 
    union all select 421304, null from dual 
    union all select 421306, null from dual 
) 
select st.id, st.data 
from some_table st 
join tmp_tab tt on st.id between tt.range_from 
    and nvl(tt.range_to, tt.range_from); 

所以你打造一个基于Excel数据的公用表格表达式，范围从-from和range-to;如果您只有一个数字，您可以将范围保留为空并稍后使用nvl将其作为虚拟范围;然后在表格中查找任何范围内的数据。

棘手的一点是生成CTE，但即使是手动从数据手动执行，也比将每个范围手动扩展到所有单个值更容易。

Answer 2

我不认为这是可能的使用，但您可以将数据插入到一个临时表或表变量：

DECLARE @table TABLE (value int) 
INSERT INTO @table VALUES (400001),(400002),(400003),(400004),(400005),(400006),(400007),(400008),(400009),(400010),(400011),(400012),(400013),(400014),(400015),(400016),(400017),(400018),(400019),(400020),(400021),(400022),(400023),(400024),(400025),(400026),(400027),(400028),(400029),(400030),(400031),(400032),(400033),(400034),(400035),(400036),(400037),(400038),(400039),(400040),(400041),(400042),(400043),(400044),(400045),(400046),(400047),(400048),(400049),(400050),(400051),(400052),(400053),(400054),(400055),(400056),(400057),(400058),(400059),(400060),(400061),(400062),(400063),(400064),(400065),(400066),(400067),(400068),(400069),(400070),(400071),(400072),(400073),(400074),(400075),(400076),(400077),(400078),(400079),(400080),(00081),(400082),(400083),(400084),(400085),(400086),(400087),(400088),(400089),(400090),(400091),(400092),(400093),(400094),(400095),(400096),(400097),(400098),(400099),(400100),(400101),(400102),(400103),(400104),(400601),(400602),(400603),(400604),(400605),(400606),(400607),(400608),(400609),(400610),(400611),(400612),(400613),(400614),(400701),(400702),(400703),(400704),(400705),(400706),(400707),(400708),(400709),(400710),(401101),(401104),(401105),(401107),(401201),(401202),(401203),(401207),(401209),(401301),(401303),(421001),(421002),(421003),(421004),(421005),(421201),(421202),(421203),(421204),(421301),(421304),(421306) 

DECLARE @minValue INT 
SET @minValue = (SELECT MIN (value) FROM @table) 

DECLARE @valueToTest INT 
SET @valueToTest = 200 

PRINT CASE WHEN @valueToTest < @minValue THEN 'below range' 
      ELSE 'NOT below range' 
     END