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

Откуда:
Сообщений: 16
Таблица 1 (Было яблок)
ПродуктЦветКоличество
ЯблокиЗеленые4
ЯблокиКрасные7


Таблица 2 (Гости)
ПродуктГостьКоличество
ЯблокиСаша1
ЯблокиОлег2
ЯблокиМаша5
ЯблокиВалентин3


Очень хочется получить Результат в виде таблицы:

Таблица 3 (Кто каких яблок съел?)

ПродуктГостьЦветКоличество
ЯблокиСашаЗеленые1
ЯблокиОлегЗеленые1
ЯблокиОлегКрасные1
ЯблокиМашаЗеленые2
ЯблокиМашаКрасные3
ЯблокиВалентинКрасные3


Устроит любая комбинация Главное требование,чтобы результирующая таблица сходилась с первыми двумя

Возможно ли получить такое решение одним запросом?
Пожалуйста помогите решить!
Заранее благодарю за любые идеи.
18 апр 13, 12:50    [14197992]     Ответить | Цитировать Сообщить модератору
 Re: задачка про яблоки или слияние 2х таблиц  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Вы по-русски, по шагам, алгоритм распределения количеств написать можете?
18 апр 13, 12:53    [14198018]     Ответить | Цитировать Сообщить модератору
 Re: задачка про яблоки или слияние 2х таблиц  [new]
Glory
Member

Откуда:
Сообщений: 104760
v64
Пожалуйста помогите решить!

- Составьте все возможные комбинации из Таблица 2
- Сравните эти комбинации по сумме с Таблица 1 и выберите те, у которых сумма совпадает

Ваши данные очень упрощены.
В реальной задаче может не быть решение где "Количество" из обеих таблиц будет точно совпадать
18 апр 13, 12:57    [14198055]     Ответить | Цитировать Сообщить модератору
 Re: задачка про яблоки или слияние 2х таблиц  [new]
v64
Member

Откуда:
Сообщений: 16
Паганель, в том то и дело что алгоритм может быть любым а точнее его нужно придумать. можно предположить про существование каких то вероятностей но главное тут получить результирующую таблицу из которой я могу получить две первые, а содержимое ее может быть любым.
18 апр 13, 13:00    [14198072]     Ответить | Цитировать Сообщить модератору
 Re: задачка про яблоки или слияние 2х таблиц  [new]
v64
Member

