:: Не фоксом единым
• Войти
SQL SELECT минимальных значений.
FoxProg
Автор

Сообщений: 150
Дата регистрации: 27.11.2012
FoxProg
26.12.18 15:26:17
Добрый день!
Что-то никак не соображу, как правильно сделать SELECT.
Есть две таблицы spisok - список людей (id I - уникальный код, fam C - фамилия) и otpusk - таблица, в каком месяце человек брал отпуск (id I - уникальный код, id_fam I - ссылка на spisok.id, god I - год, mes I - месяц)
Нужно создать таблицу с информацией когда кто ходил в отпуск за указанный период, например с февраля 2018 по декабрь 2018
Созадю процедурку 
DECLARE @cDate1 varchar(6)

DECLARE @cDate2 varchar(6)

SET @cDate1='201802'

SET @cDate2='201812'

SELECT otpusk.id, spisok.fam, otpusk.god, otpusk.mes

 FROM otpusk, spisok

 WHERE convert(varchar(8),god)+CASE WHEN mes<10 THEN '0' ELSE '' END+convert(varchar(2),mes) BETWEEN @cDate1 AND @cDate2

 AND otpusk.id_fam=spisok.id

 ORDER BY fam, god, mes
Результатом является табличка примерно такая: 
id     fam        god   mes
10069  Анисимов   2018  2
10409  Анисимов   2018  5
10632  Анисимов   2018  8
10895  Анисимов   2018  12
10215  Бабич      2018  4
10223  Бабич      2018  5
10255  Бабич      2018  9
10365  Бовкун     2018  3
10634  Бовкун     2018  8
и т.д.
Нужно изменить SELECT так, чтобы он выдавал только минимальные значения для каждого человека, т.е. должно быть такое 
id     fam        god   mes
10069  Анисимов   2018  2
10215  Бабич      2018  4
10365  Бовкун     2018  3
и т.д.
Ratings: 0 negative/0 positive
Re: SQL SELECT минимальных значений.
PaulWist

Сообщений: 14620
Дата регистрации: 01.04.2004
PaulWist
26.12.18 16:10:04


declare @otpusk TABLE (ID int, ID_Fam varchar(50), God int, Mes int)



insert into @otpusk 

select

10069, 'Анисимов', 2018,  2

union select

10409, 'Анисимов',   2018,  5

union select

10632,  'Анисимов',   2018,  8

union select

10895,  'Анисимов',   2018,  12

union select

10215,  'Бабич' ,     2018,  4

union select

10223,  'Бабич',      2018,  5

union select

10255,  'Бабич',      2018,  9

union select

10365,  'Бовкун',     2018,  3

union select

10634,  'Бовкун' ,    2018,  8



select ID_Fam, min(God) God, min(Mes) Mes

from @otpusk otpusk

group by ID_Fam


------------------
Есть многое на свете, друг Горацио...
Что и не снилось нашим мудрецам.
(В.Шекспир Гамлет)
Ratings: 0 negative/0 positive
Re: SQL SELECT минимальных значений.
Владимир Максимов

Сообщений: 14100
Откуда: Москва
Дата регистрации: 02.09.2000
Владимир Максимов
26.12.18 16:36:19
Если года разные, то два минимума могут дать не корректный результат

Тут проще сделать "в лоб" переформулировав запрос "на человеческом языке" следующим образом

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



DECLARE    @yearFrom    int = 2018,

        @yearTo        int = 2018,

        @monthFrom    int = 1,

        @monthTo    int = 12



-- Сравнение года и месяца без преобразований



SELECT spisok.id, spisok.fam, otpusk.id, otpusk.god, otpusk.mes  

FROM spisok

inner join otpusk on otpusk.id_fam=spisok.id  

where    (otpusk.god > @yearFrom or (otpusk.god = @yearFrom and otpusk.mes >= @monthFrom))

    and (otpusk.god < @yearTo or (otpusk.god = @yearTo and otpusk.mes <= @monthTo))

    and    not exists(select 'x' 

                from otpusk as otpusk2

                where otpusk2.id_fam=otpusk.id_fam

                    and (otpusk2.god > @yearFrom or (otpusk2.god = @yearFrom and otpusk2.mes >= @monthFrom))

                    and (otpusk2.god < @yearTo or (otpusk2.god = @yearTo and otpusk2.mes <= @monthTo))

                    -- Поиск записи раньше

                    and (otpusk2.god < otpusk.god

                        or (otpusk2.god = otpusk.god and otpusk2.mes < otpusk.mes)

                        )

                )



ORDER BY spisok.fam, otpusk.god, otpusk.mes





