Question

I m having some trouble writing a query.

For example:

Let s say I have just one table with the following values:

    Items
+-----+--------+
| ID  |  NAME  |
+-----+--------+
| A1  | Item_1 |
| A1  | Item_2 |
| A1  | Item_3 |
| A2  | Item_1 |
| A2  | Item_2 |
| A3  | Item_1 |
+-----+--------+

From this, I want to identify all of the item names that are associated with more than one ID, along with the associated ID names.

Given this example the output would be --

+----+--------+
| ID |  Name  |
+----+--------+
| A1 | Item_1 |
| A2 | Item_1 |
| A3 | Item_1 |
| A1 | Item_2 |
| A2 | Item_2 |
+----+--------+

Item_3 would be excluded since there is only one instance of it, associated with A3.

I m using SQL Server 2008. Thanks in advance!

Answer 1

use

SELECT * FROM MyTable A WHERE A.Name IN
(SELECT T.Name FROM MyTable T GROUP BY T.Name HAVING COUNT(DISTINCT T.ID) > 1)
ORDER BY A.Name, A.ID

Answer 2

You could use windowed aggregating:

WITH Counted AS (
  SELECT
    ID,
    NAME,
    IDCount = COUNT(*) OVER (PARTITION BY NAME)
  FROM atable
)
SELECT
  ID,
  NAME
FROM Counted
WHERE IDCount > 1

References:

Answer 3

Try this

SELECT id,name
FROM Table1
where name in ( select name from(
select name,count(name) as cnt from table1
group by name)
where cnt>1)

This query was written in MS Access / Oracle try to convert into SQL. I don t know syntax for those.

I will explain logic from inner most query First you are taking names which are greater than count 1 Then you are selecting your required id s and names.

The most simplest form of above is as follows

SELECT id,name
FROM Table1 
where name in (
  select name as cnt from table1
  group by name
  having count(name)>1)

友情链接