Недавно мне понадобилось сделать на одном сайте такую систему страниц, чтобы к любой сущности (пост в блоге, страничка автора поста, просто статическая страница) можно было обратиться по её коду, а не по ID. Причём этот код должен идти сразу за адресом самого сайта — без каких бы то ни было папок, подпапок и прочих слэшей в пути. Примерно вот так:

• http://sitename.org/cool_post
• http://sitename.org/even_cooler_post_author
• http://sitename.org/just_a_static_page

Разумеется, чтобы это реализовать, надо убедиться, что коды сущностей уникальны. Я не буду рассказывать, как я добавлял единички к кодам, когда они совпадали, я расскажу о том, как я искал одинаковые коды.

Одна таблица

Начнём с простого — с одной единственной таблицы такого вида:

Здесь у нас id — это первичный ключ, он уникален. А поле code — просто некий код, который может повторяться для разных записей. Если быть совсем точным, то я сгенерировал коды просто транслитерировав названия.

Теперь мы хотим найти все повторы. Будем выводить сам повторяющийся код, список Idшек и количество повторов.

MySQL

SELECT code, group_concat(id SEPARATOR ', ') AS ids, count(code) AS cnt
FROM post
GROUP BY code
HAVING cnt > 1

Для нашего примера получим такой вывод:

PostgreSQL

Особенность PostgreSQL’я в том, что функция конкатенации строк работает только с текстом, поэтому числовые Idшники нужно сначала привести к типу text.

SELECT code, string_agg(CAST(id AS TEXT), ', ') AS ids, count(code) AS cnt
FROM post
GROUP BY code
HAVING count(code) > 1

MSSQL

В MSSQL нет агрегирующей функции для конкатенации, поэтому придётся использовать хитрый подзапрос.

SELECT DISTINCT [code], (
	SELECT [type]+', '
	FROM POST AS post1
	WHERE post1.[code] = post2.[code]
	FOR xml path('')
) ids, count(post2.[code]) as cnt
FROM POST AS post2
GROUP BY post2.[code]
HAVING count(post2.[code]) > 1

Много таблиц

Теперь вернёмся к исходной задаче: нам нужны уникальные коды не в одной таблице, а сразу в нескольких. Возьмём для примера такие:

Таблица Post

Таблица Article

Таблица Author

Теперь вместо IDшников я буду выводить названия таблиц, где встречаются одинаковые записи.

В сущности, запрос останется прежним, только теперь выборку будем делать из временной таблицы, в которой объединим все остальные.

MySQL

SELECT code, group_concat(`type` SEPARATOR ', ') AS types, count(code) AS cnt
FROM (
	SELECT code, 'post' AS 'type' FROM post
	UNION
	SELECT code, 'article' AS 'type' FROM article
	UNION
	SELECT code, 'author' AS 'type' FROM author
) AS we_are_one
GROUP BY code
HAVING cnt > 1

MSSQL

В MSSQL исходная таблица нам нужна в двух местах — в выборке и в грязном хаке, заменяющем конкатенацию, поэтому будет проще, если мы сделаем для неё синоним.

;WITH we_are_one AS (
	SELECT code, 'post' AS 'type' FROM post
	UNION
	SELECT code, 'article' AS 'type' FROM article
	UNION
	SELECT code, 'author' AS 'type' FROM author
)

SELECT DISTINCT [code], count(we_are_one2.[code]) AS cnt, (
	SELECT [type]+', '
	FROM we_are_one AS we_are_one1
	WHERE we_are_one1.[code] = we_are_one2.[code]
	FOR xml path('')
) types
FROM we_are_one AS we_are_one2
GROUP BY we_are_one2.[code]
HAVING count(we_are_one2.[code]) > 1
ORDER BY cnt DESC

Заключение

А в конце хочется сказать, что вся эта статья была затеяна только ради конкатенации в MSSQL:)