-- Вариант "сложения" года и месяца для получения числа вида ГГГГМММ

-- Не строка! Число!



SELECT spisok.id, spisok.fam, otpusk.id, otpusk.god, otpusk.mes  

FROM spisok

inner join otpusk on otpusk.id_fam=spisok.id  

where    (otpusk.god * 100 + otpusk.mes) between (@yearFrom *100 + @monthFrom) and (@yearTo *100 + @monthTo)

    and    not exists(select 'x' 

                from otpusk as otpusk2

                where otpusk2.id_fam=otpusk.id_fam

                    and (otpusk2.god * 100 + otpusk2.mes) between (@yearFrom *100 + @monthFrom) and (@yearTo *100 + @monthTo)

                    -- Поиск записи раньше

                    and (otpusk2.god * 100 + otpusk2.mes) < (otpusk.god * 100 + otpusk.mes)

                )



ORDER BY spisok.fam, otpusk.god, otpusk.mes



Исправлено 2 раз(а). Последнее : Владимир Максимов, 26.12.18 16:40
Ratings: 0 negative/0 positive
Re: SQL SELECT минимальных значений.
PaulWist

Сообщений: 14620
Дата регистрации: 01.04.2004
PaulWist
26.12.18 16:51:47
Владимир Максимов
Если года разные, то два минимума могут дать не корректный результат

В общем случае ДА!


------------------
Есть многое на свете, друг Горацио...
Что и не снилось нашим мудрецам.
(В.Шекспир Гамлет)
Ratings: 0 negative/0 positive
Re: SQL SELECT минимальных значений.
ВладимирС

Сообщений: 1693
Дата регистрации: 03.11.2005
ВладимирС
27.12.18 06:59:44
Не знаю, подойдет ли мой вариант: 

declare @otpusk TABLE (ID int, ID_Fam varchar(50), God int, Mes int)  ;

    

  insert into @otpusk   

  select  10069, 'Анисимов', 2018,  2  union all

  select  10409, 'Анисимов', 2018,  5  union all

  select  10632, 'Анисимов', 2018,  8  union all

  select  10895, 'Анисимов', 2018, 12  union all

  select  10215, 'Бабич' ,   2018,  4  union all

  select  10223, 'Бабич',    2018,  5  union all

  select  10255, 'Бабич',    2018,  9  union all

  select  10365, 'Бовкун',   2018,  3  union all

  select  10634, 'Бовкун',   2018,  8  union all

  

  select      5, 'Анисимов', 2017,  3  union all

  select      6, 'Анисимов', 2017,  6  union all

  select     50, 'Бабич' ,   2016,  4  union all

  select     51, 'Бабич' ,   2016, 10

  ;

    

  select ID_Fam, CAST(SUBSTRING(Period,1,4) as int) as God, CAST(SUBSTRING(Period,5,2) as int) as Mes

  from

  (

      select ID_Fam, min(Period) as Period

      from

      ( 

        select ID, ID_Fam, God, Mes, CAST(God as varchar(4)) + REPLACE(STR(Mes,2),' ','0') as Period

        from @otpusk 

        where God = 2018   -- and Mes BETWEEN 2 and 12

      ) as otpusk

      group by ID_Fam

  ) r

  ;

Результат: 
ID_Fam     God   Mes
Анисимов   2018  2
Бабич      2018  4
Бовкун     2018  3
Ratings: 0 negative/0 positive
Re: SQL SELECT минимальных значений.
FoxProg
Автор

Сообщений: 150
Дата регистрации: 27.11.2012
FoxProg
27.12.18 10:56:24
Спасибо за ответы, НО!
To PaulWist и ВладимирС - у меня две таблицы! 

DECLARE @otpusk TABLE (ID int, ID_Fam int, God int, Mes int)

INSERT INTO @otpusk

        SELECT 10069, 360, 2018, 2

  UNION SELECT 10409, 360, 2018, 5

  UNION SELECT 10632, 360, 2018, 8

  UNION SELECT 10895, 360, 2018, 12

  UNION SELECT 10215, 400, 2018, 4

  UNION SELECT 10223, 400, 2018, 5

  UNION SELECT 10255, 400, 2018, 9

  UNION SELECT 10365, 352, 2018, 3

  UNION SELECT 10634, 352, 2018, 8



DECLARE @spisok TABLE (ID int, Fam varchar(50))

INSERT INTO @spisok

        SELECT 360, 'Анисимов'

  UNION SELECT 400, 'Бабич'

  UNION SELECT 352, 'Бовкун'