Откуда:
Сообщений: 16
Glory, Согласен. но конкретно в этой задаче общее количество первых двух таблиц совпадает. просто так получилось что потеряна связь между ними(( которую и требуется восстановить каким то хитрым образом.
18 апр 13, 13:02    [14198101]     Ответить | Цитировать Сообщить модератору
 Re: задачка про яблоки или слияние 2х таблиц  [new]
Glory
Member

Откуда:
Сообщений: 104760
v64
в том то и дело что алгоритм может быть любым а точнее его нужно придумать

Какую реальную задачу вы решаете ?
Партиционный учет ? Заказы-оплаты ? Подбор наилучшего варианта ?
На все эти задачи алгоритмы уже давно придуманы
18 апр 13, 13:04    [14198113]     Ответить | Цитировать Сообщить модератору
 Re: задачка про яблоки или слияние 2х таблиц  [new]
Shakill
Member

Откуда: мск
Сообщений: 1880
v64
Паганель, в том то и дело что алгоритм может быть любым а точнее его нужно придумать. можно предположить про существование каких то вероятностей но главное тут получить результирующую таблицу из которой я могу получить две первые, а содержимое ее может быть любым.


можно так, например. если суммарное количество продукта в таблицах не сходится, то ответ будет для меньшего
+
create table prod(colour sysname, qty int);
create table guest(guestname sysname, qty int);

insert prod values 
	(N'зеленые', 4),
	(N'красные', 7);

insert guest values
	(N'Саша', 1),
	(N'Олег', 2),
	(N'Маша', 5),
	(N'Валентин', 3);

with nums as ( -- тут должна быть таблица целых чисел
	select distinct sv.number 
	from master.dbo.spt_values sv
	where sv.number >= 1 and sv.number <= 1000
),
prn as (
	select p.colour, row_number() over (order by p.colour, n.number)  rn
	from prod p
	join nums n on n.number between 1 and p.qty
),
gst as (
	select g.guestname, row_number() over (order by g.guestname, n.number)  rn
	from guest g
	join nums n on n.number between 1 and g.qty
)
select p.colour, g.guestname, count(*) qty
from prn p
inner join gst g on p.rn = g.rn
group by p.colour, g.guestname
18 апр 13, 13:22    [14198250]     Ответить | Цитировать Сообщить модератору
 Re: задачка про яблоки или слияние 2х таблиц  [new]
v64
Member

Откуда:
Сообщений: 16
Glory, партионный учет наверное подойдет но как его применить к конкретному случаю?
если говорить другими словами то есть регистр продаж в котором есть товар и серия, но нет продавца, и есть таблица продаж сотрудниками в которой есть информация о товаре и сотруднике но нету информации о товаре. Необходимо получить таблицу в которой были бы все три сущности и которая отвечала бы на вопрос какой товар какой серии был продан продавцом, понятно что однозначного ответа тут быть не может но устроит любой не противоречащий исходным данным.
18 апр 13, 13:23    [14198254]     Ответить | Цитировать Сообщить модератору
 Re: задачка про яблоки или слияние 2х таблиц  [new]
v64
Member

Откуда:
Сообщений: 16
Shakill, спасибо за идею. попробую применить ее в 1С-ном запросе.
18 апр 13, 13:27    [14198270]     Ответить | Цитировать Сообщить модератору
 Re: задачка про яблоки или слияние 2х таблиц  [new]
invm
Member

Откуда: Москва
Сообщений: 9439
if @@trancount > 0
 rollback;
 
begin tran;

declare @p table (p_Product varchar(30), p_Color varchar(30), p_Quantity int, p_ProductOrder int identity, primary key (p_Product, p_Color));
declare @g table (g_Product varchar(30), g_Guest varchar(30), g_Quantity int, g_GuestOrder int identity, primary key (g_Product, g_Guest));

insert into @p
 (p_Product, p_Color, p_Quantity)
values
 ('Яблоки', 'Зеленые', 4),
 ('Яблоки', 'Красные', 7);
 
insert into @g
 (g_Product, g_Guest, g_Quantity)
values
('Яблоки', 'Саша', 1),
('Яблоки', 'Олег', 2),
('Яблоки', 'Маша', 5),
('Яблоки', 'Валентин', 3);

select
 t1.p_Product, t1.p_Color, t1.p_Quantity,
 isnull(sum(t2.p_Quantity), 0) + 1 as p_QuantityStart, isnull(sum(t2.p_Quantity), 0) + t1.p_Quantity as p_QuantityEnd
into
 #p
from
 @p t1 left join 
 @p t2 on t2.p_Product = t1.p_Product and t2.p_ProductOrder < t1.p_ProductOrder
group by
 t1.p_Product, t1.p_Color, t1.p_Quantity, t1.p_ProductOrder;

select
 t1.g_Product, t1.g_Guest, t1.g_Quantity,
 isnull(sum(t2.g_Quantity), 0) + 1 as g_QuantityStart, isnull(sum(t2.g_Quantity), 0) + t1.g_Quantity as g_QuantityEnd
into
 #g
from
 @g t1 left join 
 @g t2 on t2.g_Product = t1.g_Product and t2.g_GuestOrder < t1.g_GuestOrder
group by
 t1.g_Product, t1.g_Guest, t1.g_Quantity, t1.g_GuestOrder;

select
 p.p_Product, p.p_Color, g.g_Guest,
 case
  when p.p_QuantityStart >= g.g_QuantityStart and p.p_QuantityEnd <= g.g_QuantityEnd then p.p_Quantity
  when g.g_QuantityStart >= p.p_QuantityStart and g.g_QuantityEnd <= p.p_QuantityEnd then g.g_Quantity
  when g.g_QuantityStart between p.p_QuantityStart and p.p_QuantityEnd then p.p_QuantityEnd - g.g_QuantityStart + 1
  when g.g_QuantityEnd between p.p_QuantityStart and p.p_QuantityEnd then g.g_QuantityEnd - p.p_QuantityStart + 1
 end
from
 #p p join
 #g g on g.g_Product = p.p_Product and p.p_QuantityStart <= g.g_QuantityEnd and p.p_QuantityEnd >= g.g_QuantityStart
order by
 p.p_Product, p.p_QuantityStart;

rollback;
18 апр 13, 13:33    [14198301]     Ответить | Цитировать Сообщить модератору
 Re: задачка про яблоки или слияние 2х таблиц  [new]
v64
Member

Откуда:
Сообщений: 16
Shakill, Еще раз хочу поблагодарить за помощь. к сожалению любимое 1С предприятие не располагает функцией row_number().
но алгоритм вполне устроил.

на всякий случай выложу поделку на 1с (вдруг кому понадобится):

+

ВЫБРАТЬ
"Яблоко" КАК Товар,
"Зеленый" КАК Свойство,
3 КАК Количество
ПОМЕСТИТЬ Фрукты

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
"Яблоко",
"Красный",
5

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
"Груша",
"Зеленый",
2
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
"Яблоко" КАК Товар,
"Маша" КАК Потребитель,
1 КАК Количество
ПОМЕСТИТЬ Потребители

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
"Яблоко",
"Наташа",
3

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
"Яблоко",
"Света",
4

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
"Груша",
"Света",
1

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
"Груша",
"Маша",
1
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
0 КАК Цифра
ПОМЕСТИТЬ Цифры

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
1

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
2

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
3

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
4

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
5

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
6

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
7

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
8

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
9
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Т1.Цифра + Т2.Цифра * 10 КАК Число
ПОМЕСТИТЬ Числа
ИЗ
Цифры КАК Т1,
Цифры КАК Т2
ГДЕ
Т1.Цифра + Т2.Цифра * 10 > 0
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
КОЛИЧЕСТВО(*) КАК Номер,
Т.Товар,
Т.Свойство,
Т.Количество
ПОМЕСТИТЬ НомераФрутовыхГрупп
ИЗ
Фрукты КАК Т
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Фрукты КАК Т1
ПО (Т.Товар + Т.Свойство >= Т1.Товар + Т1.Свойство)

СГРУППИРОВАТЬ ПО
Т.Товар,
Т.Свойство,
Т.Количество
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
КОЛИЧЕСТВО(*) КАК Номер,
Т1.Товар,
Т1.Потребитель,
Т1.Количество
ПОМЕСТИТЬ НомераПотребительскихГрупп
ИЗ
Потребители КАК Т1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Потребители КАК Т2
ПО (Т1.Товар + Т1.Потребитель >= Т2.Товар + Т2.Потребитель)

СГРУППИРОВАТЬ ПО
Т1.Товар,
Т1.Потребитель,
Т1.Количество
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Т1.Товар,
Т1.Свойство,
Т1.Количество,
Т1.Номер * 1000000000 + Т2.Число КАК Индекс
ПОМЕСТИТЬ ФруктыПоштучно
ИЗ
НомераФрутовыхГрупп КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ Числа КАК Т2
ПО Т1.Количество >= Т2.Число

СГРУППИРОВАТЬ ПО
Т1.Товар,
Т1.Свойство,
Т1.Количество,
Т2.Число,
Т1.Номер
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Т1.Товар,
Т1.Потребитель,
Т1.Количество,
Т1.Номер * 1000000000 + Т2.Число КАК Индекс
ПОМЕСТИТЬ ПотребителиПоштучно
ИЗ
НомераПотребительскихГрупп КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ Числа КАК Т2
ПО Т1.Количество >= Т2.Число
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
КОЛИЧЕСТВО(*) КАК Номер,
Т1.Товар,
Т1.Свойство,
1 КАК Количество
ПОМЕСТИТЬ ИндексСвойства
ИЗ
ФруктыПоштучно КАК Т1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ФруктыПоштучно КАК Т2
ПО Т1.Индекс >= Т2.Индекс
И Т1.Товар = Т2.Товар

СГРУППИРОВАТЬ ПО
Т1.Товар,
Т1.Свойство,
Т1.Количество,
Т1.Индекс
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
КОЛИЧЕСТВО(*) КАК Номер,
Т1.Товар,
Т1.Потребитель,
1 КАК Количество
ПОМЕСТИТЬ ИндексПотребителя
ИЗ
ПотребителиПоштучно КАК Т1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ПотребителиПоштучно КАК Т2
ПО Т1.Товар = Т2.Товар
И Т1.Индекс >= Т2.Индекс

СГРУППИРОВАТЬ ПО
Т1.Товар,
Т1.Потребитель,
Т1.Количество,
Т1.Индекс
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Т1.Товар,
Т1.Свойство,
Т2.Потребитель,
СУММА(Т1.Количество) КАК Количество
ИЗ
ИндексСвойства КАК Т1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ИндексПотребителя КАК Т2
ПО Т1.Товар = Т2.Товар
И Т1.Номер = Т2.Номер

СГРУППИРОВАТЬ ПО
Т1.Товар,
Т1.Свойство,
Т2.Потребитель


Благодарю всех неравнодушных за оперативную поддержку в решении задачи.
18 апр 13, 19:01    [14200314]     Ответить | Цитировать Сообщить модератору
 Re: задачка про яблоки или слияние 2х таблиц  [new]
Shakill
Member

Откуда: мск
Сообщений: 1880
v64,

гулять так гулять
вариант invm одним запросом для 2012 сервера.

with prd as (
	select p.colour, sum(p.qty) over (order by p.colour rows unbounded preceding) AS finish,
		sum(p.qty) over (order by p.colour rows unbounded preceding) - p.qty + 1 AS start
	from prod p		
),
gst as (
	select g.guestname, sum(g.qty) over (order by g.guestname rows unbounded preceding) AS finish,
		sum(g.qty) over (order by g.guestname rows unbounded preceding) - g.qty + 1 AS start
	from guest g	
)
select 
	p.colour, g.guestname,
	case when p.finish > g.finish then g.finish else p.finish end -
		case when g.start > p.start then g.start else p.start end + 1 AS qty -- объем пересечения	
from prd p
join gst g on p.start <= g.finish and g.start <= p.finish; -- условие пересечения
18 апр 13, 19:53    [14200471]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: задачка про яблоки или слияние 2х таблиц  [new]
M-stlyle
Member

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

Ваш вариант очень понравился.
Правда результат с ошибкой

Выдает что Валентин съел 4 Зеленых яблока.
Но он хотел всего 3 (

сможете ошибку поправить?
спасибо)
6 июл 16, 17:07    [19376768]     Ответить | Цитировать Сообщить модератору
 Re: задачка про яблоки или слияние 2х таблиц  [new]
M-stlyle
Member

Откуда:
Сообщений: 101
Может кто другой поправит?
или подскажет куда копать?
7 июл 16, 09:51    [19379118]     Ответить | Цитировать Сообщить модератору
 Re: задачка про яблоки или слияние 2х таблиц  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
M-stlyle
Может кто другой поправит?
или подскажет куда копать?

а может сами уже поравите условие... всеж написанно уже
7 июл 16, 09:58    [19379166]     Ответить | Цитировать Сообщить модератору
 Re: задачка про яблоки или слияние 2х таблиц  [new]
M-stlyle
Member

Откуда:
Сообщений: 101
Maxx
M-stlyle
Может кто другой поправит?
или подскажет куда копать?

а может сами уже поравите условие... всеж написанно уже


пытался, но никак не получилось(
7 июл 16, 12:38    [19380138]     Ответить | Цитировать Сообщить модератору
 Re: задачка про яблоки или слияние 2х таблиц  [new]
Mairos
Member

Откуда:
Сообщений: 555
M-stlyle, автор алгоритма с октября 2015 года тут не был, так что вряд ли он Вам поможет
7 июл 16, 15:07    [19381372]     Ответить | Цитировать Сообщить модератору
 Re: задачка про яблоки или слияние 2х таблиц  [new]
o-o
Guest
Mairos
M-stlyle, автор алгоритма с октября 2015 года тут не был, так что вряд ли он Вам поможет

если человек не отмечается в виде сообщений, это совсем не означает, что он совсем тут не бывает.
а уж за примером громких имен, в какой-то момент перешедших в астрал(readonly), далеко ходить не надо
as the matter of fact: число читателей данной ветки значительно превышает число писателей
7 июл 16, 15:41    [19381617]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: задачка про яблоки или слияние 2х таблиц  [new]
Sion
Member

Откуда:
Сообщений: 67
основная идея рандомно высчитать количество яблок одного цвета для каждого гостя последовательно вычитая итоговое количество


зеленые - саша = SELECT ROUND(RAND()*4),0) допустим = 1
зеленые - олег = SELECT ROUND((4-1)*RAND()+1),0)
и т.д.

вобщем то да, можно одним запросом написать решение если заранее известно количество гостей, и продуктов
9 апр 20, 04:42    [22113263]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить