Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Выбрать и отсортировать по дням первый вход каждого человека  [new]
r12
Member

Откуда:
Сообщений: 63
Здравствуйте, помогите пожалуйста с запросом.
Есть 6 таблиц:
dbo.EVENTS
	[EVENTIME] 	[datetime] NULL, /*дата и время вместе*/
	[DEVID] 	[smallint] NULL, /*для связи с др. таблицей*/
	[MACHINE] 	[smallint] NOT NULL, /*для связи с др. таблицей*/
	[EMPID] 	[int] NULL /*для связи с др. таблицей*/

dbo.EMP
	[ID]		 [int] NOT NULL, /*для связи с др. таблицей*/
	[LASTNAME]	 [nvarchar](32) NOT NULL, /*фамилия*/
	[FIRSTNAME]	 [nvarchar](32) NULL, /*имя*/
	[MIDNAME] 	 [nvarchar](32) NULL, /*отчество*/
	[SSNO] 		 [nvarchar](9) NULL /*табельный номер*/

dbo.READER
	[PANELID] 	[smallint] NULL, /*для связи с др. таблицей*/
	[READERID] 	[smallint] NULL, /*для связи с др. таблицей*/
	[NAMES] 	[nvarchar](30) NULL, /*название территории (двери, турникета)*/
	[enter_exit] 	[nvarchar](7) NULL /*деествие: вход или выход*/

dbo.UDFEMP
	[ID]		[int] NOT NULL, /*для связи с др. таблицей*/
	[DEPT] 		[int] NULL, /*для связи с др. таблицей*/
	[DIVISION] 	[int] NULL, /*для связи с др. таблицей*/

dbo.DEPT
	[ID] 		[int] NULL, /*для связи с др. таблицей*/
	[NAME] 		[nvarchar](60) NULL /*название подразделения*/

dbo.DIVISION
	[ID] 		[int] NOT NULL, /*для связи с др. таблицей*/
	[NAME] 		[nvarchar](32) NULL /*шифр подразделения*/

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

вот мой вариант:
SELECT     CONVERT(varchar(10), MIN(EVENTS.EVENTIME), 104) AS Дата, CONVERT(varchar(10), MIN(EVENTS.EVENTIME), 108) AS Приход, 
                      EMP.LASTNAME AS Фамилия, EMP.FIRSTNAME AS Имя, EVENTS.CARDNUM, READER.enter_exit, READER.NAMES, DIVISION.NAME
FROM         DEPT INNER JOIN
                      EVENTS INNER JOIN
                      EMP ON EVENTS.EMPID = EMP.ID INNER JOIN
                      READER ON EVENTS.MACHINE = READER.PANELID AND EVENTS.DEVID = READER.READERID INNER JOIN
                      UDFEMP ON EMP.ID = UDFEMP.ID INNER JOIN
                      DIVISION ON UDFEMP.DIVISION = DIVISION.ID ON DEPT.ID = UDFEMP.DEPT
GROUP BY EVENTS.CARDNUM, EMP.LASTNAME, DATEDIFF(dd, 0, EVENTS.EVENTIME), EMP.FIRSTNAME, READER.enter_exit, READER.NAMES, 
                      DIVISION.NAME
HAVING      (EMP.LASTNAME = N'Иванов') AND (EMP.FIRSTNAME = N'Иван') AND (READER.enter_exit = N'Вход') AND (DIVISION.NAME = N'10')
ORDER BY Фамилия, MIN(EVENTS.EVENTIME) DESC


HAVING      (EMP.LASTNAME = N'Иванов') AND (EMP.FIRSTNAME = N'Иван')
- условие вписал только для ограничения, вобще планируется без этого, т.е. все работники по подразделениям (DIVISION.NAME = N'10')

Но у нас несколько территорий (дверей) (READER [NAMES] [nvarchar](30) NULL) и поэтому если человек в течении дня защел на несколько территорий то минимальное время (MIN(EVENTS.EVENTIME)) выводится несколько раз. Как я понимаю это происходит из за условия:GROUP BY READER.NAMES. Если это убрать - то запрос не работает. Значит надо как то по другому .... но я не знаю как....

Результат запроса:
 Дата          Приход    Фамилия        Имя      CARDNUM    nter_exit  NAMES
---------- ---------- ----------- ----------- ----------- ---------- ------
27.10.2009 10:56:40 Иванов Иван 60513 Вход ЗУ
27.10.2009 07:55:29 Иванов Иван 60513 Вход ИК
26.10.2009 14:25:17 Иванов Иван 60513 Вход ЗУ
26.10.2009 07:59:33 Иванов Иван 60513 Вход ИК