To Владимир Максимов
Минимальную и максимальную дату я буду передавать, как параметры, поэтому сразу в фоксе могу привести их к нужному виду.
Преобразовывать в строку или в число в приципе одно и то же, но Ваш метод лучше - во-первых, короче, а во-вторых, SQL при сравнении чисел работает гораздо быстрее, чем строк (я так думаю)
Сами выборки уж больно сложные. Должно быть все гораздо проще. 

DECLARE @nDate1 Int

DECLARE @nDate2 Int

SET @nDate1 = 201802

SET @nDate2 = 201812
Кстати 
DECLARE @nDate1 Int = 201802,
        @nDate2 Int = 201812
почему-то выдает ошибку Cannot assign a default value to a local variable. У меня MSSQL 2005
Вот такой SELECT 
SELECT id_fam, min(god*100+mes) AS min_date

 FROM @otpusk

 WHERE god*100+mes BETWEEN @nDate1 AND @nDate2

 GROUP BY id_fam

 ORDER BY id_fam
Срабатывает хорошо. Выдает 
id_fam  min_date
352     201803
360     201802
400     201804
Теперь бы вставить туда фамилии...
Вот такой селект выдает мне все записи - фамилии со всеми годами и месяцами 
SELECT otpusk.id, fam, god, mes

 FROM otpusk, spisok

 WHERE otpusk.id_fam=spisok.id AND god*100+mes BETWEEN @nDate1 AND @nDate2

ORDER BY fam, god, mes
Если бы объединить первый селект со вторым... Вот такое объединение 
SELECT fam, god, mes

 FROM otpusk, spisok

 WHERE otpusk.id_fam=spisok.id

 AND (id_fam, god*100+mes) IN

  (SELECT id_fam, MIN(god*100+mes)

  FROM otpusk otp

  WHERE god*100+mes BETWEEN @nDate1 AND @nDate2

  GROUP BY otp.id_fam)

ORDER BY fam
выдает ошибку Incorrect syntax near ',' на строке AND (id_fam, god*100+mes) IN
Хотя на сайте stackoverflow.com такая конструкция приведена: 
select *

  from TABLE_NAME

 where (Department, 

        Level) in (select Department,

                          min(Level)

                     from TABLE_NAME

                 group by Department)
Ratings: 0 negative/0 positive
Re: SQL SELECT минимальных значений.
FoxProg
Автор

Сообщений: 150
Дата регистрации: 27.11.2012
FoxProg
27.12.18 11:04:49
Забыл еще добавить...
Вот такой селект 
SELECT fam, god, mes

 FROM @otpusk, @spisok

 WHERE @otpusk.id_fam=@spisok.id AND god*100+mes BETWEEN @nDate1 AND @nDate2

ORDER BY fam
выдает ошибку Must declare the scalar variable "@otpusk" на строке 
WHERE @otpusk.id_fam=@spisok.id AND god*100+mes BETWEEN @nDate1 AND @nDate2
т.е. он не понимает @otpusk.id_fam. Хм... А как же тогда работать с полями временных таблиц?



Исправлено 1 раз(а). Последнее : FoxProg, 27.12.18 11:05
Ratings: 0 negative/0 positive
Re: SQL SELECT минимальных значений.
FoxProg
Автор

Сообщений: 150
Дата регистрации: 27.11.2012
FoxProg
27.12.18 11:40:52
Решил 
SELECT fam, god, mes

 FROM otpusk, spisok

 WHERE otpusk.id_fam=spisok.id

  AND god*100+mes = (

   SELECT MIN(god*100+mes)

    FROM otpusk otp

    WHERE god*100+mes BETWEEN @nDate1 AND @nDate2

     AND otp.id_fam = spisok.id

    GROUP BY otp.id_fam

   )

ORDER BY fam
Не знаю, можно ли сделать проще?
Ratings: 0 negative/0 positive
Re: SQL SELECT минимальных значений.
ssa

Сообщений: 13008
Откуда: Москва
Дата регистрации: 23.03.2005
ssa
27.12.18 14:01:47
FoxProg
Кстати 
DECLARE @nDate1 Int = 201802,
        @nDate2 Int = 201812
почему-то выдает ошибку Cannot assign a default value to a local variable. У меня MSSQL 2005
Разумеется. Испокон веков даты задавались как строки в апострофах (одинарных кавычках).
DECLARE @nDate1 Int = '20180201',
@nDate2 Int = '20181201'


------------------
Лень - это неосознанная мудрость.
Ratings: 0 negative/0 positive
Re: SQL SELECT минимальных значений.
FoxProg
Автор

