Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Объединить 3 SELECT без дублирования записей  [new]
k_mak
Member

Откуда:
Сообщений: 33
Здравствуйте, форумчане.
Подскажите, можно-ли объединить такое
select	quotename(a.DisplayName,'"')"DisplayName", quotename(a.EmployeeID,'"')"EmployeeID_AD",
		quotename(b.Tabel_number,'"')"EmployeeID_SAP", quotename(a.Title,'"')"Title",
		quotename(a.Department,'"')"Department", quotename(a.Descript,'"')"Description",
		quotename(a.sAMAccountName,'"')"SAMAccountname", quotename(c.lastlogontime,'"')"time_login_to_outlook",
		/*quotename(c.displayName,'"')"for_which_mail_login",*/ quotename(c.sAMAccountName,'"')"email_owner",
		quotename(convert(datetime,c.lastlogontime),'"')"last_time_used_email", quotename(c.lastloggedonuseraccount,'"')"who_last_login",
		quotename(a.whenCreated,'"')"whenCreated", quotename(convert(datetime,d.LastLogon),'"')"LastLogon_to_DC", quotename(d.DC,'"')"DomainController",
		quotename(e.dismissal,'"')"work\dismissal", quotename(e.moveDate,'"')"moveDate", quotename(a.Status,'"')"Status",
		quotename(a.SecID,'"')"SecID", quotename(a.badPwdCount,'"')"BadPwdCount",quotename(a.userAccountControl,'"')"userAccountControl", 
		quotename(a.distinguishedName,'"')"distinguishedName",quotename(a.passwordLastSet,'"')"passwordLastSet",
		quotename(e.namescollision,'"')"namesCollision",
		case when ((GETDATE() - e.moveDate) < 2) then '2 дня в карантине' else 'Заблокировать' end as Comment
from	(((tADUser a left outer join tUserIDTab b on a.SecID = b.UserID)
		left outer join vExchMBoxes c on a.sAMAccountName = c.sAMAccountName)
		left outer join vADLastLogonDC d on a.sAMAccountName = d.SAMAccountname)
		left outer join tSAP e on b.Tabel_number = e.tabnum		
where	b.Tabel_number = e.tabnum and a.Status = 'enable' and e.dismissal = '3'
		
	
union all


select	quotename(a.DisplayName,'"')"DisplayName", quotename(a.EmployeeID,'"')"EmployeeID_AD",
		quotename(b.Tabel_number,'"')"EmployeeID_SAP", quotename(a.Title,'"')"Title",
		quotename(a.Department,'"')"Department", quotename(a.Descript,'"')"Description",
		quotename(a.sAMAccountName,'"')"SAMAccountname", quotename(c.lastlogontime,'"')"time_login_to_outlook",
		/*quotename(c.displayName,'"')"for_which_mail_login",*/ quotename(c.sAMAccountName,'"')"email_owner",
		quotename(convert(datetime,c.lastlogontime),'"')"last_time_used_email", quotename(c.lastloggedonuseraccount,'"')"who_last_login",
		quotename(a.whenCreated,'"')"whenCreated", quotename(convert(datetime,d.LastLogon),'"')"LastLogon_to_DC", quotename(d.DC,'"')"DomainController",
		quotename(e.dismissal,'"')"work\dismissal", quotename(e.moveDate,'"')"moveDate", quotename(a.Status,'"')"Status",
		quotename(a.SecID,'"')"SecID", quotename(a.badPwdCount,'"')"BadPwdCount",quotename(a.userAccountControl,'"')"userAccountControl", 
		quotename(a.distinguishedName,'"')"distinguishedName",quotename(a.passwordLastSet,'"')"passwordLastSet",
		quotename(e.namescollision,'"')"namesCollision", '' as Comment
from	(((tADUser a left outer join tUserIDTab b on a.SecID = b.UserID)
		left outer join vExchMBoxes c on a.sAMAccountName = c.sAMAccountName)
		left outer join vADLastLogonDC d on a.sAMAccountName = d.SAMAccountname)
		left outer join tSAP e on b.Tabel_number = e.tabnum
where	(not(a.Status = 'enable' and e.dismissal = '3') or e.dismissal is null)


union all