23.10.2009 10:57:32 Иванов Иван 60513 Вход ЗУ
23.10.2009 07:55:12 Иванов Иван 60513 Вход ИК

22.10.2009 10:55:49 Иванов Иван 60513 Вход ЗУ
22.10.2009 07:57:50 Иванов Иван 60513 Вход ИК
21.10.2009 14:46:33 Иванов Иван 60513 Вход ЗУ
21.10.2009 07:58:41 Иванов Иван 60513 Вход ИК
19.10.2009 07:58:57 Иванов Иван 60513 Вход ИК
16.10.2009 07:58:02 Иванов Иван 60513 Вход ИК

Версия SQL
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
База получается путем репликации с SQL 2000

Можно еще зать как вариант
SELECT CONVERT(date, EVENTS.EVENTIME) AS date, CONVERT(time, EVENTS.EVENTIME) AS time,......
но когда пишу выбрать MIN из CONVERT(time, EVENTS.EVENTIME) AS time - пишет ошибку, может конечно я синтексиса незнаю

В дальнейшем хочу выбрать MAX - ну это тоже самое, только min на max поменять. А после этого хочу все это объединить в один запрос, что бы появились колонки "выход" и "время" (это уже сложнее)и потом посчитать разницу между выходом и входом, т.е. найти кол-во отработанного времени, а если будет CONVERT(varchar(10), MIN(EVENTS.EVENTIME), 104) - то тогда как мне кажется считать время нельзя, или я просто незнаю как это делать. Потом хочу все это поместить в Reporting Services, вот такие вот планы.....

Еще попробую предугадать некоторые вопросы:
Да, люди могут входить и выходить много раз в течение дня, но у нас много территорий и переход с территории на территорию занимает какое то время, поэтому решено смотреть только приход и уход - так сказать контроль за соблюдением трудового режима, а уж что работник будет делать в течении дня - проблема непосредственного руководителя подразделения (т.е. он должен загружать работой).
19 ноя 09, 11:25    [7949339]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать и отсортировать по дням первый вход каждого человека  [new]
aleks2
Guest
-- все ПЕРВЫЕ входы КАЖДОГО, кто входил
select [EMPID],  min(T.[EVENTIME]) [EVENTIME]
FROM
dbo.EVENTS T 
INNER JOIN 
(select * FROM dbo.READER WHERE [enter_exit]=N'Вход') R
ON 
T.EVENTS.MACHINE = R.PANELID AND T.DEVID = R.READERID
GROUP BY T.[EMPID],  DATEDIFF(day,0,T.[EVENTIME])

-- ежели надо и другие поля  - джойнте слева/справа все чо надо
-- фио, двери и пр...
19 ноя 09, 12:37    [7950028]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать и отсортировать по дням первый вход каждого человека  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
r12,

достаточно пронумеровать записи запроса функцией
ROW_NUMBER()OVER(PARTITION BY CONVERT(CHAR(8),EVENTS.EVENTIME,112),EMP.ID,DIVISION.ID ORDER BY EVENTS.EVENTIME)
и отобрать только записи с первым номером.
19 ноя 09, 12:39    [7950044]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать и отсортировать по дням первый вход каждого человека  [new]
aleks2
Guest
Однако, предвосхищая недовольство, замечу:
среди READER-ов надо бы выделить "внешние входы", а то получается полная фигня.
19 ноя 09, 12:41    [7950063]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать и отсортировать по дням первый вход каждого человека  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
iap
r12,

достаточно пронумеровать записи запроса функцией
ROW_NUMBER()OVER(PARTITION BY CONVERT(CHAR(8),EVENTS.EVENTIME,112),EMP.ID,DIVISION.ID ORDER BY EVENTS.EVENTIME)
и отобрать только записи с первым номером.
Естественно, GROUP BY и MIN в этом случае не нужны.
19 ноя 09, 12:41    [7950070]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать и отсортировать по дням первый вход каждого человека  [new]
aleks2
Guest
iap
iap
r12,

достаточно пронумеровать записи запроса функцией
ROW_NUMBER()OVER(PARTITION BY CONVERT(CHAR(8),EVENTS.EVENTIME,112),EMP.ID,DIVISION.ID ORDER BY EVENTS.EVENTIME)
и отобрать только записи с первым номером.


А потом жалиться здеся на тормоза....
19 ноя 09, 12:50    [7950156]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать и отсортировать по дням первый вход каждого человека  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
aleks2
iap
iap
r12,

достаточно пронумеровать записи запроса функцией
ROW_NUMBER()OVER(PARTITION BY CONVERT(CHAR(8),EVENTS.EVENTIME,112),EMP.ID,DIVISION.ID ORDER BY EVENTS.EVENTIME)
и отобрать только записи с первым номером.


