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

Откуда:
Сообщений: 14
Ребята, есть такие две таблицы:
Таблица расходов и Таблица приходов
use tempdb;
go

if OBJECT_ID('#tab01','u') IS NOT NULL DROP TABLE #tab01

CREATE TABLE #tab01 (
NAME nvarchar(100)
,AMOUNT int
,PRICE float
);

INSERT INTO #tab01 (NAME, AMOUNT, PRICE)
VALUES
('МОЛОКО', 10, 80.00),
('МОЛОКО', 5, 85.00),
('СМЕТАНА', 10, 100.00),
('СМЕТАНА', 5, 105.00),
('КЕФИР', 10, 90.00),
('КЕФИР', 5, 95.00)

SELECT NAME, SUM(AMOUNT) AS AMOUNT, SUM(AMOUNT*PRICE) AS SUMA FROM #tab01 GROUP BY NAME 

if OBJECT_ID('#tab02','u') IS NOT NULL DROP TABLE #tab02

CREATE TABLE #tab02 (
NAME nvarchar(100)
,AMOUNT int
,PRICE float
);

INSERT INTO #tab02 (NAME, AMOUNT, PRICE)
VALUES
('МОЛОКО', 5, 110.00),
('МОЛОКО', 5, 110.00),
('СМЕТАНА', 5, 120.00),
('СМЕТАНА', 5, 120.00),
('КЕФИР', 5, 100.00),
('КЕФИР', 5, 100.00)

SELECT NAME, SUM(AMOUNT) AS AMOUNT, SUM(AMOUNT*PRICE) AS SUMA FROM #tab02 GROUP BY NAME 


Мне необходимо получить из этих двух наборов данных один в виде:
строка: NAME, #tab01.AMOUNT, #tab01.SUMA, #tab02.AMOUNT, #tab02.SUMA, #tab01.AMOUNT-#tab02.AMOUNT

строка: КЕФИР 15 1375,00 10 1000, 5

Никак не могу по человечески объединить :(
Какой запрос надо построить?
13 янв 16, 15:10    [18673085]     Ответить | Цитировать Сообщить модератору
 Re: Объединение значений двух (трех) наборов данных в одной таблице  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
left outer ?
13 янв 16, 15:19    [18673130]     Ответить | Цитировать Сообщить модератору
 Re: Объединение значений двух (трех) наборов данных в одной таблице  [new]
SQL_begin
Member

Откуда:
Сообщений: 14
SELECT 
 T1.NAME
 ,SUM(T1.AMOUNT) AS AMOUNT_ARR
 ,SUM(T1.AMOUNT*T1.PRICE) AS SUMA_ARR
 ,SUM(T2.AMOUNT) AS AMOUNT_EX
 ,SUM(T2.AMOUNT*T2.PRICE) AS SUMA_EX
 ,(SUM(T1.AMOUNT) - SUM(T2.AMOUNT)) AS BALANCE
 FROM #tab01 T1 
  LEFT OUTER JOIN #tab02 AS T2 ON(T2.NAME = T1.NAME)
 GROUP BY T1.NAME

Я уже пробовал, но видимо делаю ошибку. Результат выходит неправильным.
13 янв 16, 15:41    [18673248]     Ответить | Цитировать Сообщить модератору
 Re: Объединение значений двух (трех) наборов данных в одной таблице  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20974
SQL_begin
Результат выходит неправильным.

Небось T1.NAME ни фига не уникальный? тогда считай суммы в подзапросах, и только потом объединяй.
13 янв 16, 15:45    [18673277]     Ответить | Цитировать Сообщить модератору
 Re: Объединение значений двух (трех) наборов данных в одной таблице  [new]
SQL_begin
Member

Откуда:
Сообщений: 14
Akina
Небось T1.NAME ни фига не уникальный? тогда считай суммы в подзапросах, и только потом объединяй.

T1.NAME - уникальный... Но не буду полагаться на случай и введу код товара, для избежания ошибок. Цифры уж точно постараюсь сделать уникальными...
USE tempdb;
GO

if OBJECT_ID('#tab01','u') IS NOT NULL DROP TABLE #tab01

CREATE TABLE #tab01 (
ID int 
,NAME nvarchar(100)
,AMOUNT int
,PRICE float
);

