如何将AND操作放在sql server中的表的单个列中 [英] How to put AND operation in a single column of a table in sql server

问题描述

大家好，

我正在尝试进行自定义搜索，我想搜索属于Atul和BaidyaNath的ID。表结构如下：

 id号名称
  -   -  ---- 
 1 2 Atul 
 4 2 Atul 
 4 35 BaidyaNath 
 6 35 BaidyaNath 
 7 35 BaidyaNath 
 7 2 Atul 
 1 35 BaidyaNath 
 8 35 BaidyaNath 
 11 35 BaidyaNath 
 12 35 BaidyaNath 
 13 2 Atul 
 13 35 BaidyaNath 
 14 35 BaidyaNath 
 17 35 BaidyaNath 
 18 2 Atul 
 19 2 Atul 
 19 35 BaidyaNath 
 20 35 BaidyaNath 
 21 35 BaidyaNath 
 22 2 Atul 
 22 35 BaidyaNath 
 23 2 Atul 
 23 35 BaidyaNath 
 24 35 BaidyaNath 
 25 35 BaidyaNath 
 26 2 Atul 
 26 35 BaidyaNath 
 27 2 Atul 

从上表中我想要属于的ids Atul 和 Baidyanath 。不是属于 Atul 或 Baidyanath 的I​​D。所以搜索到的ID应该是 4 ，7,1,13,19,22,23,26 。那么如何将一个AND操作放在一个表的一列中呢。

主要的问题是可以有n不。要搜索的名称。所以我们不能使用JOIN。



请建议我解决方法/方法。

提前谢谢。

解决方案

这应该有效。只需在IN子句中插入任意数量的名称，并相应地调整HAVING COUNT的数量。

  SELECT  t1.ID 
  FROM 表1 t1 
  WHERE  t1.Name  IN （'  Atul'，'  Baidyanath'）
  GROUP   BY  t1.ID 
  HAVING  COUNT（t1.Name）> =  2 ; 

（就像这里的一个例子，当然我会建议使用Sql-Parameters而不是文字。）



编辑： COUNT（t1.ID）

< blockquote>尝试：

  SELECT  id  FROM  MyTable 
  WHERE 名称 IN （ '  BaidyaNath'，'  Atul'）
  GROUP   BY  id 
  HAVING  COUNT（名称）>  1  

我认为这个查询应该满足您的需求：

  SELECT  id 
  FROM （
  SELECT  [名称]，MIN（id） AS  id 
  FROM  TableName 
  WHERE  [Name]  IN （'  Atul'，'  Baidyanath'）
  GROUP   BY  [名称] 
） AS  T 

但我错了......



第二次看，似乎你只想获得第一次出现的id，所以：

  SELECT  id 
  FROM （
  SELECT  id，ROW_NUMBER（） OVER （ PARTITION   BY  [名称]  ORDER   BY  id） AS  RowNo 
  FROM  TableName 
  WHERE  [Name]  IN （'  blah'，'  halb'）
） AS  T 
 < span class =code-keyword> WHERE  RowNo = 1 

它必须返回如下数据：

 1 
 4 
 7 
 13 
 19 
 22 
 23 
 26 

Hi All,
I am trying to do a customised search in which I want to search the ids which belong to both Atul and BaidyaNath. The table structure is as follows:

id      No.     Name
--      --      ----
1	2	Atul
4	2	Atul
4	35	BaidyaNath
6	35	BaidyaNath
7	35	BaidyaNath
7	2	Atul
1	35	BaidyaNath
8	35	BaidyaNath
11	35	BaidyaNath
12	35	BaidyaNath
13	2	Atul
13	35	BaidyaNath
14	35	BaidyaNath
17	35	BaidyaNath
18	2	Atul
19	2	Atul
19	35	BaidyaNath
20	35	BaidyaNath
21	35	BaidyaNath
22	2	Atul
22	35	BaidyaNath
23	2	Atul
23	35	BaidyaNath
24	35	BaidyaNath
25	35	BaidyaNath
26	2	Atul
26	35	BaidyaNath
27	2	Atul

From the above table I want the ids which belongs to both Atul and Baidyanath. Not the ids which belongs to either Atul or Baidyanath.So the searched ids should be 4,7,1,13,19,22,23,26. So how do I put a AND operation in a single column of a table.
And the main problem is that there can be n no. of Names to be searched. So we cannot use JOIN.

Kindly suggest me the solution/approach.
Thanks in advance.

解决方案

This should work. Just insert any amount of names into the IN-clause and adjust the number of HAVING COUNT accordingly.

SELECT t1.ID
FROM Table1 t1
WHERE t1.Name IN ('Atul', 'Baidyanath')
GROUP BY t1.ID
HAVING COUNT(t1.Name) >= 2;

(Just as an example here, of course I would recommend using Sql-Parameters instead of literals.)

Edit: COUNT(t1.ID)

Try:

SELECT id FROM MyTable
WHERE Name IN ('BaidyaNath', 'Atul')
GROUP BY id
HAVING COUNT(Name) > 1

[EDIT]
I thought that this query should meet your needs:

SELECT id
FROM(
   SELECT [Name], MIN(id) AS id
   FROM TableName
   WHERE [Name] IN ('Atul', 'Baidyanath')
   GROUP BY [Name]
) AS T

but i was wrong...

On the second look, it seems that you want to get only first occurence of id, so:

SELECT id
FROM (
    SELECT id, ROW_NUMBER() OVER(PARTITION BY [Name] ORDER BY id) AS RowNo
    FROM TableName
    WHERE [Name] IN ('blah', 'halb')
) AS T
WHERE RowNo=1

It must return data as follow:

1
4
7
13
19
22
23
26

这篇关于如何将AND操作放在sql server中的表的单个列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！