查询获取不匹配的传递值 [英] Queries for get unmatched passing values

问题描述

我将1000个值传递给select查询。我得到300行，但我需要知道ddnt有行的所有传递值是什么。



例如：

表：表1



ColA

1

2

3

...

..

..

$



从表1中选择colA，其中ColA在（1,2,3 .... 1000）



我需要Out如下



ColA

301

302

...

..

..

1000

解决方案

好的。我在这里看到一些混乱。



你的（1,2,3，...，1000）列表确实充当了一张桌子。如果你有一个实际的数字列表（有更好的方法来生成这样的表），那么使用它作为你的选择表。如果您希望将其用作选择，则必须为该列命名

 从中选择 ColA 
 （
  select   1   as  colA 
  union  选择  2  
  union  选择  3  
 ... 
  union  选择  1000 ）
 其中 ColA    （
  select  t.ColA 来自  table  t）

我希望你看到如果你每次都要输入序列会有多糟糕，并且不在中是不是最有效的辨别方法缺少值。



你可以设置一个表格函数，它将数字列表作为例如csv并返回一个表格，或者你可以使用一个公共Table Expression用于动态设置表。如果你有一个1到n的范围，那么我会用CTE创建它。



下面的查询将是我如何做的：

  DECLARE   @ UpperLimit   int  
 
  set   @ UpperLimit  =  1000  
 
   -    CTE  
  WITH  mycte  AS （
  SELECT   1   AS  ColA 
  UNION   ALL   SELECT  ColA +  1   FROM  mycte 
  WHERE  ColA<  @ UpperLimit  
）
   -    mycte现在是一个包含ColA和1-1000作为值的表 
   -    CTE是更高效，因为它们在select（下面）中进行评估，所以如果有where子句则不必生成所有值 
 
  SELECT  m.ColA 
  FROM  mycte m 
  WHERE   NOT   EXISTS （ SELECT  *  FROM  table1 t 其中​​ m.ColA = t.ColA）
   -   这更有效，因为内部选择不必执行完整查询 
 
 
   -   这也可以使用表格函数编写： 
  SELECT  m.ColA 
  FROM  TableFromCSVFunction（ @ TheCsvNumbers ）m 
  WHERE   NOT   EXISTS （ SELECT  *  FROM  table1 t 其中 m.ColA = t.ColA）
   -   由于整个功能必须在执行选择之前完成，因此效率低得多 

我希望有所帮助^ _ ^



Andy

Hi,

I am passing 1000 values into select query. I am getting 300 rows , but i need to know what are all passing values ddnt have rows.

for eg.:
Table : Table1

ColA
1
2
3
...
..
..
300

select colA from Table1 where ColA in (1,2,3....1000)

I need Out as follows

ColA
301
302
...
..
..
1000

解决方案

Ok. Some confusion that I see here.

You list of (1,2,3,...,1000) does actually act as a table. If you have an actual list of numbers here (there are betters ways of generating a table like this) then use this as your select table. You will have to name the column if you wish to use it as the select

select ColA
from (
    select 1 as colA
    union select 2
    union select 3
    ...
    union select 1000)
where ColA not in(
   select t.ColA from table t)

I hope you see how awful this could be if you have to enter the sequence each time, and not in is not the most efficient method of discerning the missing values.

You can set up a tabular function that takes the list of numbers as, for example, a csv and returns a table, or you can use a Common Table Expression to set up the table on the fly. If you have a set 1 to n range then I would create this with a CTE.

The query below would be how I would do it:

DECLARE @UpperLimit int

set @UpperLimit = 1000

--CTE
    WITH mycte AS (
        SELECT 1 AS ColA
        UNION ALL SELECT ColA+ 1 FROM mycte
        WHERE ColA < @UpperLimit
    ) 
--mycte is now a table with ColA and 1-1000 as values
--CTE's are more efficient as they are evaluated in the select (below) so do not have to generate all values if there is a where clause

    SELECT m.ColA
    FROM mycte m
    WHERE NOT EXISTS (SELECT * FROM table1 t where m.ColA = t.ColA)
-- this is more efficient as the inner select does not have to perform the full query


--This might also be written with a tabular function:
   SELECT m.ColA
   FROM TableFromCSVFunction(@TheCsvNumbers) m
   WHERE NOT EXISTS (SELECT * FROM table1 t where m.ColA = t.ColA)
-- This is a lot less efficient as the whole function must complete before the select is performed

I hope that helps ^_^

Andy

这篇关于查询获取不匹配的传递值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！