Ответы пользователя Руслан . по тегу «SQL»

Сортировка по связующей таблице?

Руслан . @LaRN

Senior Developer

А почему через join не вышло?
Можно так попробовать:

SELECT companies.name, max(polls.created_at)
  FROM polls
  JOIN teams
    ON teams.id = polls.team_id
  JOIN companies
    ON companies.id = teams.company_id
 GROUP BY companies.name
 ORDER BY 2

Ответ написан более трёх лет назад

3 комментария

Как совместить DELETE и LEFT JOIN?

Руслан . @LaRN

Senior Developer

Т.е. задача удалить всех юзеров, которые не студенты?
Если да, то можно так попробовать:

DELETE user
  FROM user u
 WHERE NOT EXISTS(SELECT 1
                    FROM students s
                   where s.student_id = u.id)

Ответ написан более трёх лет назад

Комментировать

Как сделать запрос на sql?

Руслан . @LaRN

Senior Developer

Для определения позиции строки в датасете можно использовать оконные функции.
В MS SQL это будет например так:

SELECT Row_Number() over (order by score), user_id, score
    FROM vk 
   WHERE score <= ?

Ответ написан более трёх лет назад

Комментировать

Вопрос по джоинам?

Руслан . @LaRN

Senior Developer

Можно вот так попробовать:

select pn.user_id, 
       MAX(pn.Phone), 
       MAX(ml.Email)
  from contact_info pn   
  JOIN users
    ON users.user_id = pn.user_id 
  LEFT
  JOIN contact_info ml
    ON ml.user_id    = pn.user_id 
   AND ml.InfoType      = 'Phone'   
 WHERE pn.user_id.Phone = '1234567'
   AND pn.InfoType      = 'Email'
 GROUP BY pn.user_id

Но структура хранения странная.
У Вас же есть поле InfoType, зачем еще для каждого вида InfoType свою колонку заводить?

Ответ написан более трёх лет назад

1 комментарий

Как update table через массив?

Руслан . @LaRN

Senior Developer

Чтобы проапдейти таблицу, нужно каждую строку изменяемой таблицы как-то связать с массивом (чтобы связь была один к одному), т.е. в массиве условно д.б. ключевое поле (а массив д.б. двухмерным), которое вы укажите в запросе на апдейт.

Подробнее тут:
https://postgrespro.ru/docs/postgrespro/9.5/querie...

Ответ написан более трёх лет назад

Комментировать

Как построить sql запрос к 3 таблицам?

Руслан . @LaRN

Senior Developer

Тут ошибка в использовании группировки
В группировку (group by) нужно включать все столбцы из select к которым не применены агрегирующие функции.
Т.е. вместо group by c.id нужно group by c.first_name, c.last_name;

Ответ написан более трёх лет назад

Комментировать

Алиасы строки в sql?

Руслан . @LaRN

Senior Developer

вот так например:
select alb.year as year1, cal.year as year2

Ответ написан более трёх лет назад

1 комментарий

Как сделать sql выборку по возрасту имея только дату рождения?

Руслан . @LaRN

Senior Developer

Можно так попробовать:
select datediff(yy, DOB, NOW())

Ответ написан более трёх лет назад

1 комментарий

Почему запрос не работает?

Руслан . @LaRN

Senior Developer

Тут условие странное:

di.product_id IS NOT NULL
AND p.product_id IS NULL

При этом:
p.product_id=di.product_id

Тут одно поле всегда д.б. NULL, а второе всегда не NULL и условие p.product_id=di.product_id никогда не выполнится.

Ответ написан более трёх лет назад

3 комментария

Как в SQL вывести дешевую среди всех по условию?

Руслан . @LaRN

Senior Developer

Вот так можно попробовать, чтобы по всем брендам сразу:

SELECT p2.make_name, min(p1.id), min(p2.cost) 
  FROM price_items AS p1
 INNER JOIN price_items AS p2
         ON p1.make_name = p2.make_name 
 GROUP BY p2.make_name

Ответ написан более трёх лет назад

Комментировать

Как выбрать и приоритизировать координаты на карте по другим?

Руслан . @LaRN

Senior Developer

При такого вида запросах точного попадания в координаты организации не будет и нужно определиться с размером области вокруг координат пользователя и ее формой (квадрат, прямоугольник, круг, что-то еще).
Размер области ограничит список отбираемых организаций.
Задать размер можно например так, пусть нужно показать все вокруг пользователя в радиусе 2 км.
И пусть область для простоты - это квадрат.
Из вики можно найти, что средняя длинна одного градуса широты/долготы примерно 111 км.
Тогда вариация по широте/долготе от текущей позиции пользователя будет плюс/минус 2/111 градуса = 0,018 градуса.

А дальше можно вот таким запросом вывести список подходящих организаций:

SELECT us.id,
       lc.name,
       lc.address 
  FROM tb_user us
  JOIN tb_location lc
    ON lc.lat BETWEEN us.lat - 0.018 AND us.lat + 0.018
   AND lc.lon BETWEEN us.lon - 0.018 AND us.lon + 0.018

Чтобы это нормально работало нужны индексы по полям lat, lon таблицы tb_location.
Также лучше ограничить точность координат lat, lon например можно указать место организации с точностью до 10 метров, тогда координаты можно округлить до 0,01/111 = 9*10^-6, т.е. до 5 знаков после запятой.

Можно еще для операции поиска координаты привести к целым числам, т.е. хранить для поиска lat/lon умноженными на 10^5 и без дробной части. При такой схеме нужно 3+5 = 8 значащих цифр и тип int подойдет для хранения. Этот вариант позволить уменьшит размер индекса по полям lat/lon, а это должно ускорить поиск.
При таком вариант в запросе вместо
us.lat - 0.018 AND us.lat + 0.018
нужно указать
us.lat - 1800 AND us.lat + 1800

При использовании в поиске условия вида
us.lat - 1800 AND us.lat + 1800
есть нюанс, если искать в районе 0/360 градуса нужно делить условие на две части, вот пример когда
us.lat - 1800 получается меньше нуля.

BETWEEN 36000000 + (us.lat - 1800) AND 36000000
OR
BETWEEN 0 AND us.lat + 1800

Если us.lat + 1800 получается больше 36000000, то аналогично две части:
BETWEEN us.lat - 1800 AND 36000000
OR
BETWEEN 0 AND (us.lat + 1800)-36000000

Ответ написан более трёх лет назад

Комментировать

Как вывести строки, определенные поля у которых дают в сумме 100?

Руслан . @LaRN

Senior Developer

Можно вот так попробовать:

WITH ref AS
(SELECT `referral_id` 
   FROM `table`
  GROUP BY `referral_id`
 HAVING sum(`amount`) >= 100)
SELECT t1.`referral_id`, t1.`id`, t1.`amount`
  FROM ref as t
 INNER JOIN `table` t1
         ON t1.`referral_id` = t.`referral_id`
ORDER BY t1.`referral_id`, t1.`id`

Ответ написан более трёх лет назад

Комментировать

Можно ли как-то оптимизировать данную хранимую процедуру?

Руслан . @LaRN

Senior Developer

Судя по запросу:
SELECT @Code=КодТовара, @Итог=SUM(Количество)
FROM Заказ
WHERE ДатаЗаказа BETWEEN GetDate()-@Интервал AND GetDate()
GROUP BY КодТовара
ORDER BY SUM(Количество)

У вас может вернуться много строк, но так вы это все в переменные начитываете, то все равно останется только одно последнее по порядку сортировки значение, т.е. товар с максимальным или минимальным значением SUM(Количество), т.е. можно попробовать использовать агрегирующую функцию на SUM(Количество).

Также в запросе используется GetDate(), и если предположить, что в таблице нет записей с датой превосходящей GetDate(), то можно делать проверку на открытый диапазон, т.е.
ДатаЗаказа >= GetDate()-@Интервал.

Итого, можно так попробовать:

WITH Q AS  
(SELECT КодТовара, SUM(Количество) as Сумм
   FROM Заказ
  WHERE ДатаЗаказа >= GetDate()-@Интервал
  GROUP BY КодТовара)
SELECT @Code = Q.КодТовара, 
       @Итог = Q.Сумм
  FROM Q
 WHERE Q.Сумм = (SELECT CASE WHEN @ТипРезультата = 1 
                              THEN MAX(Q.Сумм) 
                              ELSE MIN(Q.Сумм) 
                         END
                   FROM Q)

Ответ написан более трёх лет назад

Комментировать

Возможно в запросе организовать подбор, чтобы суммарно не превышало заданное число?

Руслан . @LaRN

Senior Developer

Можно вот так попробовать:

select d1.[time], d1.contr, d1.val
  from debt d1
  join debt d2
    on d2.[time] <= d1.[time]
 group by d1.contr, d1.val, d1.[time]
having sum(d2.val) <= 25 -- сумма которую нужно выплатить
 order by d1.[time]

Тут debt - таблица с задолженностями.
Есть пара моментов при такой реализации:
1. Нужно быть уверенным, что все задолженности будут попадать в систему с разными датами-временем,
если например дата и время совпадут у двух, то как тут строить ФИФО вопрос.
2. Желательно как-то помечать задолженности которые оплачены, чтобы повторно их не отбирать.
3. Поле time лучше обозвать иначе, сейчас оно совпадает с системным типом данных для хранения времени.

Ответ написан более трёх лет назад

Комментировать

Правильно написать запрос SELECT sum(таблица1.sum - таблица2.sum )... GROUP BY id для вывода остатков по номенклатуре?

Руслан . @LaRN

Senior Developer

Можно вот так попробовать.

WITH amount AS
 (
   SELECT buy.nomenclature_id, sum(buy.summa) as summa
    FROM buy
   GROUP BY buy.nomenclature_id
   UNION ALL
  SELECT sale.nomenclature_id, -1*sum(sale.summa) as summa
    FROM sale
   GROUP BY sale.nomenclature_id
  )
 SELECT nomenclature_id, sum(summa) as summa
   FROM amount 
  GROUP BY nomenclature_id

Ответ написан более трёх лет назад

1 комментарий

Есть ли смысл использовать checksum или hashbyte в JOIN'ах?

Руслан . @LaRN

Senior Developer

Можно в таблице tbl создать поле в котором хранить checksum(tbl.date,tbl.text,tbl.size) и сделать по этому полю индексацию.

Если checksum дает много коллизий можно попробовать другой хэш, в MSSQL например их вот сколько вариантов:
MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512

https://docs.microsoft.com/ru-ru/sql/t-sql/functio...

Но хэш хорош только когда нужно ответить на вопрос = или <>, а за другими условиями все равно придется в поля таблицы идти.

У меня есть кейс, где нужно искать совпадения по 17 аналитикам и там такой способ ускорил поиск в 10 - ки раз по сравнению с обычным поиском по куче полей, в вашем случае всего 3 поля и наверное join по полям и наличие селективного индекса хотя бы по одному из них уже достаточно.
По полю с varchar(1000) лучше не делать индекса, а по двум другим вполне можно.

Ответ написан более трёх лет назад

Комментировать

Двойной SELECT вместе с UNION и возвратом наибольшего значения, как?

Руслан . @LaRN

Senior Developer

Можно через временны запрос попробовать:

WITH rates as
(SELECT rate, type, route_id, weight, volume
   FROM test_rates rs
  WHERE rs.dt_del IS NULL
    AND (rs.volume >= 300 OR rs.weight >= 50000))
SELECT rate, type, route_id, weight, volume
  FROM rates
 where rate = (select max(rate) from rates)

Ответ написан более трёх лет назад

Комментировать

Почему появляется ошибка "column must appear in the GROUP BY clause or be used in an aggregate function"?

Руслан . @LaRN

Senior Developer

Поле по которому происходит группировка image_url, поле которое агрегируется votes.value, а поле views ни там, ни там.
Нужно добавить поле views в секцию group by:
group by image_url, views , например так:

select views, image_url, (select avg(votes.value))
  from images
 inner join votes 
     on votes.image_id = images.id
 group by views, image_url
 order by views asc

Либо в секцию order by поставить поле image_url.

Ответ написан более трёх лет назад

1 комментарий

Разница между значениями SQL таблицы?

Руслан . @LaRN

Senior Developer

Можно вот так попробовать:

select top 1 c1.code_number as c1, c2.code_number as c2, min(c2.code_number - c1.code_number) as delta
  from code as c1
  join code as c2
    on c2.code_number > c1.code_number
 group by c1.code_number, c2.code_number
 having min(c2.code_number - c1.code_number) > 0
 order by 3

Ответ написан более трёх лет назад

Комментировать

Почему OR проверяет оба условия при выполнении хотя бы одного?

Руслан . @LaRN

Senior Developer

Если английская версия есть всегда, а другие опционально, можно так попробовать:

SELECT ISNULL(i2.title, i1.title) as title, ISNULL(i2.descr, i1.descr) as descr, gg.* 
  FROM game_text_info as i1
 INNER JOIN games as gg
         ON gg.id = i1.game_id
  LEFT JOIN game_text_info as i2
         ON i2.game_id = i1.game_id 
        AND i2.game_text_info.local = :local
 WHERE i1.title LIKE ':search'
   and i1.game_text_info.local = "en"

Ответ написан более трёх лет назад

1 комментарий

Войдите на сайт