А потом жалиться здеся на тормоза....
Ну откуда такой пессимизм?
Всё будет хорошо!
Кроме того, кто мешает сравнить предлагаемые решения и выбрать лучшее?
19 ноя 09, 12:56    [7950206]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать и отсортировать по дням первый вход каждого человека  [new]
aleks2
Guest
iap
Ну откуда такой пессимизм?
Всё будет хорошо!
Кроме того, кто мешает сравнить предлагаемые решения и выбрать лучшее?


Я те так сравню

1. ROW_NUMBER over... эквивалентно группировке.
2. Только идиоты соединяют все, шо можно, а потом группируют/ROW_NUMBER-ят . Умные сначала группируют/ROW_NUMBER-ят минимальный набор таблиц (в идеале ОДНУ) - потом присобачивают остальное.
3. А в остальном ROW_NUMBER будет не хуже и не лучше.
19 ноя 09, 13:38    [7950586]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать и отсортировать по дням первый вход каждого человека  [new]
r12
Member

Откуда:
Сообщений: 63
aleks2,

Спасибо большое, заработало, только я немного переделал, почему то с таблицей T не работало, сделал вот так:
SELECT     EVENTS.EMPID, MIN(EVENTS.EVENTIME) AS EVENTIME, EMP.LASTNAME, EMP.FIRSTNAME 
FROM         EVENTS INNER JOIN
                          (SELECT     READERDESC, PANELID, READERID, NAMES, enter_exit
                            FROM          READER
                            WHERE      (enter_exit = N'Вход')) AS R ON EVENTS.MACHINE = R.PANELID AND EVENTS.DEVID = R.READERID INNER JOIN
                      EMP ON EVENTS.EMPID = EMP.ID
GROUP BY EVENTS.EMPID, DATEDIFF(day, 0, EVENTS.EVENTIME), EMP.LASTNAME, EMP.FIRSTNAME
--HAVING      (EMP.LASTNAME = N'Иванов') AND (EMP.FIRSTNAME = N'Иван')
ORDER BY DATEDIFF(day, 0, EVENTS.EVENTIME) DESC


А когда с таблицей T (или представлением или .... не знаю как правильно это называется) пишет ошибку:

Сообщение 207, уровень 16, состояние 1, строка 19
Недопустимое имя столбца "EVENTS".

И еще вопрос по MIN(EVENTS.EVENTIME) - а можно как то сделать что бы выводилось отдельно дата и отдельно время
что то вроде этого: (CONVERT(date, EVENTS.EVENTIME) AS date, CONVERT(time, EVENTS.EVENTIME) AS time,) ну и что бы это все работало :)


aleks2
Однако, предвосхищая недовольство, замечу:
среди READER-ов надо бы выделить "внешние входы", а то получается полная фигня.

Reader - там только "внешние входы", внутри помещений у нас считыватели не стоят.
19 ноя 09, 14:28    [7950989]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать и отсортировать по дням первый вход каждого человека  [new]
r12
Member

Откуда:
Сообщений: 63
aleks2,
заработало вот так:

SELECT     EVENTS.EMPID, MIN(CONVERT(date, EVENTS.EVENTIME)) AS EVENTIME, MIN(CONVERT(time, EVENTS.EVENTIME)) AS EVENTIME1, EMP.LASTNAME, EMP.FIRSTNAME 
FROM         EVENTS INNER JOIN
                          (SELECT     READERDESC, PANELID, READERID, NAMES, enter_exit
                            FROM          READER
                            WHERE      (enter_exit = N'Вход')) AS R ON EVENTS.MACHINE = R.PANELID AND EVENTS.DEVID = R.READERID INNER JOIN
                      EMP ON EVENTS.EMPID = EMP.ID
GROUP BY EVENTS.EMPID, DATEDIFF(day, 0, EVENTS.EVENTIME), EMP.LASTNAME, EMP.FIRSTNAME
HAVING      (EMP.LASTNAME = N'Иванов') AND (EMP.FIRSTNAME = N'Иван')
ORDER BY DATEDIFF(day, 0, EVENTS.EVENTIME) DESC
19 ноя 09, 14:34    [7951070]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать и отсортировать по дням первый вход каждого человека  [new]
aleks2
Guest
r12
aleks2,
заработало вот так:

SELECT     EVENTS.EMPID, MIN(CONVERT(date, EVENTS.EVENTIME)) AS EVENTIME, MIN(CONVERT(time, EVENTS.EVENTIME)) AS EVENTIME1, EMP.LASTNAME, EMP.FIRSTNAME 
FROM         EVENTS INNER JOIN
                          (SELECT     READERDESC, PANELID, READERID, NAMES, enter_exit
                            FROM          READER
                            WHERE      (enter_exit = N'Вход')) AS R ON EVENTS.MACHINE = R.PANELID AND EVENTS.DEVID = R.READERID INNER JOIN
                      EMP ON EVENTS.EMPID = EMP.ID
