Intereting Posts
Как изменить столбец, не отбрасывая таблицу в SQL 2008 Попытка развертывания базы данных Asp.Net Memebership для SQL Azure sys.objects в SQL Server отображает все объекты или весь объект, к которому может обратиться текущий пользователь SQL – выберите все строки и замените значения столбца максимальным значением столбца с идентификатором Как изменить время «23: 00: 00» на что-то вроде «11: 00PM» в sql-запросе? SQL-запрос top1 row query Мастер импорта SQL Server обрабатывает NULL как строку буква «NULL» SQL Query для определения периодов из таблицы истории Подборка SQL-запроса Обновить поле по дате переупорядочения из всех записей в таблице SSDT для VS2013 Не отображает SQL Server 2014 как целевую версию Короткий, хотя SQL Query (T-SQL, SQL Server) Показать всех детей и внуков в иерархии SQL CTE Как хранить DT_R8 в переменной в SSIS SQL Server: при использовании нескольких UPDATES в одном запросе предложение WHERE использует текущие или обновленные значения полей?

Справка по 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, который я могу сделать?

Solutions Collecting From Web of "Справка по SQL-запросу с таблицей моста"

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

 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 (…) для каждого разрешения.

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

Нет.

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

Помните, что 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) 

Если вы используете 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, которые могут достичь такого же поведения.

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

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

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

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

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

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

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

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

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

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

Кажется, что опорная функция была разработана для ситуаций, когда вы можете использовать агрегатную функцию в одном из полей. Например, если бы я хотел знать, сколько доходов каждый продавец сделал для компании x. Я мог бы подвести ценовое поле из таблицы продаж. Затем я получаю продавца и сколько доходов от продаж у них есть. Для разрешений, хотя не имеет смысла суммировать / подсчитывать / etc до поля permissionId или поля Rights.

Возможно, вы захотите посмотреть следующий пример при создании запросов межсайтовых таблиц в SQL:

http://www.databasejournal.com/features/mssql/article.php/3521101/Cross-Tab-reports-in-SQL-Server-2005.htm

Похоже, что есть новые операции, которые были включены как часть SQL Server 2005 под названием PIVOT и UNPIVOT

Для этого типа преобразования данных вам необходимо выполнить как UNPIVOT и PIVOT данных. Если вы знаете, значения, которые вы хотите преобразовать, то вы можете жестко закодировать запрос с использованием статического шарнира, в противном случае вы можете использовать динамический SQL.

Создание таблиц:

 CREATE TABLE Table_User ([UserId] int, [UserName] varchar(5)) ; INSERT INTO Table_User ([UserId], [UserName]) VALUES (1, 'John'), (2, 'Joe'), (3, 'James') ; CREATE TABLE Table_UserPermissions ([UserId] int, [PermissionId] int, [Rights] int) ; INSERT INTO Table_UserPermissions ([UserId], [PermissionId], [Rights]) VALUES (1, 10, 1), (1, 11, 2), (1, 12, 3), (2, 11, 2), (2, 12, 3), (3, 10, 2) ; 

Статический PIVOT:

 select * from ( select userid, username, value, col + '_'+ cast(rn as varchar(10)) col from ( select u.userid, u.username, p.permissionid, p.rights, row_number() over(partition by u.userid order by p.permissionid, p.rights) rn from table_user u left join Table_UserPermissions p on u.userid = p.userid ) src unpivot ( value for col in (permissionid, rights) ) unpiv ) src pivot ( max(value) for col in (permissionid_1, rights_1, permissionid_2, rights_2, permissionid_3, rights_3) ) piv order by userid 

См. SQL Fiddle with Demo

Динамический PIVOT:

Если у вас есть неизвестное количество permissionid и rights , вы можете использовать динамический sql:

 DECLARE @query AS NVARCHAR(MAX), @colsPivot as NVARCHAR(MAX) select @colsPivot = STUFF((SELECT ',' + quotename(c.name +'_'+ cast(t.rn as varchar(10))) from ( select row_number() over(partition by u.userid order by p.permissionid, p.rights) rn from table_user u left join Table_UserPermissions p on u.userid = p.userid ) t cross apply sys.columns as C where C.object_id = object_id('Table_UserPermissions') and C.name not in ('UserId') group by c.name, t.rn order by t.rn FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'select * from ( select userid, username, value, col + ''_''+ cast(rn as varchar(10)) col from ( select u.userid, u.username, p.permissionid, p.rights, row_number() over(partition by u.userid order by p.permissionid, p.rights) rn from table_user u left join Table_UserPermissions p on u.userid = p.userid ) src unpivot ( value for col in (permissionid, rights) ) unpiv ) x1 pivot ( max(value) for col in ('+ @colspivot +') ) p order by userid' exec(@query) 

См. SQL Fiddle с демонстрацией

Результат для обоих:

 | USERID | USERNAME | PERMISSIONID_1 | RIGHTS_1 | PERMISSIONID_2 | RIGHTS_2 | PERMISSIONID_3 | RIGHTS_3 | --------------------------------------------------------------------------------------------------------- | 1 | John | 10 | 1 | 11 | 2 | 12 | 3 | | 2 | Joe | 11 | 2 | 12 | 3 | (null) | (null) | | 3 | James | 10 | 2 | (null) | (null) | (null) | (null) |