Справка по SQL-запросу с таблицей моста

Я работаю с существующей базой данных и пытаюсь написать sql-запрос, чтобы получить всю информацию об учетной записи, включая уровни разрешений. Это для аудита безопасности. Мы хотим с легкостью сбрасывать всю эту информацию, чтобы было легко сравнивать ее. Моя проблема в том, что для разрешений есть таблица моста / ссылки, поэтому для каждого пользователя есть несколько записей. Я хочу вернуть результаты со всеми разрешениями для одного пользователя в одной строке. Вот пример:

Table_User:
UserId   UserName
1        John
2        Joe
3        James

Table_UserPermissions:
UserId   PermissionId   Rights
1        10             1
1        11             2
1        12             3
2        11             2
2        12             3
3        10             2

PermissionID ссылки на таблицу с именем разрешения и что он делает. Правильно как 1 = вид, 2 = изменение и т. Д.

Что я получаю от базового запроса для пользователя 1:

UserId UserName PermissionId Rights
1      John     10           1
1      John     11           2
1      John     12           3

Что бы мне хотелось:

UserId UserName Permission1 Rights1 Permission2 Right2 Permission3 Right3
1      John     10          1       11          2      12          3

В идеале я бы хотел это сделать для всех пользователей. Самое близкое, что я нашел, - это функция Pivot в SQL Server 2005. http://geekswithblogs.net/lorint/archive/2006/08/04/87166.aspx Проблема с этим из того, что я могу сказать, это то, что мне нужно чтобы назвать каждый столбец для каждого пользователя, и я не уверен, как получить уровень прав. С реальными данными у меня около 130 пользователей и 40 разных разрешений.

Есть ли другой способ с помощью sql, который я могу сделать?

sql,sql-server,pivot,

6

Ответов: 8

3

Вы могли бы сделать что-то вроде этого:

select userid, username
,      max(case when permissionid=10 then rights end) as permission10_rights
,      max(case when permissionid=11 then rights end) as permission11_rights
,      max(case when permissionid=12 then rights end) as permission12_rights
from   userpermissions
group by userid, username;

Вы должны явно добавить столбец max (...) для каждого разрешения.

1

Короткий ответ:

Нет.

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

Помните, что SQL - это язык, основанный на наборе. Вы выполняете наборы запросов и объединяете множество.

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

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

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

Что-то вроде:

SELECT Table_User.userID, userName, permissionid, rights
FROM Table_User
        LEFT JOIN Table_UserPermissions ON Table_User.userID =Table_UserPermissions.userID
ORDER BY userName

Затем отобразите все разрешения для каждого пользователя, используя что-то вроде (Python):

userID = recordset[0][0]
userName = recordset[0][1]
for row in recordset:
   if userID != row[0]:
       printUserPermissions(username, user_permissions)
       user_permissions = []
       username = row[1]
       userID = row[0]

    user_permissions.append((row[2], row[3]))

printUserPermissions(username, user_permissions)
1

Если вы используете MySQL, я бы предложил вам использовать group_concat (), как показано ниже.

select UserId, UserName, 
       group_concat(PermissionId) as PermIdList,
       group_concat(Rights SEPARATOR ',') as RightsList
from Table_user join Table_UserPermissions on 
     Table_User.UserId = Table_UserPermissions.UserId=
GROUP BY Table_User.UserId

Это вернет

UserId UserName PermIdList  RightsList
1      John     10,11,12    1,2,3

Быстрый поиск по Google для «mssql group_concat» показал пару разных хранимых процедур ( I ), ( II ) для MSSQL, которые могут достичь такого же поведения.

0

Вы могли бы создать временную таблицу table_flatuserpermissions:

Идентификатор пользователя
PermissionID1
Rights1
PermissionID2
правах2
... и т. д. до тех пор, пока вам не потребуется

Вставьте записи в эту таблицу из Table_user со всеми полями разрешений и прав null.

Обновление записей в этой таблице из table_userpermissions - вставка первой записи и установка PermissionID1 & Rights1, вторая запись для пользовательского обновления PermissionsID2 & Rights2 и т. Д.

Затем вы запрашиваете эту таблицу для создания отчета.

Лично я просто придерживаюсь столбцов UserId, UserName, PermissionID, прав, которые у вас есть сейчас.

Возможно, замените в некоторых текстах PermissionID и Права вместо числовых значений.

Возможно, отсортируйте таблицу с помощью PermissionID, User вместо User, PermissionID, чтобы аудитор мог проверять пользователей по каждому типу разрешений.

0

Если это приемлемо, стратегия, которую я использовал, как для проектирования, так и для реализации, заключается в том, чтобы сбрасывать запрос, не привязанный к Excel или Access. Оба имеют гораздо более дружественные пользовательские интерфейсы для поворота данных, и в этой среде намного больше людей.

Когда у вас есть дизайн, который вам нравится, тогда легче подумать о том, как его дублировать в TSQL.

SQL, SQL-сервер, стержень,
Похожие вопросы
Яндекс.Метрика