GROUP BY EVENTS.EMPID, DATEDIFF(day, 0, EVENTS.EVENTIME), EMP.LASTNAME, EMP.FIRSTNAME
HAVING      (EMP.LASTNAME = N'Иванов') AND (EMP.FIRSTNAME = N'Иван')
ORDER BY DATEDIFF(day, 0, EVENTS.EVENTIME) DESC


Редкостный отстой...

select X.*, CONVERT(date, EVENTIME) AS EVENTIME, CONVERT(time, EVENTIME) AS EVENTIME1, EMP.LASTNAME, EMP.FIRSTNAME 
FROM
(SELECT E.EMPID, MIN(E.EVENTIME) EVENTIME
	FROM         
	EVENTS E
	INNER JOIN
	(SELECT * FROM READER WHERE  (enter_exit = N'Вход')) AS R 
	ON E.MACHINE = R.PANELID AND E.DEVID = R.READERID 
	WHERE E.EMPID in (select ID FROM EMP WHERE LASTNAME = N'Иванов' AND FIRSTNAME = N'Иван')
	GROUP BY E.EMPID, DATEDIFF(day, 0, E.EVENTIME)
) X
INNER JOIN
EMP ON X.EMPID = EMP.ID
ORDER BY EVENTIME DESC
будет быстрее.

Запомни:
1. Все шо можно надо отфильтровать ДО группировки.
2. Все шо можно следует JOIN-ить ПОСЛЕ группировки.
3. Серверу то по-барабану, а тебе ждать придется.
19 ноя 09, 16:41    [7952249]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать и отсортировать по дням первый вход каждого человека  [new]
r12
Member

Откуда:
Сообщений: 63
aleks2,


Спасибо большое, все работает :)
Я переделал чтобы выборка шла не по фамилии, а по подразделению. Только вот мне кажется что немного не корректно (отфильтровываю не в том месте), наверно выбор подразделения надо поставить на то место где стоит выбор ФИО, но у меня не получается, все время пишет ошибку.

SELECT     X.EMPID, X.EVENTIME, CONVERT(date, X.EVENTIME) AS Date, CONVERT(time, X.EVENTIME) AS Time, EMP_1.LASTNAME, EMP_1.FIRSTNAME, 
                      DIVISION.NAME
FROM         DIVISION INNER JOIN
                      UDFEMP ON DIVISION.NAME = UDFEMP.DIVISION INNER JOIN
                          (SELECT     E.EMPID, MIN(E.EVENTIME) AS EVENTIME
                            FROM          EVENTS AS E INNER JOIN
                                                       (SELECT     PANELID, READERID, NAMES, enter_exit
                                                         FROM          READER
                                                         WHERE      (enter_exit = N'Вход')) AS R ON E.MACHINE = R.PANELID AND E.DEVID = R.READERID
                         /*   WHERE      (E.EMPID IN
                                                       (SELECT     ID
                                                         FROM          EMP
                                                         /*WHERE      (LASTNAME = 'Иванов') AND (FIRSTNAME = N'Иван')*/))*/
                            GROUP BY E.EMPID, DATEDIFF(day, 0, E.EVENTIME)) AS X INNER JOIN
                      EMP AS EMP_1 ON X.EMPID = EMP_1.ID ON UDFEMP.ID = EMP_1.ID
WHERE     (DIVISION.NAME = N'10')
ORDER BY X.EVENTIME DESC
20 ноя 09, 13:59    [7956573]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать и отсортировать по дням первый вход каждого человека  [new]
r12
Member

Откуда:
Сообщений: 63
aleks2,

