データが、
OpenAM
OpenAM
OpenIDM
OpenDJ
OpenDJ
OpenDJ
のようにあるとき、
OpenAM が2個、
OpenDJ が3個、という情報を得たいときのSQLです。
SQL SERVER用のSQLです。
以下のサイトで実験できます。
http://sqlfiddle.com/
スキーマ
CREATE TABLE ForgeRock
([productName] varchar(23), [description] varchar(57))
;
INSERT INTO ForgeRock
([productName], [description])
VALUES
('OpenIDM', 'Platform for building enterprise provisioning solutions'),
('OpenDJ', 'Robust LDAP server for Java'),
('OpenAM', 'Full-featured access management'),
('OpenAM', 'Full-featured access management'),
('OpenDJ', 'Robust LDAP server for Java'),
('OpenDJ', 'Robust LDAP server for Java')
;
SQL:
WITH CTE AS (
SELECT productName, ROW_NUMBER() OVER (PARTITION BY productName ORDER BY productName) AS RowNum
FROM ForgeRock
)
SELECT productName, COUNT() as DuplicateCount
FROM CTE
GROUP BY productName
HAVING COUNT() > 1;