Форум программистов, компьютерный форум, киберфорум
Наши страницы
Microsoft SQL Server
Войти
Регистрация
Восстановить пароль
 
Рейтинг 4.60/5: Рейтинг темы: голосов - 5, средняя оценка - 4.60
alexandrstegnin
4 / 4 / 0
Регистрация: 09.01.2014
Сообщений: 130
#1

Выборка из 1 таблицы суммы значений, удовлетворяющие условиям

16.02.2015, 20:35. Просмотров 973. Ответов 16
Метки нет (Все метки)

Здравствуйте, уважаемые форумчане.
Весь день ломал голову, но так и не победил.
Суть вопроса такова:
возьмем 4 поля таблицы - 0. Название орг-ии 1. ИНН 2. Сумма 3. Счет
0. Ааа 1. 111 2. 100 3. 111111
0. Ббб 1. 222 2. 200 3. 222222
0. Ввв 1. 333 2. 300 3. 333333
0. Ббб 1. 222 2. 200 3. 111111
0. Ввв 1. 333 2. 300 3. 111111
В результате выборки надо получить:
0. Ааа 1. 111 зачислил на 0 счетов
0. Ббб 1. 222 зачислил на 1 счет
0. Ввв 1. 333 зачислил на 2 счета
Логика такая:
Нужно посчитать кол-во счетов на которые перечислила конкретная организация (ИНН), с условием, что если на один и тот же счет зачисляли 2 (или более) организации, то считать это кол-во в организацию с наибольшим зачислением.
До 21:00 бился на работе с этим вопросом, но так и не осилил. Кол-во счетов не трудно посчитать было, а вот наложить туда еще это условие - увы не смог.
Надеюсь понятно изъяснился. Если будут вопросы - готов ответить.
Очень надеюсь на вашу помощь.
Заранее спасибо.
С уважением, Александр.
0
Надоела реклама? Зарегистрируйтесь и она исчезнет полностью.
Similar
Эксперт
41792 / 34177 / 6122
Регистрация: 12.04.2006
Сообщений: 57,940
16.02.2015, 20:35
Ответы с готовыми решениями:

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

Выборка из таблицы только первых значений для конкретного поля
добрый день! есть таблица с полями: 1.code 2.cp 3.city 4.place значения...

Выборка по условиям
Здравствуйте! Есть таблица, по ней нужно сделать выборку. строки: kurs, god,...

Выборка финальных значений в промежутке и нахождение несуществующих значений
всем привет! прошу помощи начинающему, есть 3 таблицы: две из них - с данными...

Внести в поле таблицы сумму значений из другой таблицы по условию
Есть две таблицы данных People (плательщики) и Pays (платежи) Необходимо при...

16
Metall_Version
2118 / 1255 / 516
Регистрация: 04.03.2014
Сообщений: 4,094
Завершенные тесты: 2
16.02.2015, 22:49 #2
alexandrstegnin, вообще схема таблицы странная, должна быть :id, idфирмы, счет, сумма.
ну да ладно

T-SQL
1
2
3
4
5
6
7
8
SELECT o.name, o.inn, COUNT(x.account)
FROM orgTest o LEFT JOIN 
    (SELECT account, MAX([sum]) AS maxsum, 
        (SELECT TOP(1) inn FROM orgTest WHERE o.account= account AND [sum] = MAX(o.[sum])) AS maxinn
    FROM orgTest AS o 
    GROUP BY account) x 
ON x.maxinn =o.inn AND x.account = o.account
GROUP BY o.inn, o.name
Добавлено через 1 минуту
ну в таком случае если у двух фирм будет одна и та-же сумма на один и тот же счет. запрос выберет любую из этих фирм, вам нужно это обдумать
1
invm
1858 / 1261 / 369
Регистрация: 02.06.2013
Сообщений: 3,195
16.02.2015, 23:27 #3
T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
with a as
(
 select
  t.Название, t.ИНН,
  sum(t.Сумма) over (partition by t.Счет) as Сумма,
  row_number() over (partition by t.Счет order by t.Сумма desc) as rn
 from
  Таблица t
)
select
 a.Название, a.ИНН, a.Сумма
from
 a
where
 a.rn = 1;