INSERT INTO #tab01 (ID, NAME, AMOUNT, PRICE)
VALUES
(1,'МОЛОКО', 10, 80.00),
(1,'МОЛОКО', 5, 85.00),
(2,'СМЕТАНА', 10, 100.00),
(2,'СМЕТАНА', 5, 105.00),
(3,'КЕФИР', 10, 90.00),
(3,'КЕФИР', 5, 95.00)


if OBJECT_ID('#tab02','u') IS NOT NULL DROP TABLE #tab02

CREATE TABLE #tab02 (
ID int 
,NAME nvarchar(100)
,AMOUNT int
,PRICE float
);

INSERT INTO #tab02 (ID, NAME, AMOUNT, PRICE)
VALUES
(1,'МОЛОКО', 5, 110.00),
(1,'МОЛОКО', 5, 110.00),
(2,'СМЕТАНА', 5, 120.00),
(2,'СМЕТАНА', 5, 120.00),
(3,'КЕФИР', 5, 100.00),
(3,'КЕФИР', 5, 100.00)


SELECT DISTINCT
  T1.ID
 ,T1.NAME
 ,SUM(T1.AMOUNT) AS AMOUNT_ARR
 ,SUM(T1.AMOUNT*T1.PRICE) AS SUMA_ARR
 ,SUM(T2.AMOUNT) AS AMOUNT_EX
 ,SUM(T2.AMOUNT*T2.PRICE) AS SUMA_EX
 ,(SUM(T1.AMOUNT) - SUM(T2.AMOUNT)) AS BALANCE
 FROM #tab01 T1 
  LEFT OUTER JOIN #tab02 AS T2 ON(T2.NAME = T1.NAME)
 GROUP BY T1.ID, T1.NAME


Но, к сожалению, ошибка на месте, никуда не делась :(
Akina, конечно можно все считать подзапросами, но мне казалось, что есть элегантнее SELECT и только я где-то допускаю ошибку.
Может есть еще идеи подобного нужніх отображения результатов?
13 янв 16, 15:58    [18673363]     Ответить | Цитировать Сообщить модератору
 Re: Объединение значений двух (трех) наборов данных в одной таблице  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20974
SQL_begin
T1.NAME - уникальный...

SQL_begin
INSERT INTO #tab01 (ID, NAME, AMOUNT, PRICE)
VALUES
(1,'МОЛОКО', 10, 80.00),
(1,'МОЛОКО', 5, 85.00),
(2,'СМЕТАНА', 10, 100.00),
(2,'СМЕТАНА', 5, 105.00),
(3,'КЕФИР', 10, 90.00),
(3,'КЕФИР', 5, 95.00)
Ну и кто из нас дурак?
13 янв 16, 16:01    [18673384]     Ответить | Цитировать Сообщить модератору
 Re: Объединение значений двух (трех) наборов данных в одной таблице  [new]
iap
Member

Откуда: Москва
Сообщений: 47052
Интересно, и сколько же записей ожидается при соединении JOINом двух таблиц с двумя кефирами в каждой?
13 янв 16, 16:04    [18673422]     Ответить | Цитировать Сообщить модератору
 Re: Объединение значений двух (трех) наборов данных в одной таблице  [new]
SQL_begin
Member

Откуда:
Сообщений: 14
Akina
Ну и кто из нас дурак?

Akina, дурак бесспорно я, иначе бы не тратил чье-то время на решение своих проблем ;)

iap
Интересно, и сколько же записей ожидается при соединении JOINом двух таблиц с двумя кефирами в каждой?

iap, два кефира = два раза Вы абсолютно правы! Тыкнете мне, слепому, где выход?
13 янв 16, 16:20    [18673558]     Ответить | Цитировать Сообщить модератору
 Re: Объединение значений двух (трех) наборов данных в одной таблице  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20974
SQL_begin
где выход?

Akina
считай суммы в подзапросах, и только потом объединяй.
13 янв 16, 16:25    [18673602]     Ответить | Цитировать Сообщить модератору
 Re: Объединение значений двух (трех) наборов данных в одной таблице  [new]
Владислав Колосов
Member

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

просуммировать с группировкой по продукту сначала отдельно приход и расход.
13 янв 16, 16:27    [18673619]     Ответить | Цитировать Сообщить модератору
 Re: Объединение значений двух (трех) наборов данных в одной таблице  [new]
iap
Member

Откуда: Москва
Сообщений: 47052
SQL_begin
Akina
Ну и кто из нас дурак?

Akina, дурак бесспорно я, иначе бы не тратил чье-то время на решение своих проблем ;)

iap
Интересно, и сколько же записей ожидается при соединении JOINом двух таблиц с двумя кефирами в каждой?

iap, два кефира = два раза Вы абсолютно правы! Тыкнете мне, слепому, где выход?
Два кефира, соединённые JOINом с двумя кефирами, дадут 4 кефира. Каждый с каждым.
13 янв 16, 16:30    [18673642]     Ответить | Цитировать Сообщить модератору
 Re: Объединение значений двух (трех) наборов данных в одной таблице  [new]
SQL_begin
Member

Откуда:
Сообщений: 14
Akina
считай суммы в подзапросах, и только потом объединяй.


Получилось так:
--словарь продуктов
if OBJECT_ID('#voc01','u') IS NOT NULL DROP TABLE #voc01
CREATE TABLE #voc01 (ID int IDENTITY(1,1)  ,NAME nvarchar(100));
INSERT INTO #voc01 (NAME) VALUES ('МОЛОКО'),('СМЕТАНА'),('КЕФИР')
--таблица покупок
if OBJECT_ID('#tab01','u') IS NOT NULL DROP TABLE #tab01
CREATE TABLE #tab01 (ID_VOC int, AMOUNT int, PRICE float);
INSERT INTO #tab01 (ID_VOC, AMOUNT, PRICE)
VALUES (1, 10, 80.00),(1, 5, 85.00),(2, 10, 100.00),(2, 5, 105.00),(3, 10, 90.00),(3, 5, 95.00),(3, 2, 96.00)
--таблица продаж
if OBJECT_ID('#tab02','u') IS NOT NULL DROP TABLE #tab02
CREATE TABLE #tab02 (ID_VOC int ,NAME nvarchar(100) ,AMOUNT int ,PRICE float);
INSERT INTO #tab02 (ID_VOC, AMOUNT, PRICE)
VALUES (1, 5, 110.00), (1, 5, 110.00), (2, 5, 120.00), (2, 5, 120.00),  (3, 5, 100.00), (3, 5, 100.00)
-- тот набор данных, который хотелось получить
 SELECT 
  V.ID
 ,V.NAME
 ,(SELECT SUM(T1.AMOUNT) FROM #tab01 AS T1 WHERE T1.ID_VOC = V.ID) AS AMOUNT_ARR
 ,(SELECT SUM(T1.AMOUNT*T1.PRICE) FROM #tab01 AS T1 WHERE T1.ID_VOC = V.ID)  AS SUMA_ARR
 ,(SELECT SUM(T2.AMOUNT) FROM #tab02 AS T2 WHERE T2.ID_VOC = V.ID) AS AMOUNT_EX
 ,(SELECT SUM(T2.AMOUNT*T2.PRICE) FROM #tab02 AS T2 WHERE T2.ID_VOC = V.ID) AS SUMA_EX
 ,((SELECT SUM(T1.AMOUNT) FROM #tab01 AS T1 WHERE T1.ID_VOC = V.ID) - (SELECT SUM(T2.AMOUNT) FROM #tab02 AS T2 WHERE T2.ID_VOC = V.ID)) AS BALANCE
 FROM #voc01 V 
 GROUP BY V.ID, V.NAME
 ORDER BY V.ID


Результат тот который ожидал, но сам запрос (мне кажется) топорно выглядит. Может есть еще варианты реализации? Более элегантные?
14 янв 16, 00:11    [18675633]     Ответить | Цитировать Сообщить модератору
 Re: Объединение значений двух (трех) наборов данных в одной таблице  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20974
Ну вот почему подзапросы надо пихать в секцию SELECT, когда им место в секции FROM?
14 янв 16, 09:02    [18676147]     Ответить | Цитировать Сообщить модератору
 Re: Объединение значений двух (трех) наборов данных в одной таблице  [new]
SQL_begin
Member

Откуда:
Сообщений: 14
Akina
Ну вот почему подзапросы надо пихать в секцию SELECT, когда им место в секции FROM?


 SELECT 
  V.ID
 ,V.NAME
 ,SUM(T1.AMOUNT) AS AMOUNT_ARR
 ,SUM(T1.AMOUNT*T1.PRICE) AS SUMA_ARR
 ,SUM(T2.AMOUNT) AS AMOUNT_EX
 ,SUM(T2.AMOUNT*T2.PRICE) AS SUMA_EX
 ,SUM(T1.AMOUNT)-(SUM(T2.AMOUNT)) AS BALANCE
 FROM 
 #voc01 V, 
 #tab01 AS T1,
 #tab02 AS T2 
 WHERE 
 T1.ID_VOC = V.ID
 AND
 T2.ID_VOC = V.ID
 GROUP BY V.ID, V.NAME
 ORDER BY V.ID


Наверное не "въезжаю"...
14 янв 16, 11:03    [18676771]     Ответить | Цитировать Сообщить модератору
 Re: Объединение значений двух (трех) наборов данных в одной таблице  [new]
Glory
Member

Откуда:
Сообщений: 104751
SQL_begin
Наверное не "въезжаю"...

Все же элементарно
Просуммируйте сначала все для каждой из таблиц отдельно.
А потом уже соединяйте наборы.
14 янв 16, 11:07    [18676806]     Ответить | Цитировать Сообщить модератору
 Re: Объединение значений двух (трех) наборов данных в одной таблице  [new]
Владислав Колосов
Member

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

select ... from (select ...) t1 inner join (select ...) t2 on t2.f1=t1.f1
14 янв 16, 11:07    [18676808]     Ответить | Цитировать Сообщить модератору
 Re: Объединение значений двух (трех) наборов данных в одной таблице  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8350
Можно также предварительно выбрать во временные таблицы.
14 янв 16, 11:08    [18676816]     Ответить | Цитировать Сообщить модератору
 Re: Объединение значений двух (трех) наборов данных в одной таблице  [new]
PaulYoung
Member

Откуда: Москва
Сообщений: 2565
SQL_begin,

SELECT t.NAME, t.AMOUNT AS A1, t.SUMA AS S1, v.AMOUNT AS A2, v.SUMA AS S2, t.AMOUNT - ISNULL(v.AMOUNT, 0) AS ADIFF
FROM (
	SELECT v.NAME, SUM(t.AMOUNT) AS AMOUNT, SUM(t.AMOUNT*t.PRICE) AS SUMA
	FROM #tab01 t JOIN #voc01 v ON t.ID_VOC = v.ID
	GROUP BY v.NAME
) t
LEFT JOIN
(
	SELECT v.NAME, SUM(t.AMOUNT) AS AMOUNT, SUM(t.AMOUNT*t.PRICE) AS SUMA
	FROM #tab02 t JOIN #voc01 v ON t.ID_VOC = v.ID
	GROUP BY v.NAME
) v ON t.NAME = v.NAME
14 янв 16, 11:34    [18677052]     Ответить | Цитировать Сообщить модератору
 Re: Объединение значений двух (трех) наборов данных в одной таблице  [new]
SQL_begin
Member

Откуда:
Сообщений: 14
Вышло так:
SELECT V.ID, V.NAME, T1.AMOUNT_ARR, T1.SUMA_ARR, T2.AMOUNT_EX, T2.SUMA_EX, T1.AMOUNT_ARR - ISNULL(T2.AMOUNT_EX,0) AS BALANCE
 FROM
 (
 SELECT ID_VOC, SUM(AMOUNT) AS AMOUNT_ARR, SUM(AMOUNT*PRICE) AS SUMA_ARR
 FROM #tab01
 GROUP BY ID_VOC
) AS T1,
 (
 SELECT ID_VOC, SUM(AMOUNT) AS AMOUNT_EX, SUM(AMOUNT*PRICE) AS SUMA_EX
 FROM #tab02
 GROUP BY ID_VOC
) AS T2,
#voc01 V
WHERE T1.ID_VOC = V.ID AND T2.ID_VOC = V.ID


Результат тот, который нужен. Правильно ли само написание кода запроса?
14 янв 16, 11:40    [18677101]     Ответить | Цитировать Сообщить модератору
 Re: Объединение значений двух (трех) наборов данных в одной таблице  [new]
Glory
Member

Откуда:
Сообщений: 104751
SQL_begin
Правильно ли само написание кода запроса?

Вы про синтаксис спрашиваете ?
14 янв 16, 11:44    [18677144]     Ответить | Цитировать Сообщить модератору
 Re: Объединение значений двух (трех) наборов данных в одной таблице  [new]
PaulYoung
Member

Откуда: Москва
Сообщений: 2565
Давайте скажем ему "Правильно", хотя я бы не пихал условия соединения в секцию WHERE.
14 янв 16, 11:50    [18677185]     Ответить | Цитировать Сообщить модератору
 Re: Объединение значений двух (трех) наборов данных в одной таблице  [new]
iap
Member

Откуда: Москва
Сообщений: 47052
PaulYoung
Давайте скажем ему "Правильно", хотя я бы не пихал условия соединения в секцию WHERE.
Если у него CROSS JOIN в виде реликтовой запятой, то куда же ещё пихать-то?
В доисторическую эпоху (и по слухам сейчас в Oracle) так и писали.
14 янв 16, 12:04    [18677271]     Ответить | Цитировать Сообщить модератору
 Re: Объединение значений двух (трех) наборов данных в одной таблице  [new]
SQL_begin
Member

Откуда:
Сообщений: 14
Glory
Вы про синтаксис спрашиваете ?

Да, именно про синтаксис.

PaulYoung
Давайте скажем ему "Правильно", хотя я бы не пихал условия соединения в секцию WHERE.

Почему?

iap
Если у него CROSS JOIN в виде реликтовой запятой, то куда же ещё пихать-то?
В доисторическую эпоху (и по слухам сейчас в Oracle) так и писали.

Если бы я тупо ждал готового кода от гуру и при этом сам бы ничего не делал - не вопрос.
Сарказм уместен везде, где в одном месте присутствуют профи и дилетанты. Не вопрос - пусть будет. Только хотелось бы из темы вынести что-то положительное.
Если натупил в синтаксисе - подскажите пожалуйста где и почему в "нынешнюю эпоху" "неОракуловскую" так не пишут.
И, конечно же, спасибо за терпение ;)
14 янв 16, 12:46    [18677637]     Ответить | Цитировать Сообщить модератору
 Re: Объединение значений двух (трех) наборов данных в одной таблице  [new]
Glory
Member

Откуда:
Сообщений: 104751
SQL_begin
Glory
Вы про синтаксис спрашиваете ?

Да, именно про синтаксис.

Если бы синтаксис был неправильным, то ваш запрос даже не начал бы выполняться

SQL_begin
Почему?

Потому что соединение таблиц через запятую и where является устаревшим синтаксисом. Но пока еще поддерживаемым.
14 янв 16, 12:49    [18677668]     Ответить | Цитировать Сообщить модератору
 Re: Объединение значений двух (трех) наборов данных в одной таблице  [new]
SQL_begin
Member

Откуда:
Сообщений: 14
Glory
Потому что соединение таблиц через запятую и where является устаревшим синтаксисом. Но пока еще поддерживаемым.

Спасибо. Последний раз писал на TSQL в 2009. Видимо закостенел мозг и привычки.
Значит соединение таблиц - JOIN LEFT "вместо реликтовых запятых", а условия соединения в ON вместо "реликтовой WHERE". я правильно понял?
14 янв 16, 13:23    [18677968]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить