Доступ к запросу Vb6
Мне нужна ваша помощь в создании SQL-запроса с использованием vb6 и базы данных доступа. Вот сценарий: 2 стола, дай и получи Поля Tb1 Id, Name, Amount Идентификатор Tb2, имя, сумма Мне нужно иметь общую сумму для каждого имени в обеих таблицах, чтобы иметь общий столбец Give и общий столбец, но мой запрос не работает
Select tb1.id,tb1.name,sum(tb1.amount) as TG, tb2.id,tb2.name,sum(tb2.amount) as TH
from tb1 inner join
tb2
on tb1.id=tb2.id
group by... Etc
Если у меня есть 10 записей, где id = 1 в tb1 и 3 записи в tb 2, общая сумма в tb2 неверна (она повторяет сумму в tb2 для каждой записи в tb1)
Я также пытался использовать Union, чтобы получить правильный результат в строке, но я должен получить что-то вроде
Id Name Have Give
1 John Doe 200,00 76,00
Я надеюсь объяснить лучше с помощью фотографий
Triyng @Parfait предполагает, что полученный результат очень похож на запрос, который я написал ранее.
Заранее спасибо за вашу помощь
Ответы 3
Попробуйте использовать union all, а затем агрегировать:
Select id, name, sum(tg) as tg, sum(th) as th
from (select id, name, amount as tg, 0 as th from tb1
union all
select id, name, 0, amount from tbl2
) as t
group by id, name;
Я не уверен, что все версии MS Access поддерживают union all в предложении from таким образом. Если нет, то этот фрагмент необходимо инкапсулировать в представление.
Рассмотрите возможность объединения агрегатов обеих таблиц по отдельности с помощью id:
Совокупные запросы (сохранить как сохраненные запросы доступа)
SELECT tb1.idF
, tb1.[name]
, SUM(tb1.Give) AS TG
FROM tblGive tb1
GROUP BY tb1.idF
, tb1.[name]
SELECT tb2.IDB
, tb2.[name]
, SUM(tb2.Have) AS TH
FROM tblHave tb2
GROUP BY tb2.IDB
, tb2.name
Окончательный запрос (запуск Full Join Query для возврата всех различных имен в обеих таблицах)
SELECT NZ(agg1.idF, agg2.idB) AS [id]
, NZ(agg1.name, agg2.name) AS [name]
, NZ(agg2.TH, 0) AS [Have]
, NZ(agg1.TG, 0) AS [Give]
FROM tblGiveAgg agg1
LEFT JOIN tblHaveAgg agg2
ON agg1.idF = agg2.idB
UNION
SELECT NZ(agg1.idF, agg2.idB) AS [id]
, NZ(agg1.name, agg2.name) AS [name]
, NZ(agg2.TH, 0) AS [Have]
, NZ(agg1.TG, 0) AS [Give]
FROM tblGiveAgg agg1
RIGHT JOIN tblHaveAgg agg2
ON agg1.idF = agg2.idB;
Чтобы продемонстрировать приведенные ниже данные
CREATE TABLE tblGive (
ID AUTOINCREMENT,
IdF INTEGER,
[Name] TEXT(10),
Give INTEGER
);
INSERT INTO tblGive (IdF, [Name], [Give]) VALUES (1, 'JOHN', 37);
INSERT INTO tblGive (IdF, [Name], [Give]) VALUES (2, 'ANNA', 10);
INSERT INTO tblGive (IdF, [Name], [Give]) VALUES (3, 'BILL', -37);
INSERT INTO tblGive (IdF, [Name], [Give]) VALUES (2, 'ANNA', 116);
INSERT INTO tblGive (IdF, [Name], [Give]) VALUES (1, 'JOHN', 120);
CREATE TABLE tblHave (
ID AUTOINCREMENT,
IDB INTEGER,
[Name] TEXT(10),
Have INTEGER
);
INSERT INTO tblHave (IDB, [Name], [Have]) VALUES (1, 'JOHN', 200);
INSERT INTO tblHave (IDB, [Name], [Have]) VALUES (2, 'ANNA', 400);
INSERT INTO tblHave (IDB, [Name], [Have]) VALUES (3, 'BILL', 150);
INSERT INTO tblHave (IDB, [Name], [Have]) VALUES (1, 'JOHN', 25);
INSERT INTO tblHave (IDB, [Name], [Have]) VALUES (1, 'JOHN', 70);
Окончательный запрос полного соединения возвращает следующий результат:
Привет @Parfait, спасибо также за ваш ответ. Ваш sql-запрос дает результат, но он отличается от ожидаемого, потому что объединяет только имена в обеих таблицах. Итак, если у меня есть сценарий, который я проиллюстрирую ниже Tb1 1 John 150 2 Mike 300 1 John 200 Tb2 16 Tony 32 1 John 50 20 Anna 25, он дает только John с его правильной суммой в разнице Give / Have, вместо этого я должен хотеть найти 1 Джон 350 50 2 Майк 300 0 20 Анна 0 25 Спасибо
Спасибо @Parfait, но я получаю синтаксическую ошибку в процедуре присоединения. Очевидно, что два запроса, представленные выше, работают отлично, но не последний запрос. я все еще делаю некоторые тесты, чтобы увидеть, вызвана ли ошибка ошибками транскрипции
Спасибо за ваш новый ответ, я заметил забывчивость и уже исправил ее в своем тесте, запрос работает отлично, к сожалению, я должен вам сказать, что даже эта формула не дает того результата, которого я хотел. Я повторяю имена так же, как и при использовании обычного ВНУТРЕННЕГО СОЕДИНЕНИЯ.
Спасибо @Parfait, я разместил 3 поясняющих фото ;-)
Третий. Первое изображение — это таблица с названием Give, вторая — таблица Have. Как видите, в каждой таблице можно найти множество независимых движений. Итак, Билл должен получить деньги, но ему нечем платить, у других есть записи в обеих таблицах. Результатом может быть синтез всех движений в столбце с одной строкой для каждой IDF.
Как этот последний набор результатов может быть результатом этого решения? Агрегированные запросы должны возвращать одну пару Id и Name с Have и Give, объединенными в отдельные значения. Я только что проверил в Access, используя ваши первые два изображения, и вернул 3 записи для 1 Джона, 2 Анны, 3 Билла. (Я предполагаю, что 3-я строка на 2-м изображении должна быть «Билл».) Возможно, вы используете ID, который является автонумерацией в ваших изображениях, но ID в этом решении будет соответствовать вашим IdF и IDB.
Вы использовали IDF и IDB тогда? Не используйте ID нигде в вашей версии, включая агрегированные и окончательные запросы полного соединения. (Пожалуйста, удалите более ранние комментарии, которые больше не актуальны).
Да, как вы предложили в своем запросе, я использовал поля IDF и IDB, но в результате вы видите то, что вы видите на третьем рисунке. Если вы затем скажете мне, что вы попробовали свой запрос на доступ, и он работает, я должен посмотреть, не было ли случайно что-то плохо изменено мной. Мне больше не о чем думать. я попробую еще раз
См. мой расширенный ответ, демонстрирующий решение с вашими опубликованными данными. Я также настроил запросы, чтобы использовать IDF и IDB.
Использование этого DDL
CREATE TABLE tb1 (
ID AUTOINCREMENT,
IdF INTEGER,
[Name] TEXT(10),
Give INTEGER
);
INSERT INTO tb1 (IdF, [Name], [Give]) VALUES (1, 'JOHN', 37);
INSERT INTO tb1 (IdF, [Name], [Give]) VALUES (2, 'ANNA', 10);
INSERT INTO tb1 (IdF, [Name], [Give]) VALUES (3, 'BILL', -37);
INSERT INTO tb1 (IdF, [Name], [Give]) VALUES (2, 'ANNA', 116);
INSERT INTO tb1 (IdF, [Name], [Give]) VALUES (1, 'JOHN', 120);
CREATE TABLE tb2 (
ID AUTOINCREMENT,
IDB INTEGER,
[Name] TEXT(10),
Have INTEGER
);
INSERT INTO tb2 (IDB, [Name], [Have]) VALUES (1, 'JOHN', 200);
INSERT INTO tb2 (IDB, [Name], [Have]) VALUES (2, 'ANNA', 400);
INSERT INTO tb2 (IDB, [Name], [Have]) VALUES (3, 'BILL', 150);
INSERT INTO tb2 (IDB, [Name], [Have]) VALUES (1, 'JOHN', 25);
INSERT INTO tb2 (IDB, [Name], [Have]) VALUES (1, 'JOHN', 70);
Этот UNION ALL запрос работает
SELECT Name
, SUM(Give) AS TotalGive
, SUM(Have) AS YotalHave
FROM (
SELECT Name, Give, 0 AS Have
FROM tb1
UNION ALL
SELECT Name, 0 AS Give, Have
FROM tb2
) AS t
GROUP BY Name;
Спасибо, Гордон, к сожалению, я попробовал этот запрос, но получил ошибку, поэтому я думаю, что он не поддерживается. Однако я просто пытаюсь что-то изменить. Я сообщу вам позже