1
alexandrstegnin
4 / 4 / 0
Регистрация: 09.01.2014
Сообщений: 130
17.02.2015, 06:43  [ТС] #4
To Metall_Version, полностью солидарен с Вами, но приходится работать с тем, что дают... Не моя таблица... Запрос отработал, но почему-то не верно считает... Не затруднит Вас прокомментировать, что происходит в этом случае (Ваш код). В частности то, что мы присвоили maxsum, но нигде не использовали и присвоили "о" два раза таблицы.
Заранее спасибо.
To invm, я думал, что некоторые навыки t-sql у меня всё же есть, оказывается далеко не базовые, а еще меньше. Ваш код у меня спотыкается на with, пишет "Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon". К сожалению навыков, чтобы самому разобраться в этом у меня не хватает. Если подскажете, что не так, буду признателен. Спасибо.
С уважением, Александр.
0
pincet
1319 / 906 / 123
Регистрация: 23.07.2010
Сообщений: 4,917
17.02.2015, 08:39 #5
Цитата Сообщение от alexandrstegnin Посмотреть сообщение
If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon
тебе ж явно сервер сказал - кляуза with должна начинаться с ";" если она не первая в пакете
1
alexandrstegnin
4 / 4 / 0
Регистрация: 09.01.2014
Сообщений: 130
17.02.2015, 09:21  [ТС] #6
Цитата Сообщение от pincet Посмотреть сообщение
тебе ж явно сервер сказал - кляуза with должна начинаться с ";" если она не первая в пакете
Спасибо, pincet, за проявленный интерес к моей теме. Действительно так и есть. В таком случае вопрос:
По запросу от invm, он выдал мне список, в котором орг-ия, ИНН и максимальная сумма зачисления, но мне нужно немного другое, а именно по каждому ИНН количество перечислений с максимальной суммой по счету.
Если запустить такой запрос:
T-SQL
1
2
Select T1.schet, max(t1.summa) from test t1
Group by t1.schet
Он построит выборку в которой есть счет и максимальная сумма зачисления. Вопрос как теперь соединить это с ИНН?
Т.е. получить именно count...
Спасибо, что не остаетесь безучастными.
С уважением, Александр.
0
Metall_Version
2118 / 1255 / 516
Регистрация: 04.03.2014
Сообщений: 4,094
Завершенные тесты: 2
17.02.2015, 11:15 #7
Цитата Сообщение от alexandrstegnin Посмотреть сообщение
Он построит выборку в которой есть счет и максимальная сумма зачисления. Вопрос как теперь соединить это с ИНН?
вот это я и сделал в запросе

C#
1
2
3
4
5
6
7
8
9
10
SELECT o.name, o.inn, COUNT(x.account)
FROM orgTest o LEFT JOIN 
 
    (SELECT account, 
        (SELECT TOP(1) inn FROM orgTest WHERE o.account= account AND [sum] = MAX(o.[sum])) AS maxinn
    FROM orgTest AS o 
    GROUP BY account) x  -- группируем записи по счету, выбираем сам счет(account), и ИНН фирмы, у которой зачисление на этот счет самое большое (в случае если таких фирм несколько выберется случайная)
 
ON x.maxinn =o.inn AND x.account = o.account
GROUP BY o.inn, o.name
полученный запрос х мы объединяем с таблицей фирм, и группируем фирмы по инн и имени. и для каждой фирмы находим количество сделок из запроса х

Добавлено через 25 секунд
Цитата Сообщение от alexandrstegnin Посмотреть сообщение
но почему-то не верно считает
на каких данных ?

Добавлено через 1 минуту
Цитата Сообщение от alexandrstegnin Посмотреть сообщение
В частности то, что мы присвоили maxsum, но нигде не использовали и присвоили "о" два раза таблицы.
maxsum было излишне, а насчет "о" просто так по привычке первую букву таблицы использую, области ихние не пересекаются
1
invm
1858 / 1261 / 369
Регистрация: 02.06.2013
Сообщений: 3,195
17.02.2015, 11:21 #8
Цитата Сообщение от alexandrstegnin Посмотреть сообщение
Логика такая:
Нужно посчитать кол-во счетов на которые перечислила конкретная организация (ИНН), с условием, что если на один и тот же счет зачисляли 2 (или более) организации, то считать это кол-во в организацию с наибольшим зачислением.
T-SQL
1
2
3
4
5
6
7
select
 a.Название, a.ИНН, count(distinct t.Счет)
from
 Таблица t cross apply
 (select top (1) Название, ИНН from Таблица where Счет = t.Счет order by Сумма desc) a
group by
 a.Название, a.ИНН;