Сообщений: 150
Дата регистрации: 27.11.2012
FoxProg
27.12.18 14:22:16
ssa
Разумеется. Испокон веков даты задавались как строки в апострофах (одинарных кавычках).
DECLARE @nDate1 Int = '20180201',
@nDate2 Int = '20181201'
У меня не дата, у меня Int
И почему тогда 
DECLARE @nDate1 Int  

DECLARE @nDate2 Int  

SET @nDate1 = 201802  

SET @nDate2 = 201812
проходит нормально? 
DECLARE @nDate1 Int = '20180201',

        @nDate2 Int = '20181201'
выдает ошибку
Cannot assign a default value to a local variable
"Невозможно присвоить значение по умолчанию локальной переменной"
Он еще на типы пока не смотрит, говорит, что это действие Нельзя.

А вот эти примеры работают абсолютно одинаково 
DECLARE @nDate1 Int,

        @nDate2 Int

SET @nDate1='20180201'

SET @nDate2='20181201'

SELECT @nDate1+@nDate2
и 
DECLARE @nDate1 Int,

        @nDate2 Int

SET @nDate1=20180201

SET @nDate2=20181201

SELECT @nDate1+@nDate2
и дают один и тот же результат 40361402
Ratings: 0 negative/0 positive
Re: SQL SELECT минимальных значений.
pasha_usue

Сообщений: 3650
Откуда: Е-бург
Дата регистрации: 06.10.2006
pasha_usue
27.12.18 15:57:47
FoxProg
выдает ошибку
Cannot assign a default value to a local variable
Нет в 2005 такого синтаксиса ещё. Он в 2008 появляется.
Ratings: 0 negative/0 positive
Re: SQL SELECT минимальных значений.
Владимир Максимов

Сообщений: 14100
Откуда: Москва
Дата регистрации: 02.09.2000
Владимир Максимов
27.12.18 19:35:56
FoxProg
Забыл еще добавить...
Вот такой селект 
SELECT fam, god, mes

 FROM @otpusk, @spisok

 WHERE @otpusk.id_fam=@spisok.id AND god*100+mes BETWEEN @nDate1 AND @nDate2

ORDER BY fam
выдает ошибку Must declare the scalar variable "@otpusk" на строке 
WHERE @otpusk.id_fam=@spisok.id AND god*100+mes BETWEEN @nDate1 AND @nDate2
т.е. он не понимает @otpusk.id_fam. Хм... А как же тогда работать с полями временных таблиц?

Через алиасы (псевдонимы) попробуйте 



SELECT otpusk.fam, otpusk.god, otpusk.mes

FROM @otpusk as otpusk

inner join @spisok as spisok on otpusk.id_fam = spisok.id 

WHERE otpusk.god*100+otpusk.mes BETWEEN @nDate1 AND @nDate2

ORDER BY otpusk.fam


FoxProg
Сами выборки уж больно сложные. Должно быть все гораздо проще.

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




SELECT 

    otpusk.fam, 

    min(otpusk.god*100+otpusk.mes)/100 as god,

    min(otpusk.god*100+otpusk.mes) % 100 as mes

FROM @otpusk as otpusk

INNER JOIN @spisok as spisok on otpusk.id_fam = spisok.id 

WHERE otpusk.god*100+otpusk.mes BETWEEN @nDate1 AND @nDate2

GROUP BY otpusk.fam

ORDER BY 1


A % B - это целочисленный остаток от деления A/B, что здесь и нужно

Если делятся два целых числа, то результат будет целое число. В этом случае остаток будет отброшен. Округления не будет.
Ratings: 0 negative/0 positive
Re: SQL SELECT минимальных значений.
FoxProg
Автор

Сообщений: 150
Дата регистрации: 27.11.2012
FoxProg
28.12.18 09:27:32
Владимир Максимов
Через алиасы (псевдонимы) попробуйте

Точно! Спасибо!

Владимир Максимов


SELECT 

    otpusk.fam, 

    min(otpusk.god*100+otpusk.mes)/100 as god,

    min(otpusk.god*100+otpusk.mes) % 100 as mes

FROM @otpusk as otpusk

INNER JOIN @spisok as spisok on otpusk.id_fam = spisok.id 

WHERE otpusk.god*100+otpusk.mes BETWEEN @nDate1 AND @nDate2

GROUP BY otpusk.fam

ORDER BY 1
Ух ты! Оказывается можно еще проще! Спасибо!
Ratings: 0 negative/0 positive


:: Список Форумов•Список тем•Поиск•Войти
Извините, только зарегистрированные пользователи могут оставлять сообщения в этом форуме.

On-line: 5 (Гостей: 5)
© 2000-2024 Fox Club 
Яндекс.Метрика