задачка про яблоки или слияние 2х таблиц / Microsoft SQL Server

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / задачка про яблоки или слияние 2х таблиц

19 сообщений из 19, страница 1 из 1

задачка про яблоки или слияние 2х таблиц

#38230698

v64

Гость

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

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

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

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

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

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

Возможно ли получить такое решение одним запросом?
Пожалуйста помогите решить!
Заранее благодарю за любые идеи.

...

Рейтинг:

0 / 0

18.04.2013, 12:50

| Ответить | Цитировать | Написать

задачка про яблоки или слияние 2х таблиц

#38230710

Паганель

Участник

Откуда: Винница

Сообщения: 22 663

Рейтинг: 0 / 0

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

...

Рейтинг:

0 / 0

18.04.2013, 12:53

| Ответить | Цитировать | Написать

задачка про яблоки или слияние 2х таблиц

#38230726

Glory

Участник

Сообщения: 105 723

Рейтинг: 0 / 0

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

Ваши данные очень упрощены.
В реальной задаче может не быть решение где "Количество" из обеих таблиц будет точно совпадать

...

Рейтинг:

0 / 0

18.04.2013, 12:57

| Ответить | Цитировать | Написать

задачка про яблоки или слияние 2х таблиц

#38230731

v64

Гость

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

...

Рейтинг:

0 / 0

18.04.2013, 13:00

| Ответить | Цитировать | Написать

задачка про яблоки или слияние 2х таблиц

#38230737

v64

Гость

Glory, Согласен. но конкретно в этой задаче общее количество первых двух таблиц совпадает. просто так получилось что потеряна связь между ними(( которую и требуется восстановить каким то хитрым образом.

...

Рейтинг:

0 / 0

18.04.2013, 13:02

| Ответить | Цитировать | Написать

задачка про яблоки или слияние 2х таблиц

#38230743

Glory

Участник

Сообщения: 105 723

Рейтинг: 0 / 0

v64в том то и дело что алгоритм может быть любым а точнее его нужно придумать
Какую реальную задачу вы решаете ?
Партиционный учет ? Заказы-оплаты ? Подбор наилучшего варианта ?
На все эти задачи алгоритмы уже давно придуманы

...

Рейтинг:

0 / 0

18.04.2013, 13:04

| Ответить | Цитировать | Написать

задачка про яблоки или слияние 2х таблиц

#38230793

Shakill

Участник

Откуда: мск

Сообщения: 1 887

Рейтинг: 0 / 0

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

можно так, например. если суммарное количество продукта в таблицах не сходится, то ответ будет для меньшего

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

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);

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.

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

...

Рейтинг:

0 / 0

18.04.2013, 13:22

| Ответить | Цитировать | Написать

задачка про яблоки или слияние 2х таблиц

#38230797

v64

Гость

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

...

Рейтинг:

0 / 0

18.04.2013, 13:23

| Ответить | Цитировать | Написать

задачка про яблоки или слияние 2х таблиц

#38230803

v64

Гость

Shakill, спасибо за идею. попробую применить ее в 1С-ном запросе.

...

Рейтинг:

0 / 0

18.04.2013, 13:27

| Ответить | Цитировать | Написать

задачка про яблоки или слияние 2х таблиц

#38230810

invm

Участник

Откуда: Москва

Сообщения: 9 956

Рейтинг: 0 / 0

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.

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;

...

Рейтинг:

0 / 0

18.04.2013, 13:33

| Ответить | Цитировать | Написать

задачка про яблоки или слияние 2х таблиц

#38231537

v64

Гость

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.Потребитель

Благодарю всех неравнодушных за оперативную поддержку в решении задачи.

...

Рейтинг:

0 / 0

18.04.2013, 19:01

| Ответить | Цитировать | Написать

задачка про яблоки или слияние 2х таблиц

#38231605

Shakill

Участник

Откуда: мск

Сообщения: 1 887

Рейтинг: 0 / 0

v64,

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

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

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; -- условие пересечения

...

Рейтинг:

0 / 0

18.04.2013, 19:53

| Ответить | Цитировать | Написать

Период между сообщениями больше года.

задачка про яблоки или слияние 2х таблиц

#39269133

M-stlyle

Гость

Shakill,

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

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

сможете ошибку поправить?
спасибо)

...

Рейтинг:

0 / 0

06.07.2016, 17:07

| Ответить | Цитировать | Написать

задачка про яблоки или слияние 2х таблиц

#39269451

M-stlyle

Гость

Может кто другой поправит?
или подскажет куда копать?

...

Рейтинг:

0 / 0

07.07.2016, 09:51

| Ответить | Цитировать | Написать

задачка про яблоки или слияние 2х таблиц

#39269457

Maxx

Участник

Откуда: Киев

Сообщения: 24 876

Рейтинг: 0 / 0

M-stlyleМожет кто другой поправит?
или подскажет куда копать?
а может сами уже поравите условие... всеж написанно уже

...

Рейтинг:

0 / 0

07.07.2016, 09:58

| Ответить | Цитировать | Написать

задачка про яблоки или слияние 2х таблиц

#39269628

M-stlyle

Гость

MaxxM-stlyleМожет кто другой поправит?
или подскажет куда копать?
а может сами уже поравите условие... всеж написанно уже

пытался, но никак не получилось(

...

Рейтинг:

0 / 0

07.07.2016, 12:38

| Ответить | Цитировать | Написать

задачка про яблоки или слияние 2х таблиц

#39269890

Mairos

Участник

Сообщения: 358

Рейтинг: 0 / 0

M-stlyle, автор алгоритма с октября 2015 года тут не был, так что вряд ли он Вам поможет

...

Рейтинг:

0 / 0

07.07.2016, 15:07

| Ответить | Цитировать | Написать

задачка про яблоки или слияние 2х таблиц

#39269926

o-o

Гость

MairosM-stlyle, автор алгоритма с октября 2015 года тут не был, так что вряд ли он Вам поможет
если человек не отмечается в виде сообщений, это совсем не означает, что он совсем тут не бывает.
а уж за примером громких имен, в какой-то момент перешедших в астрал(readonly), далеко ходить не надо
as the matter of fact: число читателей данной ветки значительно превышает число писателей

...

Рейтинг:

0 / 0

07.07.2016, 15:41

| Ответить | Цитировать | Написать

Период между сообщениями больше года.

задачка про яблоки или слияние 2х таблиц

#39945149

Sion

Участник

Сообщения: 68

Рейтинг: 0 / 0

основная идея рандомно высчитать количество яблок одного цвета для каждого гостя последовательно вычитая итоговое количество

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

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

...

Рейтинг:

0 / 0

09.04.2020, 04:42

| Ответить | Цитировать | Написать

19 сообщений из 19, страница 1 из 1

Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / задачка про яблоки или слияние 2х таблиц

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=46&msg=38230743&tid=1686252]:	0ms
get settings:	10ms
get forum list:	15ms
check forum access:	4ms
check topic access:	4ms
track hit:	28ms
get topic data:	11ms
get forum data:	3ms
get page messages:	60ms
get tp. blocked users:	1ms
others:	265ms

total:	401ms