И еще вопрос, что бы выбрать последний Выход, я поменял вот эту часть (MIN на MAX, вход на выход):

 (SELECT     E.EMPID, MAX(E.EVENTIME) AS EVENTIME
                            FROM          EVENTS AS E INNER JOIN
                                              (SELECT     PANELID, READERID, NAMES, enter_exit
                                              FROM          READER
                                              WHERE      (enter_exit = N'Выход')) AS R 
                                              ON E.MACHINE = R.PANELID AND E.DEVID = R.READERID

А можно как нибудь это все соединить в один запрос чтобы было:
дата - приход - время - ФИО - подразделение - уход - время
20 ноя 09, 14:09    [7956644]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать и отсортировать по дням первый вход каждого человека  [new]
aleks2
Guest
r12
aleks2,

А можно как нибудь это все соединить в один запрос чтобы было:
дата - приход - время - ФИО - подразделение - уход - время


Ясен пень - можно.

Ты чо JOIN входов с выходами написать не могешь?
20 ноя 09, 15:36    [7957293]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать и отсортировать по дням первый вход каждого человека  [new]
aleks2
Guest
r12
Я переделал чтобы выборка шла не по фамилии, а по подразделению. Только вот мне кажется что немного не корректно (отфильтровываю не в том месте), наверно выбор подразделения надо поставить на то место где стоит выбор ФИО, но у меня не получается, все время пишет ошибку.

SELECT     X.EMPID, X.EVENTIME, CONVERT(date, X.EVENTIME) AS Date, CONVERT(time, X.EVENTIME) AS Time, EMP_1.LASTNAME, EMP_1.FIRSTNAME, 
                      DIVISION.NAME
FROM         DIVISION INNER JOIN
                      UDFEMP ON DIVISION.NAME = UDFEMP.DIVISION INNER JOIN
                          (SELECT     E.EMPID, MIN(E.EVENTIME) AS EVENTIME
                            FROM          EVENTS AS E INNER JOIN
                                                       (SELECT     PANELID, READERID, NAMES, enter_exit
                                                         FROM          READER
                                                         WHERE      (enter_exit = N'Вход')) AS R ON E.MACHINE = R.PANELID AND E.DEVID = R.READERID
                         /*   WHERE      (E.EMPID IN
                                                       (SELECT     ID
                                                         FROM          EMP
                                                         /*WHERE      (LASTNAME = 'Иванов') AND (FIRSTNAME = N'Иван')*/))*/
                            GROUP BY E.EMPID, DATEDIFF(day, 0, E.EVENTIME)) AS X INNER JOIN
                      EMP AS EMP_1 ON X.EMPID = EMP_1.ID ON UDFEMP.ID = EMP_1.ID
WHERE     (DIVISION.NAME = N'10')
ORDER BY X.EVENTIME DESC


Блин, к чему это громадье джойнов?

select X.EMPID, X.EVENTIME, CONVERT(date, EVENTIME) AS EVENTIME, CONVERT(time, EVENTIME) AS EVENTIME1
	, EMP.LASTNAME, EMP.FIRSTNAME 
	, N'10' as NAME
FROM
(SELECT E.EMPID, MIN(E.EVENTIME) EVENTIME
	FROM         
	EVENTS E
	INNER JOIN
	(SELECT * FROM READER WHERE  (enter_exit = N'Вход')) AS R 
	ON E.MACHINE = R.PANELID AND E.DEVID = R.READERID 
	WHERE 
	E.EMPID in (
			select ID FROM EMP E1 inner join UDFEMP U1 ON E1.ID=U1.ID 
			WHERE U1.DIVISION=N'10'
		)
	GROUP BY E.EMPID, DATEDIFF(day, 0, E.EVENTIME)
) X
INNER JOIN
EMP ON X.EMPID = EMP.ID
ORDER BY EVENTIME DESC
21 ноя 09, 08:07    [7959443]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать и отсортировать по дням первый вход каждого человека  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
aleks2
Блин, к чему это громадье джойнов?
Нафига это громадье подзапросов? Бу-э-э
Чем не устраивает вариант? Вот выровню его:
SELECT	 X.EmpID
	,X.EvenTime
	,Convert(Date,EvenTime)	AS EvenDate
	,Convert(Time,EvenTime)	AS EvenTime
	,E.LastName
	,E.FirstName
	,U.Division		AS Name
FROM	     dbo.UdfEmp	U
	JOIN (
	SELECT	 E.EmpID
		,Min(E.EvenTime)	AS EvenTime
	FROM	     dbo.Events	E
		JOIN dbo.Reader	R ON R.PanelID		= E.Machine
				 AND R.ReaderID 	= E.DevID
				 AND R.Enter_Exit	= N'Вход'	-- Бу-э-э
	GROUP BY E.EmpID
		,DateDiff(Day,0,E.EvenTime)
		)	X ON X.EmpID = U.ID
	JOIN dbo.Emp	E ON E.ID = U.ID
WHERE	U.Division = N'10'
ORDER BY X.EvenTime DESC
Надеюсь не ошибся.
21 ноя 09, 12:37    [7959640]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать и отсортировать по дням первый вход каждого человека  [new]
aleks2
Guest
Mnior
aleks2
Блин, к чему это громадье джойнов?
Нафига это громадье подзапросов? Бу-э-э
Чем не устраивает вариант? Вот выровню его:
SELECT	 X.EmpID
	,X.EvenTime
	,Convert(Date,EvenTime)	AS EvenDate
	,Convert(Time,EvenTime)	AS EvenTime
	,E.LastName
	,E.FirstName
	,U.Division		AS Name