SELECT  QUOTENAME(a.DisplayName, '"') AS DisplayName, QUOTENAME(a.EmployeeID, '"') AS EmployeeID_AD, QUOTENAME(b.Tabel_number, '"') 
        AS EmployeeID_SAP, QUOTENAME(a.Title, '"') AS Title, QUOTENAME(a.Department, '"') AS Department, QUOTENAME(a.Descript, '"') AS Description, 
        QUOTENAME(a.sAMAccountName, '"') AS SAMAccountname, QUOTENAME(c.lastlogontime, '"') AS time_login_to_outlook, 
        QUOTENAME(c.sAMAccountName, '"') AS email_owner, QUOTENAME(CONVERT(datetime, c.lastlogontime), '"') AS last_time_used_email, 
        QUOTENAME(c.lastloggedonuseraccount, '"') AS who_last_login, QUOTENAME(a.whenCreated, '"') AS whenCreated, QUOTENAME(CONVERT(datetime, 
        d.LastLogon), '"') AS LastLogon_to_DC, QUOTENAME(d.DC, '"') AS DomainController, QUOTENAME(e.dismissal, '"') AS [work\dismissal], 
        QUOTENAME(e.moveDate, '"') AS moveDate, QUOTENAME(a.Status, '"') AS Status, QUOTENAME(a.SecID, '"') AS SecID, QUOTENAME(a.badPwdCount, 
        '"') AS BadPwdCount, QUOTENAME(a.userAccountControl, '"') AS userAccountControl, QUOTENAME(a.distinguishedName, '"') AS distinguishedName, 
        QUOTENAME(a.passwordLastSet, '"') AS passwordLastSet, QUOTENAME(e.namesCollision, '"') AS namesCollision, CASE WHEN ((GETDATE() 
        - a.whenCreated) > 60 and d.DC is null) THEN 'нет входов, создана более 60 дней назад' ELSE '' END AS Comment
FROM	dbo.tADUser AS a LEFT OUTER JOIN
		dbo.tUserIDTab AS b ON a.SecID = b.UserID LEFT OUTER JOIN
		dbo.vExchMBoxes AS c ON a.sAMAccountName = c.sAMAccountName LEFT OUTER JOIN
		dbo.vADLastLogonDC AS d ON a.sAMAccountName = d.SAMAccountname LEFT OUTER JOIN
		dbo.tSAP AS e ON b.Tabel_number = e.tabnum


Проблема в том, что, когда добавляю в union третий запрос, то в итоге все записи первых двух запросов дублируются,но у же с коммнетарием в поле Comment от третьего запроса.
Никак не могу додуматься какое можно поставить условие в третьем запросе,чтобы он тоже как бы "дополнял" итог первых двух запросов?
И возможно-ли такое вообще с начальными знаниями SQL и данным кодом?
Спасибо=)
12 дек 13, 16:48    [15283720]     Ответить | Цитировать Сообщить модератору
 Re: Объединить 3 SELECT без дублирования записей  [new]
Добрый Э - Эх
Guest
На словах расскажи, что сделать пытаешься. И очень неплохо было бы тестовые данные привести, и желаемый результат на них.
12 дек 13, 17:11    [15283844]     Ответить | Цитировать Сообщить модератору
 Re: Объединить 3 SELECT без дублирования записей  [new]
Добрый Э - Эх
Guest
а так очень похоже, что тебе нужен банальный CASE
12 дек 13, 17:11    [15283846]     Ответить | Цитировать Сообщить модератору
 Re: Объединить 3 SELECT без дублирования записей  [new]
k_mak
Member

Откуда:
Сообщений: 33
на словах..сейчас попробую.
Есть 5 таблиц
В итоге мне нужно получить список пользователей для выгрузки в Excel, в котором будут содержаться определенные данные из этих 5 таблиц,и,в соответствии с условиями некоторыми в поле Comment будут добавляться строки типа
  • "Заблокировать учетную запись"
  • "Уч. запись два дня в карантине"
  • "Уч. запись создана более 60 дней назад, нету входов в домен"
  • "Последний вход более 65 дней назад"
  • "Последнйи вход более 95 дней назад"

    если правильно понял по поводу тестовых данных, то приведу пример 1 записи из каждой таблицы
    1.tADUser(выгрузка юзеров из AD)
    есть пользователь с SID 'S-1-5-21-1893336958-3029027543-262923298-37230' и SAMAccountName = 'k.kudrjavceva'

    2.tUserIDtab(тут сопоставление SID и табельных номеров из SAP) -
    S-1-5-21-1893336958-3029027543-262923298-37230 = 7933

    3.в vExchMBoxes хранятся данные о входах пользователей в outlook
    4.в vADLastLogonDC хранятся данные о времени входов пользователей на доменные контроллеры

    5.в tSAP хранятся данные о сотрудниках фирмы (табельники, ФИО, дата увольнения, признак уволен или не уволен и т.д)

    В итоговом варианте хочу получить таблицу, в которой будут все учетки AD, в которой в соответствии с их SID и SAMAccountName(для vExchMBoxes и vADLastLogonDC) будут проставляться соответствующие записи времени входов и, по признаку уволен более 2х дней назад ставить в поле Comment 'Заблокировать!', по признаку уволен менее двух дней назхад будет 'Учю запись два дня в карантине', по признаку не уволен и нету входов 'Уч. запись создана более 60 дней назад, нету входов в домен' и аналогично другие комментари в этмо поле.

    ппц понаписал..не знаю как сюда в удобноваримом виде хотя б по 1 записи вставить в виде таблицы..

    По поводу одного единого CASE я пробовал..но для какого-то условия нужно четко проставлять в конце where...соответсвенно другие услдовия CASE перестают работать..) может я не правильно дулел его..завтра еще покурю посильнее по единому CASE
  • 12 дек 13, 17:33    [15283999]     Ответить | Цитировать Сообщить модератору
     Re: Объединить 3 SELECT без дублирования записей  [new]
    alexeyvg
    Member

    Откуда: Moscow
    Сообщений: 31430
    k_mak
    Здравствуйте, форумчане.
    Подскажите, можно-ли объединить такое
    select	quotename(a.DisplayName,'"')"DisplayName", quotename(a.EmployeeID,'"')"EmployeeID_AD",
    		quotename(b.Tabel_number,'"')"EmployeeID_SAP", quotename(a.Title,'"')"Title",
    		quotename(a.Department,'"')"Department", quotename(a.Descript,'"')"Description",
    		quotename(a.sAMAccountName,'"')"SAMAccountname", quotename(c.lastlogontime,'"')"time_login_to_outlook",
    		/*quotename(c.displayName,'"')"for_which_mail_login",*/ quotename(c.sAMAccountName,'"')"email_owner",
    		quotename(convert(datetime,c.lastlogontime),'"')"last_time_used_email", quotename(c.lastloggedonuseraccount,'"')"who_last_login",
    		quotename(a.whenCreated,'"')"whenCreated", quotename(convert(datetime,d.LastLogon),'"')"LastLogon_to_DC", quotename(d.DC,'"')"DomainController",
    		quotename(e.dismissal,'"')"work\dismissal", quotename(e.moveDate,'"')"moveDate", quotename(a.Status,'"')"Status",
    		quotename(a.SecID,'"')"SecID", quotename(a.badPwdCount,'"')"BadPwdCount",quotename(a.userAccountControl,'"')"userAccountControl", 
    		quotename(a.distinguishedName,'"')"distinguishedName",quotename(a.passwordLastSet,'"')"passwordLastSet",
    		quotename(e.namescollision,'"')"namesCollision",
    		case when ((GETDATE() - e.moveDate) < 2) then '2 дня в карантине' else 'Заблокировать' end as Comment
    from	(((tADUser a left outer join tUserIDTab b on a.SecID = b.UserID)
    		left outer join vExchMBoxes c on a.sAMAccountName = c.sAMAccountName)
    		left outer join vADLastLogonDC d on a.sAMAccountName = d.SAMAccountname)
    		left outer join tSAP e on b.Tabel_number = e.tabnum		
    where	b.Tabel_number = e.tabnum and a.Status = 'enable' and e.dismissal = '3'
    		
    	
    union all
    
    
    select	quotename(a.DisplayName,'"')"DisplayName", quotename(a.EmployeeID,'"')"EmployeeID_AD",
    		quotename(b.Tabel_number,'"')"EmployeeID_SAP", quotename(a.Title,'"')"Title",
    		quotename(a.Department,'"')"Department", quotename(a.Descript,'"')"Description",
    		quotename(a.sAMAccountName,'"')"SAMAccountname", quotename(c.lastlogontime,'"')"time_login_to_outlook",
    		/*quotename(c.displayName,'"')"for_which_mail_login",*/ quotename(c.sAMAccountName,'"')"email_owner",
    		quotename(convert(datetime,c.lastlogontime),'"')"last_time_used_email", quotename(c.lastloggedonuseraccount,'"')"who_last_login",
    		quotename(a.whenCreated,'"')"whenCreated", quotename(convert(datetime,d.LastLogon),'"')"LastLogon_to_DC", quotename(d.DC,'"')"DomainController",
    		quotename(e.dismissal,'"')"work\dismissal", quotename(e.moveDate,'"')"moveDate", quotename(a.Status,'"')"Status",
    		quotename(a.SecID,'"')"SecID", quotename(a.badPwdCount,'"')"BadPwdCount",quotename(a.userAccountControl,'"')"userAccountControl", 
    		quotename(a.distinguishedName,'"')"distinguishedName",quotename(a.passwordLastSet,'"')"passwordLastSet",
    		quotename(e.namescollision,'"')"namesCollision", '' as Comment
    from	(((tADUser a left outer join tUserIDTab b on a.SecID = b.UserID)
    		left outer join vExchMBoxes c on a.sAMAccountName = c.sAMAccountName)
    		left outer join vADLastLogonDC d on a.sAMAccountName = d.SAMAccountname)
    		left outer join tSAP e on b.Tabel_number = e.tabnum
    where	(not(a.Status = 'enable' and e.dismissal = '3') or e.dismissal is null)
    
    
    union all
    
    
    SELECT  QUOTENAME(a.DisplayName, '"') AS DisplayName, QUOTENAME(a.EmployeeID, '"') AS EmployeeID_AD, QUOTENAME(b.Tabel_number, '"') 
            AS EmployeeID_SAP, QUOTENAME(a.Title, '"') AS Title, QUOTENAME(a.Department, '"') AS Department, QUOTENAME(a.Descript, '"') AS Description, 
            QUOTENAME(a.sAMAccountName, '"') AS SAMAccountname, QUOTENAME(c.lastlogontime, '"') AS time_login_to_outlook, 
            QUOTENAME(c.sAMAccountName, '"') AS email_owner, QUOTENAME(CONVERT(datetime, c.lastlogontime), '"') AS last_time_used_email, 
            QUOTENAME(c.lastloggedonuseraccount, '"') AS who_last_login, QUOTENAME(a.whenCreated, '"') AS whenCreated, QUOTENAME(CONVERT(datetime, 
            d.LastLogon), '"') AS LastLogon_to_DC, QUOTENAME(d.DC, '"') AS DomainController, QUOTENAME(e.dismissal, '"') AS [work\dismissal], 
            QUOTENAME(e.moveDate, '"') AS moveDate, QUOTENAME(a.Status, '"') AS Status, QUOTENAME(a.SecID, '"') AS SecID, QUOTENAME(a.badPwdCount, 
            '"') AS BadPwdCount, QUOTENAME(a.userAccountControl, '"') AS userAccountControl, QUOTENAME(a.distinguishedName, '"') AS distinguishedName, 
            QUOTENAME(a.passwordLastSet, '"') AS passwordLastSet, QUOTENAME(e.namesCollision, '"') AS namesCollision, CASE WHEN ((GETDATE() 
            - a.whenCreated) > 60 and d.DC is null) THEN 'нет входов, создана более 60 дней назад' ELSE '' END AS Comment
    FROM	dbo.tADUser AS a LEFT OUTER JOIN
    		dbo.tUserIDTab AS b ON a.SecID = b.UserID LEFT OUTER JOIN
    		dbo.vExchMBoxes AS c ON a.sAMAccountName = c.sAMAccountName LEFT OUTER JOIN
    		dbo.vADLastLogonDC AS d ON a.sAMAccountName = d.SAMAccountname LEFT OUTER JOIN
    		dbo.tSAP AS e ON b.Tabel_number = e.tabnum
    



    Проблема в том, что, когда добавляю в union третий запрос, то в итоге все записи первых двух запросов дублируются,но у же с коммнетарием в поле Comment от третьего запроса.
    Никак не могу додуматься какое можно поставить условие в третьем запросе,чтобы он тоже как бы "дополнял" итог первых двух запросов?
    И возможно-ли такое вообще с начальными знаниями SQL и данным кодом?
    Спасибо=)
    Разумеется, ведь первый и второй запросы - это третий запрос, но с дополнительными условиями.

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

    where NOT (
        (b.Tabel_number = e.tabnum and a.Status = 'enable' and e.dismissal = '3')
        or
        (not(a.Status = 'enable' and e.dismissal = '3') or e.dismissal is null)
    )
    
    12 дек 13, 17:40    [15284068]     Ответить | Цитировать Сообщить модератору
     Re: Объединить 3 SELECT без дублирования записей  [new]
    alexeyvg
    Member

    Откуда: Moscow
    Сообщений: 31430
    Добрый Э - Эх
    а так очень похоже, что тебе нужен банальный CASE
    Согласен, так более правильно, нужно взять третий запрос из union all, а для Comment написать CASE с этими условиями.
    12 дек 13, 17:43    [15284091]     Ответить | Цитировать Сообщить модератору
     Re: Объединить 3 SELECT без дублирования записей  [new]
    k_mak
    Member

    Откуда:
    Сообщений: 33
    alexeyvg
    Спасибо за помощь, код подставил, запрос вернул нужное кол-во записей,но в поле Comment не записались результаты CASE
    но то,что вернулиьс записи без дублирования - уже хорошо! Завтра подумаю еще, сеня уже не варит голова

    p.s вот как раз таки над таким условием для третьего запроса я весь день и думаю..=)
    12 дек 13, 17:47    [15284113]     Ответить | Цитировать Сообщить модератору
     Re: Объединить 3 SELECT без дублирования записей  [new]
    k_mak
    Member

    Откуда:
    Сообщений: 33
    блин...походу дела все оказалось намноооого проще,чем я мудрил..
    в итоге сейчас имею такой запрос, который возвращает нужное количество записей и проставляет необходимые примечания в Comment

    select	quotename(a.DisplayName,'"')"DisplayName", quotename(a.EmployeeID,'"')"EmployeeID_AD",
    		quotename(b.Tabel_number,'"')"EmployeeID_SAP", quotename(a.Title,'"')"Title",
    		quotename(a.Department,'"')"Department", quotename(a.Descript,'"')"Description",
    		quotename(a.sAMAccountName,'"')"SAMAccountname", quotename(c.lastlogontime,'"')"time_login_to_outlook",
    		/*quotename(c.displayName,'"')"for_which_mail_login",*/ quotename(c.sAMAccountName,'"')"email_owner",
    		quotename(convert(datetime,c.lastlogontime),'"')"last_time_used_email", quotename(c.lastloggedonuseraccount,'"')"who_last_login",
    		quotename(a.whenCreated,'"')"whenCreated", quotename(convert(datetime,d.LastLogon),'"')"LastLogon_to_DC", quotename(d.DC,'"')"DomainController",
    		quotename(e.dismissal,'"')"work\dismissal", quotename(e.moveDate,'"')"moveDate", quotename(a.Status,'"')"Status",
    		quotename(a.SecID,'"')"SecID", quotename(a.badPwdCount,'"')"BadPwdCount",quotename(a.userAccountControl,'"')"userAccountControl", 
    		quotename(a.distinguishedName,'"')"distinguishedName",quotename(a.passwordLastSet,'"')"passwordLastSet",
    		quotename(e.namescollision,'"')"namesCollision",
    		case 
    			when ((GETDATE() - e.moveDate) < 2 and b.Tabel_number = e.tabnum and a.Status = 'enable' and e.dismissal = '3') then '2 дня в карантине' 
    			when ((GETDATE() - e.moveDate) > 2 and b.Tabel_number = e.tabnum and a.Status = 'enable' and e.dismissal = '3') then 'Заблокировать!'
    			when ((GETDATE() - a.whenCreated) > 60 and d.LastLogon is null) then 'без входа,создана более 60 дней назад'
    			when ((GETDATE() - d.LastLogon) >= 65 and (GETDATE() - d.LastLogon) < 95) then 'последний вход более 65 дней назад'
    			when ((GETDATE() - d.LastLogon) >= 95) then 'последний вход более 95 дней назад'
    			when ((GETDATE() - a.whenCreated) >= 30 and e.tabnum is null and d.LastLogon is null) then 'нет информации в SAP более месяца от даты создания или пустой lastlogon'
    			else '' end as Comment
    from	(((tADUser a left outer join tUserIDTab b on a.SecID = b.UserID)
    		left outer join vExchMBoxes c on a.sAMAccountName = c.sAMAccountName)
    		left outer join vADLastLogonDC d on a.sAMAccountName = d.SAMAccountname)
    		left outer join tSAP e on b.Tabel_number = e.tabnum	
    


    всем спасибо за помощь. завтра буду проверять, насколько верно оно работает =)
    12 дек 13, 17:58    [15284187]     Ответить | Цитировать Сообщить модератору
     Re: Объединить 3 SELECT без дублирования записей  [new]
    alexeyvg
    Member

    Откуда: Moscow
    Сообщений: 31430
    k_mak
    p.s вот как раз таки над таким условием для третьего запроса я весь день и думаю..=)
    В смысле, как обойтись без union all одним запросом с правильным CASE?

    А вы не думайте, нужно просто сопировать условия из первых двух запросов, так же, как я сделал для условия where.

    Только оформляйте код получше, а то у вас всё что в select записано фактически одрой строкой, плохо видно условия, поэтом и думается плохо.
    12 дек 13, 17:58    [15284192]     Ответить | Цитировать Сообщить модератору
     Re: Объединить 3 SELECT без дублирования записей  [new]
    alexeyvg
    Member

    Откуда: Moscow
    Сообщений: 31430
    k_mak
    блин...походу дела все оказалось намноооого проще,чем я мудрил..
    А. ну да, вот примерно так, и видно всё теперь хорошо.
    12 дек 13, 17:59    [15284195]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить