根据两列选择一行，一列具有ID，另一列具有特定值

I would like to select rows based on specific column values and a unique id column in SQL

我想根据特定的列值和SQL中的唯一id列选择行

The Table I have is as follows

我的表格如下

    Acc_id | Name   |  Status    | value
    ----------------------------------------
      101  |  com   |  Active    |  1
      202  |  net   |  Active    |  2
      202  |  net   |  New       |  3
      303  |  com   |  Active    |  1 
      303  |  com   |  New       |  4
      303  |  com   |  Inactive  |  2
      404  |  org   |  Active    |  5
      404  |  org   |  Inactive  |  6
      505  |  gov   |  New       |  2
      505  |  gov   |  Active    |  3

I would like to have the following table as a result

我想要以下表格作为结果

    Acc_id | Name   |  Status    | value
    ----------------------------------------
      202  |  net   |  Active    |  2
      202  |  net   |  New       |  3
      303  |  com   |  Active    |  1 
      303  |  com   |  New       |  4
      505  |  gov   |  New       |  2
      505  |  gov   |  Active    |  3

As you see above for the same id from column 'Acc_id' with Column 'Status' only with "New" and "Active" are selected

如上面所示，从列“Acc_id”中选择相同的id，列“Status”，只选择“New”和“Active”

2 个解决方案

#1

Try this:

试试这个:

SELECT
  t1.*
FROM table1 AS t1
INNER JOIN
(
  SELECT Acc_id
  FROM table1
  WHERE status IN('Active', 'New')
  GROUP BY Acc_id
  HAVING COUNT(DISTINCT status) = 2
) AS t2 ON t1.Acc_id = t2.Acc_id 
WHERE t1.status IN('Active', 'New');

The HAVING COUNT(DISTINCT status) = 2 with WHERE status IN('Active', 'New') will ensure that the selected Acc_id have only two statuses active and new and no more, then JOIN with the original table to get the result of the columns.

have COUNT(DISTINCT status) = 2，其中的status IN('Active'， 'New')将确保所选Acc_id只有两个Active和New状态，不会再有其他状态，然后与原始表联接以获得列的结果。

SQL Fiddle Demo
SQL小提琴演示

This will give you:

这将给你:

| ACC_ID | NAME | STATUS | VALUE |
|--------|------|--------|-------|
|    202 |  net | Active |     2 |
|    202 |  net |    New |     3 |
|    303 |  com | Active |     1 |
|    303 |  com |    New |     4 |
|    505 |  gov |    New |     2 |
|    505 |  gov | Active |     3 |

#2

Try This...

试试这个…

SELECT * FROM TABLENAME WHERE Acc_id IN 
(SELECT Acc_id FROM TABLENAME WHERE status IN ('Active','New') 
GROUP BY Acc_id Having COUNT(Acc_id)>1) AND status IN ('Active','New')

#1