SQL query to find ids where field1 = x and field1

问题:

I have this table (simplified)

 ID  | Field1
---------------------------------
 2   | Cat     
 2   | Goat
 6   | Cat
 6   | Dog 

I need to find the IDs where a record exists whose value for field1 is cat and for the same id, another record exists whose value is Goat. In this case, it would only return ID 2.

Doing something like below will not work. where Field1='Cat' and Field1='Goat'

I'm guessing I need some sort of subquery here? I'm not entirely sure. (Using SQL Server 2008)

回答1:

Use:

  SELECT t.id
    FROM YOUR_TABLE t
   WHERE t.field1 IN ('Cat', 'Goat')
GROUP BY t.id
  HAVING COUNT(DISTINCT t.field1) = 2

The key here is using COUNT (DISTINCT ...) to get a count of the field1 values. Doesn't matter if a user has Cat 3x, and dog 1x... unless you need to filter those out?

Another option is INTERSECT, which returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand:

SELECT a.id 
  FROM YOUR_TABLE a 
 WHERE a.field1 = 'Cat'
INTERSECT
SELECT b.id 
  FROM YOUR_TABLE b 
 WHERE b.field1 = 'Goat'

回答2:

Try this:

SELECT id FROM
(
 SELECT id FROM <YOUR_TABLE> WHERE Field1 = 'Cat'
 INTERSECT
 SELECT id FROM <YOUR_TABLE> WHERE Field1 = 'Goat'
) a

Alternative:

SELECT a.ID 
  FROM <YOUR_TABLE> a INNER JOIN <YOUR_TABLE> b 
    ON a.ID = b.ID
WHERE a.Field1 = 'CAT'
  AND b.Field1 = 'GOAT'

回答3:

Use a query like this

SELECT ID FROM table INNER JOIN 
    (SELECT ID, COUNT(FILED1) AS Expr1 
       FROM table GROUP BY ID 
          HAVING COUNT(FIELD1) > 1) SR ON table.ID = SR.ID  WHERE table.FIELD1 = 'Cat'

So you just have to put a variable with a DECLARE for the 'Cat' if you want to have a more dynamic query

回答4:

SELECT DISTINCT t1.ID 
FROM table t1, table t2
WHERE t1.ID=t2.ID AND t1.Field1 <> t2.Field1

回答5:

Not tested but something like this might work

select t1.ID from tbl t1 inner join tbl t2 on t1.ID=t2.ID
where (t1.Field1='Cat' and t2.Field1='Goat')