FROM	     dbo.UdfEmp	U
	JOIN (
	SELECT	 E.EmpID
		,Min(E.EvenTime)	AS EvenTime
	FROM	     dbo.Events	E
		JOIN dbo.Reader	R ON R.PanelID		= E.Machine
				 AND R.ReaderID 	= E.DevID
				 AND R.Enter_Exit	= N'Вход'	-- Бу-э-э
	GROUP BY E.EmpID
		,DateDiff(Day,0,E.EvenTime)
		)	X ON X.EmpID = U.ID
	JOIN dbo.Emp	E ON E.ID = U.ID
WHERE	U.Division = N'10'
ORDER BY X.EvenTime DESC
Надеюсь не ошибся.


Тута две глупости:
1. Группировка по ИЗБЫТОЧНОМУ набору.
2. Неумение читать.
aleks2

Запомни:
1. Все шо можно надо отфильтровать ДО группировки.
2. Все шо можно следует JOIN-ить ПОСЛЕ группировки.
3. Серверу то по-барабану, а тебе ждать придется.


Конечно, есть варианты данных, когда твой запрос будет быстрее, но в общем случае - это отстой.
21 ноя 09, 15:10    [7959830]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать и отсортировать по дням первый вход каждого человека  [new]
iljy
Member

Откуда:
Сообщений: 8711
aleks2

Тута две глупости:
1. Группировка по ИЗБЫТОЧНОМУ набору.
2. Неумение читать.
aleks2

Запомни:
1. Все шо можно надо отфильтровать ДО группировки.
2. Все шо можно следует JOIN-ить ПОСЛЕ группировки.
3. Серверу то по-барабану, а тебе ждать придется.


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

Тута одна глупость -
aleks2

Не считай оптимизатор глупее себя.

У этих запросов:
select d.id, max_val, Name
from StrLevel3 m join
	 (select id, max(value1) max_val from Data group by id) d
	on m.id=d.id
--where m.id in (1,2,7,8,40)


select d.id,  max_val, Name
from (select m.id, Name, max(value1) max_val
	  from StrLevel3 m join Data d on m.id = d.id
	  group by m.id, m.Name) d
--where id in (1,2,7,8,40)
планы идентичны даже если раскоментарить where.

PS Кстати за тот пример спасибо. Запрос надо дорабатывать напильником, но если даработать - план получается интересный. Правда время выполнения как у APPLY, а воспроизвести его на своих данных смог только после долгого изврата и доведения запроса до полностью непригодного на практике, но что скуль в принипе такое умеет - уже приятно. Что-то подобное я пробовал на 7 и 2000, но тогда получалась фигня и я забил.
21 ноя 09, 16:15    [7959892]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать и отсортировать по дням первый вход каждого человека  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
aleks2
но в общем случае - это отстой.
Писать на асме? 2009 год на дворе!
Панимаете, иногда простота итак немалого кода в стопяцот раз необходимее, чем почти мистическая вероятность "сваливания" оптимизатора. А "поправить" те редкие случаи проще и всегда успеется, чем огород городить на ровном месте, где не попадя.

Оптимизация на более глобальном уровне (структурном, стратегическом) в мульоны раз эффективнее чем императивное "мелкотничество".

Нет, я не призываю "не думать", наоборот, думать больше ещё в тыщу раз.

aleks2, вы правы, но чтоб вопить из-за этого "громадье джойнов" - это вы черезчур перестарались.
Прям как я со своим минимализмом.
21 ноя 09, 22:04    [7960265]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать и отсортировать по дням первый вход каждого человека  [new]
aleks2
Guest
Mnior
aleks2
но в общем случае - это отстой.
Писать на асме? 2009 год на дворе!
Панимаете, иногда простота итак немалого кода в стопяцот раз необходимее, чем почти мистическая вероятность "сваливания" оптимизатора. А "поправить" те редкие случаи проще и всегда успеется, чем огород городить на ровном месте, где не попадя.

Оптимизация на более глобальном уровне (структурном, стратегическом) в мульоны раз эффективнее чем императивное "мелкотничество".

Нет, я не призываю "не думать", наоборот, думать больше ещё в тыщу раз.

aleks2, вы правы, но чтоб вопить из-за этого "громадье джойнов" - это вы черезчур перестарались.
Прям как я со своим минимализмом.


1. JOIN выполняется оптимизатором ВСЕГДА, независимо от того, используется или нет шо-либо из присоединенной таблицы. Это документировано и воля ваша НЕ принимать этого во внимание.

2. Вопрос "проще и понятнее" - вопрос субъективный... Ну вот мне, например,
 ON ...  AND R.Enter_Exit = N'Вход'	-- Бу-э-э
менее понятно, чем
(SELECT * FROM READER WHERE  (enter_exit = N'Вход'))
.
Но тута хоть эквивалентно по быстродействию...

3. В оригинале тредстартера на ОДИН join больше, чем в моем варианте, т.е. сокращение 25%... а в вашем - стока же, что и в моем. Так шо... чья бы корова мычала.
22 ноя 09, 11:08    [7960764]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать и отсортировать по дням первый вход каждого человека  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
aleks2
1. JOIN выполняется оптимизатором ВСЕГДА
Не понял к чему вы это. А вот с LEFT JOIN не так. ;)
aleks2
2. вопрос субъективный...
Мне понятно :), но в принципе лучше в WHERE но без подзапроса (так кошернее :) ).
aleks2
3. В оригинале тредстартера на ОДИН join больше
Упс.
23 ноя 09, 02:04    [7962089]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать и отсортировать по дням первый вход каждого человека  [new]
aleks2
Guest
Mnior
aleks2
1. JOIN выполняется оптимизатором ВСЕГДА
Не понял к чему вы это. А вот с LEFT JOIN не так. ;)


Расскажи это в ясельках.

LEFT JOIN способен изменить число строк результирующего запроса => его необходимо выполнить.

Шоб не подвергать твой интеллект чрезмерному испытанию, пример

Табл. А
id str
1 'a'

Табл. В
id str
1 'a'
2 'a'

select A.*
FROM A LEFT OUTER JOIN B ON A.str=B.str
23 ноя 09, 06:51    [7962182]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать и отсортировать по дням первый вход каждого человека  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
aleks2, не веди себя как iljy, всё все ясно уловили.

Что OUTER работает не так как INNER. А именно, что для INNER не нуна подсоединять только при случае [1], а для OUTER при [0,1]. Если будет PK - LEFT может и отвалится.
Этим пользуются повсеместно, а я повально - даже где не нужно :).

+ KO
USE tempdb
GO
CREATE TABLE dbo.PK (ID Int PRIMARY KEY)
GO
CREATE TABLE dbo.FK (ID Int IDENTITY PRIMARY KEY, FK Int REFERENCES dbo.PK(ID))
GO
INSERT	dbo.PK VALUES (1);
INSERT	dbo.FK VALUES (1);
INSERT	dbo.FK VALUES (1);
GO
SET SHOWPLAN_TEXT ON
GO
SELECT	F.ID
FROM	          dbo.FK F
	LEFT JOIN dbo.PK P ON P.ID = F.FK
GO
SET SHOWPLAN_TEXT OFF
GO
DROP TABLE dbo.FK
DROP TABLE dbo.PK
GO
StmtText
|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[FK].[PK__FK__3214EC270A9D95DB] AS [F]))


Да, мне не очень понятно почему INNER не отваливается (при FK NOT NULL). Может просвятите нам - детсадовцам.
23 ноя 09, 10:06    [7962509]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать и отсортировать по дням первый вход каждого человека  [new]
r12
Member

Откуда:
Сообщений: 63
aleks2,

Все работает :)
Немного изменил, для связи с таблицами добавил строчки
INNER JOIN DIVISION AS DIV1 ON U1.DIVISION = DIV1.ID
там просто у меня 2 таблицы с DIVISION: UDFEMP.DIVISION и DIVISION.NAME, а то он без этого не то выбирал.

SELECT     X.EMPID, X.EVENTIME, CONVERT(date, X.EVENTIME) AS EVENTIME, CONVERT(time, X.EVENTIME) AS EVENTIME1, EMP.LASTNAME, EMP.FIRSTNAME
               -- , DIV1.NAME ,  R.NAMES
               --, E.EMPID
FROM         (SELECT     E.EMPID, MIN(E.EVENTIME) AS EVENTIME
                       FROM          EVENTS AS E INNER JOIN
                                                  (SELECT     PANELID, READERID, NAMES, enter_exit
                                                    FROM          READER
                                                    WHERE      (enter_exit = N'Вход')) AS R ON E.MACHINE = R.PANELID AND E.DEVID = R.READERID
                       WHERE      (E.EMPID IN
                                                  (SELECT     E1.ID
                                                    FROM          EMP AS E1 INNER JOIN
                                                                           UDFEMP AS U1 ON E1.ID = U1.ID INNER JOIN
                                                                           DIVISION AS DIV1 ON U1.DIVISION = DIV1.ID
                                                 WHERE      (DIV1.NAME = N'20')))
                       GROUP BY E.EMPID, DATEDIFF(day, 0, E.EVENTIME)) AS X INNER JOIN
                      EMP ON X.EMPID = EMP.ID
                    
