Форум программистов, компьютерный форум, киберфорум
Наши страницы

Microsoft SQL Server

Войти
Регистрация
Восстановить пароль
 
 
alexandrstegnin
4 / 4 / 0
Регистрация: 09.01.2014
Сообщений: 130
#1

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

16.02.2015, 20:35. Просмотров 839. Ответов 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 таблицы суммы значений, удовлетворяющие условиям (SQL Server):

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

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

Выборка по условиям - SQL Server
Здравствуйте! Есть таблица, по ней нужно сделать выборку. строки: kurs, god, kol(1), kol(0) Если (kurs = kurs) и (god != god) и...

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

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

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

16
Metall_Version
Модератор
2109 / 1246 / 414
Регистрация: 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
1626 / 1100 / 295
Регистрация: 02.06.2013
Сообщений: 2,742
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
1252 / 824 / 91
Регистрация: 23.07.2010
Сообщений: 4,257
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
Модератор
2109 / 1246 / 414
Регистрация: 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
1626 / 1100 / 295
Регистрация: 02.06.2013
Сообщений: 2,742
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
1252 / 824 / 91
Регистрация: 23.07.2010
Сообщений: 4,257
17.02.2015, 11:32 #10
вообще для top(1) нужно явно указывать порядок сортировки. Иначе какой смысл?
1
Metall_Version
Модератор
2109 / 1246 / 414
Регистрация: 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
Модератор
2109 / 1246 / 414
Регистрация: 04.03.2014
Сообщений: 4,094
Завершенные тесты: 2
17.02.2015, 12:07 #13
alexandrstegnin, вы на главное не ответили, что если для одного счета несколько фирм имеют максимальные зачисления. какую тогда выбирать? я думаю от сюда и выползает то что вы называете "неправильность".

Добавлено через 49 секунд
на счет производительности, с такой "транной" схемой Бд это ожидаемо, плюс не помешает сделать верную индексацию.
1
invm
1626 / 1100 / 295
Регистрация: 02.06.2013
Сообщений: 2,742
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
17.02.2015, 12:22
MoreAnswers
Эксперт
37091 / 29110 / 5898
Регистрация: 17.06.2006
Сообщений: 43,301
17.02.2015, 12:22
Привет! Вот еще темы с ответами:

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

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

Выборка из таблицы Заказы - SQL Server
Дана таблица Заказы(КодЗаказа, ДадаОформления, ДатаДоставки, Клиент, Сотрудник, СуммаЗаказа, СтранаДоставки, ГородДоставки, АдресДоставки)....

Выборка из таблицы и сортировка - SQL Server
Всем привет, с наступающими праздниками. Прошу помощи в таком вопросе. Есть таблица такого вида Таблица.jpg как сделать выборку...


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

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

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