1
alexandrstegnin
4 / 4 / 0
Регистрация: 09.01.2014
Сообщений: 130
17.02.2015, 11:24  [ТС] #9
Metall_Version, я беру уже свои реальные данные, вернее те, которые мне предоставили. Сейчас попробую разобраться, может и в данных какой косяк закрался. Попробую сделать на тестовой и тогда будет видно думаю...
PS. Почему "select top(1) inn"?
Спасибо.
С уважением, Александр.
0
pincet
1319 / 906 / 123
Регистрация: 23.07.2010
Сообщений: 4,917
17.02.2015, 11:32 #10
вообще для top(1) нужно явно указывать порядок сортировки. Иначе какой смысл?
1
Metall_Version
2118 / 1255 / 516
Регистрация: 04.03.2014
Сообщений: 4,094
Завершенные тесты: 2
17.02.2015, 11:44 #11
Цитата Сообщение от alexandrstegnin Посмотреть сообщение
PS. Почему "select top(1) inn"?
потому что что нужно выбрать одну фирму. из всех которые зачислили максимум средств на один счет
1
alexandrstegnin
4 / 4 / 0
Регистрация: 09.01.2014
Сообщений: 130
17.02.2015, 11:55  [ТС] #12
invm, к сожалению на 80000 записей Ваш запрос выполнялся 22 минуты, но увы, так и не увенчался успехом.
С удовольствием попробую еще идеи, а пока попробую просмотреть записи на "правильность"...
С уважением, Александр.
0
Metall_Version
2118 / 1255 / 516
Регистрация: 04.03.2014
Сообщений: 4,094
Завершенные тесты: 2
17.02.2015, 12:07 #13
alexandrstegnin, вы на главное не ответили, что если для одного счета несколько фирм имеют максимальные зачисления. какую тогда выбирать? я думаю от сюда и выползает то что вы называете "неправильность".

Добавлено через 49 секунд
на счет производительности, с такой "транной" схемой Бд это ожидаемо, плюс не помешает сделать верную индексацию.
1
invm
1858 / 1261 / 369
Регистрация: 02.06.2013
Сообщений: 3,195
17.02.2015, 12:17 #14
Цитата Сообщение от alexandrstegnin Посмотреть сообщение
invm, к сожалению на 80000 записей Ваш запрос выполнялся 22 минуты, но увы, так и не увенчался успехом.
Сделайте индекс по (Счет, Сумма desc)
0
alexandrstegnin
4 / 4 / 0
Регистрация: 09.01.2014
Сообщений: 130
17.02.2015, 12:22  [ТС] #15
Цитата Сообщение от Metall_Version Посмотреть сообщение
alexandrstegnin, вы на главное не ответили, что если для одного счета несколько фирм имеют максимальные зачисления. какую тогда выбирать? я думаю от сюда и выползает то что вы называете "неправильность".

Добавлено через 49 секунд
на счет производительности, с такой "транной" схемой Бд это ожидаемо, плюс не помешает сделать верную индексацию.
Metall_Version, к вопросу о 2 фирмах с одинаковым зачислением - брать фирму, которая в списке попалась первой.
Погрешность в несколько счетов для данной аналитики не критична.
Еще раз соглашусь с Вами, странная структура, но такую предоставили, а переделывать за кого-то это не благодарный труд, приходится работать с данными в том виде, в котором они предоставлены.
Про "верную индексацию" немного развернутый можете сказать?
Спасибо.
С уважением, Александр.

Добавлено через 2 минуты
Цитата Сообщение от invm Посмотреть сообщение
Сделайте индекс по (Счет, Сумма desc)
invm, буду благодарен, если немного конкретней скажите что и где сделать. Моих познаний хватает только на простые запросы с выборкой и, иногда, с join...
Спасибо.
С уважением, Александр.
0
Metall_Version
2118 / 1255 / 516
Регистрация: 04.03.2014
Сообщений: 4,094
Завершенные тесты: 2
17.02.2015, 12:27 #16
Цитата Сообщение от alexandrstegnin Посмотреть сообщение
Про "верную индексацию" немного развернутый можете сказать?
это уже новый вопрос, создайте новую тему с таким вопросом
1
alexandrstegnin
4 / 4 / 0
Регистрация: 09.01.2014
Сообщений: 130
17.02.2015, 12:54  [ТС] #17
Цитата Сообщение от Metall_Version Посмотреть сообщение
это уже новый вопрос, создайте новую тему с таким вопросом
Metall_Version, спасибо. Буду пока разбираться на этих примерах.
С уважением, Александр.
0
17.02.2015, 12:54
MoreAnswers
Эксперт
37091 / 29110 / 5898
Регистрация: 17.06.2006
Сообщений: 43,301
17.02.2015, 12:54

Замена значений в поле одной таблицы на значение из другой таблицы
Добрый всем день,прошу помощи,так как не силён в программировании, суть вот в...

Выборка из таблицы
Здравствуйте. У меня есть таблица A. Допустим в ней записи за весь месяц, за...

Выборка из таблицы
Проблемы возникли со следующей задачей Получить все пары фамилий...


Искать еще темы с ответами

Или воспользуйтесь поиском по форуму:
17
Ответ Создать тему
Опции темы

КиберФорум - форум программистов, компьютерный форум, программирование
Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2018, vBulletin Solutions, Inc.
Рейтинг@Mail.ru