如果表SQL中存在多个记录 [英] If multiple records exist in a table SQL

问题描述

我正在尝试以下列格式输出结果;



< pre lang =text>数字存在
123456 1
345633 0
243234 1
865445 1

其中'exists'是1或0，具体取决于表X中是否存在'number'。



我尝试过各种各样的事情，但它总是最终只显示1旁边存在的数字，但没有显示与0并不存在的数字。



我想这样做如果可能的话，不创建额外的表，并且在X中找到每个字段是否存在是很重要的，因为X包含数十万个记录，如果找到记录但是表的其余部分是扫描继续寻找它。



更新：



我现在有：

 CREATE TEMPORARY TABLE number 
（number int（11））; 
 
插入数字（数字）值
（1），（2），（3）; 
 
 SELECT n.number 
 COALESCE（（选择TOP 1 
 1 
其中n.number in（'1'，'2'，'3'，' 4'）），0）AS'存在'
来自数字n 

但现在还有另一种语法错误，

查询错误（1064）：'附近的语法错误（（SELECT TOP 1 1，其中n.number in（'1'，'2'，'3'，'4'）），0）AS'存在''在第2行

我尝试过：



SELECT 1 WHERE EXISTS

INNER JOIN

UNION

解决方案

你可以试试类似

  DECLARE   @tableY   TABLE （Number  INT ）
 
  DECLARE   @ tableX   TABLE （Number  INT ）
 
  INSERT   INTO   @ tableY  
  SELECT   123456   UNION  
  SELECT   345633   UNION  
  SELECT   243234   UNION  
  SELECT   865445  
 
 < span class =code-keyword> INSERT   INTO   @ tableX  
 < span class =code-keyword> SELECT   345633   UNION  
  SELECT   243234   U NION  
  SELECT   865445  
 
  SELECT  Y.Number，
  CASE  
  WHEN  ISNULL（x.Number，' '）= ' ' 那么  0  
  ELSE   1  
  END  ' 存在' 
  FROM   @ tableY  y 
  LEFT   JOIN   @ tableX  x 
  ON  y.Number = x.Number 

输出：

数字存在
 123456 0 
 243234 1 
 345633 1 
 865445 1 

您写道，如果可能的话，您不想创建其他表格。如果你没有在表格或类似的表格中列出数字，那么这些数字会来自哪里？



查询本身的内容，可以通过多种方式完成。就个人而言，我可能更喜欢以下格式

  SELECT  a.TestNumber，
  COALESCE （（ SELECT   TOP   1  
  1  
  FROM  DataTable b 
  WHERE  b.Number = a.TestNumber）， 0 ） AS  存在 
  FROM  NumberToTestTable a 

如果数字在数据表中存在多次，上面的查询应该可以防止行的乘法运算。另外 TOP 1 结构应该在找到匹配后立即结束调查。



从性能点来看因此，为了有效地扫描数据，在 DataTable 中索引 Number 列是至关重要的。

Hi,

I'm trying to output results in the following format;

number exists
123456 1
345633 0
243234 1
865445 1

where 'exists' is 1 or 0 depending on whether 'number' exists in table X.

I've tried all sorts of things, but it always ends up only showing the numbers which exist alongside 1, but not showing the ones which don't exist alongside 0.

I'd like to do this without creating additional tables if possible, and it's important to return whether each field exists or not as soon as it's found in X, as X contains multiple hundred thousand records and it would be inefficient if the record is found but the rest of the table is scanned to continue looking for it.

UPDATE:

I now have:

CREATE TEMPORARY TABLE numbers
(number int(11));

insert into numbers (number) values
(1),(2),(3);

SELECT n.number
COALESCE((SELECT TOP 1
        1
where n.number in ('1','2','3','4')), 0) AS 'exists'
FROM numbers n

but now there's yet another syntax error,

Error in query (1064): Syntax error near '((SELECT TOP 1 1 where n.number in ('1','2','3','4')), 0) AS 'exists' ' at line 2

What I have tried:

SELECT 1 WHERE EXISTS
INNER JOIN
UNION

解决方案

You can try something like

DECLARE @tableY TABLE (Number INT)

DECLARE @tableX TABLE (Number INT)

INSERT INTO @tableY
	SELECT 123456  UNION
	SELECT 345633   UNION
	SELECT 243234   UNION
	SELECT 865445   

INSERT INTO @tableX
	SELECT 345633   UNION
	SELECT 243234   UNION
	SELECT 865445   

SELECT Y.Number, 
CASE 
	WHEN ISNULL(x.Number,'') = '' THEN 0
	ELSE 1
END 'Exists'
FROM @tableY y
LEFT JOIN @tableX x
ON y.Number = x.Number

Output:

Number	Exists
123456	0
243234	1
345633	1
865445	1

You wrote that you'd like to do this without creating additional tables if possible. Where would the numbers come from if you don't list them in a table or similar?

What comes to the query itself, it can be done in multiple ways. Personally I would perhaps prefer the following format

SELECT a.TestNumber,
       COALESCE( (SELECT TOP 1
               1
        FROM DataTable b
        WHERE b.Number = a.TestNumber), 0) AS Exists
FROM NumberToTestTable a

The query above should prevent multiplication of rows in case the number exists several times in the data table. Also TOP 1 structure should end the investigation as soon as a match is found.

From the performance point of view, it would be critical to index the Number column in the DataTable in order to efficiently scan for the data.

这篇关于如果表SQL中存在多个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！