ORDER BY X.EVENTIME DESC

И у меня вопросы:
как можно вывести на экран поля:
1. DIV1.NAME (DIVISION.NAME) (10), потому что как у вас (, N'10' as NAME) - он выводит 10, а эта цифра будет меняться в зависимости от подразделения
2. READER.enter_exit (R.enter_exit) и READER.NAMES()R.NAMES)
3. И некоторые поля из таблицы UDFEMP (там забиты должности, телефоны и т.п.)
Наверно надо создавать еще какие то временные таблицы на подобие X, только как это сделать не знаю..........
Пишу вот так -- , DIV1.NAME , R.NAMES - пишет ошибка - не найдена таблица
может их надо создавать после вот этого -
GROUP BY E.EMPID, DATEDIFF(day, 0, E.EVENTIME)) AS X ?

И еще по поводу MIN и MAX (JOIN)
MIN - это таблица X, а чтобы склеить с MAX - надо создать еще одну таблицу (например X1) и связать ее с Х через EMP.ID, но так может не заработать (у меня не получается) т.к. в таблицах Х1 и Х будут разное дата и время (EVENTIME), если только сравнивать по дням (CONVERT(date, X.EVENTIME)) - но как я не знаю.........

Вот мой вариант:
SELECT     X.EMPID, CONVERT(date, X.EVENTIME) AS Date, CONVERT(time, X.EVENTIME) AS Time, EMP_1.LASTNAME, EMP_1.FIRSTNAME, DIVISION.NAME, 
                      D.EMPID AS Expr1, EMP_1.LASTNAME AS Expr3, EMP_1.FIRSTNAME AS Expr4, CONVERT(date, D.EVENTIME) AS Date1, CONVERT(time, D.EVENTIME) 
                      AS Time1
FROM         (SELECT     E.EMPID, MIN(E.EVENTIME) AS EVENTIME
                       FROM          EVENTS AS E INNER JOIN
                                                  (SELECT     PANELID, READERID, NAMES, enter_exit
                                                    FROM          READER
                                                    WHERE      (enter_exit = N'Вход')) AS R ON E.MACHINE = R.PANELID AND E.DEVID = R.READERID
                       GROUP BY E.EMPID, DATEDIFF(day, 0, E.EVENTIME)) AS X INNER JOIN
                      DIVISION INNER JOIN
                      UDFEMP ON DIVISION.NAME = UDFEMP.DIVISION INNER JOIN
                      EMP AS EMP_1 ON UDFEMP.ID = EMP_1.ID ON X.EMPID = EMP_1.ID INNER JOIN
                          (SELECT     W.EMPID, MAX(W.EVENTIME) AS EVENTIME
                            FROM          EVENTS AS W INNER JOIN
                                                       (SELECT     PANELID, READERID, NAMES, enter_exit
                                                         FROM          READER AS READER_1
                                                         WHERE      (enter_exit = N'Выход')) AS Q ON W.MACHINE = Q.PANELID AND W.DEVID = Q.READERID
                            GROUP BY W.EMPID, DATEDIFF(day, 0, W.EVENTIME)) AS D ON EMP_1.ID = D.EMPID
WHERE     (DIVISION.NAME = N'10')
ORDER BY Date DESC, X.EVENTIME DESC, D.EVENTIME DESC

Сам запрос старый до:
aleks2
Блин, к чему это громадье джойнов?

но смысл наверно все равно один и тот же.....
Он что то там выдает, но не правильно (дни друг с другом не совпадают, а там где MIN они вообще повторяются) и долго думают........

Вариант "Mnior" тоже работает, я делал без подразделений ( --WHERE (DIV1.NAME = N'10'))
разница в 1 секунду (aleks2 -7 сек, Mnior - 8 сек)
23 ноя 09, 11:40    [7963189]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать и отсортировать по дням первый вход каждого человека  [new]
aleks2
Guest
r12
И еще по поводу MIN и MAX (JOIN)


Честно говоря, скучно писать тривиальщину

SELECT E.EMPID
, MIN(case WHEN enter_exit = N'Вход' THEN E.EVENTIME END) as EnterTIME
, MAX(case WHEN enter_exit = N'Выход' THEN E.EVENTIME END) as ExitTIME
FROM         
EVENTS E
INNER JOIN
READER AS R 
ON E.MACHINE = R.PANELID AND E.DEVID = R.READERID 
WHERE E.EMPID in (select ID FROM EMP WHERE LASTNAME = N'Иванов' AND FIRSTNAME = N'Иван')
GROUP BY E.EMPID, DATEDIFF(day, 0, E.EVENTIME)

Будем надеятся - дальше сообразишь сам.
23 ноя 09, 19:08    [7966688]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить