Logo MurCode
Microsoft SQL Server Новое сообщение

Помогите сделать запрос

Yoki
Дата: 21.02.2013 14:07:10
Есть одна БД.
2 сущности. Доходы и расходы.

Incomes {
id,
date_document,
id_type,
summ
}

Incomes_types {
id,
name
}

Expenses {
id,
date_document,
id_kind,
id_contragent
}

Expenses_kinds {
id,
name
}

Expenses_contragents {
id,
name,
inn
}

Надо сделать запрос, выводящий таблицу следующего вида:
date income_summ income_type expenses_summ expense_type expense_contragent_inn
10.02 1000 1 1500 1 1234567890
10.02 null null 1500 1 1234567890
10.02 null null 1500 1 1234567890
11.02 null null 1500 1 1234567890
12.02 1000 1 1200 1 1234567890
12.02 1400 1 null null null

т.е. за одну дату быть несколько записей за расход, и несколько записей за доход.

запрос вида
select
    *
    from incomes i

    full join
    expenses e
        on i.date_document = e.date_document

не помог.
Что делать?
Гость333
Дата: 21.02.2013 14:21:19
Yoki
date income_summ income_type expenses_summ expense_type expense_contragent_inn
10.02 null null 1500 1 1234567890
10.02 null null 1500 1 1234567890

Объясните принцип, по которому была задвоена эта строка.

А ещё дайте скрипт на T-SQL с исходными данными (т.е. создание таблиц — create table и их заполнение — insert), а не вот это "нечто"
автор
Incomes {
id,
date_document,
id_type,
summ
}
Yoki
Дата: 21.02.2013 14:39:47
Гость333, скрипт не из MSSQL, просто форум наиболее активен, поэтому решил создать тут, не обессудьте.
Решение для MSSQL портирую.
В скрипте нет функций для foreign keys.

Creates:
+

CREATE TABLE ACCOUNTINCOMES (
    ID             INTEGER NOT NULL,
    DATE_DOCUMENT  TIMESTAMP,
    SUMM           FLOAT,
    INCOMETYPE_ID  INTEGER,
    CREATIONTIME   TIMESTAMP,
    MODIFYTIME     TIMESTAMP
);

CREATE TABLE ACCOUNTINCOMESTYPES (
    ID            INTEGER NOT NULL,
    NAME          STRING /* STRING = VARCHAR(255) */,
    PRIVATE       BOOLEAN /* BOOLEAN = SMALLINT NOT NULL CHECK (value in (0,1)) */,
    CREATIONTIME  TIMESTAMP,
    MODIFYTIME    TIMESTAMP
);

CREATE TABLE ACCOUNTEXPENSES (
    ID             INTEGER NOT NULL,
    CONTRAGENT_ID  INTEGER,
    KIND_ID        INTEGER,
    DATE_DOCUMENT  DATETIME /* DATETIME = TIMESTAMP */,
    SUMM           FLOAT,
    SUMM_TO_OFCET  FLOAT,
    RESPONSIBLE    STRING /* STRING = VARCHAR(255) */,
    CREATIONTIME   TIMESTAMP,
    MODIFYTIME     TIMESTAMP
);

CREATE TABLE ACCOUNTCONTRAGENTS (
    ID            INTEGER NOT NULL,
    NAME          STRING /* STRING = VARCHAR(255) */,
    INN           VARCHAR(20),
    CREATIONTIME  TIMESTAMP,
    MODIFYTIME    TIMESTAMP
);

CREATE TABLE ACCOUNTEXPENSESSTATCATS (
    ID            INTEGER NOT NULL,
    NAME          STRING /* STRING = VARCHAR(255) */,
    NUMBER        STRING /* STRING = VARCHAR(255) */,
    PARENT_ID     INTEGER,
    CREATIONTIME  TIMESTAMP,
    MODIFYTIME    TIMESTAMP
);

CREATE TABLE ACCOUNTEXPENSESTYPES (
    ID            INTEGER NOT NULL,
    NAME          STRING /* STRING = VARCHAR(255) */,
    STAT_CAT_ID   INTEGER,
    CREATIONTIME  TIMESTAMP,
    MODIFYTIME    TIMESTAMP
);



inserts
+

INSERT INTO ACCOUNTINCOMES (ID, DATE_DOCUMENT, SUMM, INCOMETYPE_ID, CREATIONTIME, MODIFYTIME) VALUES (1, '13-FEB-2013 00:00:00', 1500, 2, NULL, NULL);
INSERT INTO ACCOUNTINCOMES (ID, DATE_DOCUMENT, SUMM, INCOMETYPE_ID, CREATIONTIME, MODIFYTIME) VALUES (2, '15-FEB-2013 00:00:00', 2000, 3, NULL, NULL);
INSERT INTO ACCOUNTINCOMES (ID, DATE_DOCUMENT, SUMM, INCOMETYPE_ID, CREATIONTIME, MODIFYTIME) VALUES (3, '18-FEB-2013 00:00:00', 2500, 4, NULL, NULL);
INSERT INTO ACCOUNTINCOMES (ID, DATE_DOCUMENT, SUMM, INCOMETYPE_ID, CREATIONTIME, MODIFYTIME) VALUES (5, '14-FEB-2013 00:00:00', 3500, 2, NULL, NULL);


INSERT INTO ACCOUNTINCOMESTYPES (ID, NAME, PRIVATE, CREATIONTIME, MODIFYTIME) VALUES (2, 'Лекция', 0, NULL, NULL);
INSERT INTO ACCOUNTINCOMESTYPES (ID, NAME, PRIVATE, CREATIONTIME, MODIFYTIME) VALUES (3, 'Публикация', 0, NULL, NULL);
INSERT INTO ACCOUNTINCOMESTYPES (ID, NAME, PRIVATE, CREATIONTIME, MODIFYTIME) VALUES (4, 'Консультация', 0, NULL, NULL);



INSERT INTO ACCOUNTEXPENSESTYPES (ID, NAME, STAT_CAT_ID, CREATIONTIME, MODIFYTIME) VALUES (2, 'Приобретение принтера', 1, NULL, NULL);
INSERT INTO ACCOUNTEXPENSESTYPES (ID, NAME, STAT_CAT_ID, CREATIONTIME, MODIFYTIME) VALUES (3, 'Оплата коммунальных услуг конторы', 2, NULL, NULL);
INSERT INTO ACCOUNTEXPENSESTYPES (ID, NAME, STAT_CAT_ID, CREATIONTIME, MODIFYTIME) VALUES (4, 'Подряд на ремонт помещения', 3, NULL, NULL);

INSERT INTO ACCOUNTEXPENSESSTATCATS (ID, NAME, NUMBER, PARENT_ID, CREATIONTIME, MODIFYTIME) VALUES (1, 'на приобретение приспособлений, инвентаря, приборов и др. имущества, не являющегося амортизируемым', '240', 239, NULL, NULL);
INSERT INTO ACCOUNTEXPENSESSTATCATS (ID, NAME, NUMBER, PARENT_ID, CREATIONTIME, MODIFYTIME) VALUES (2, 'на приобретение топлива, воды и энергии всех видов, расходуемых на технологические цели, отопление зданий, помещений нотариальных контор', '241', 239, NULL, NULL);
INSERT INTO ACCOUNTEXPENSESSTATCATS (ID, NAME, NUMBER, PARENT_ID, CREATIONTIME, MODIFYTIME) VALUES (3, 'на приобретение работ, услуг, связанных с осуществлением нотариальной деятельности, выполняемых сторонними организациями или индивидуальными предпринимателями', '242', 239, NULL, NULL);
INSERT INTO ACCOUNTEXPENSESSTATCATS (ID, NAME, NUMBER, PARENT_ID, CREATIONTIME, MODIFYTIME) VALUES (4, 'Общая сумма материальных расходов в связи с нотариальной деятельностью в отчетном периоде (согласно декларации 3НДФЛ), в том числе:', '239', NULL, NULL, NULL);


INSERT INTO ACCOUNTCONTRAGENTS (ID, NAME, INN, CREATIONTIME, MODIFYTIME) VALUES (1, 'testContragent1', '01234567890', NULL, NULL);
INSERT INTO ACCOUNTCONTRAGENTS (ID, NAME, INN, CREATIONTIME, MODIFYTIME) VALUES (2, 'testContragent2', '01234567891', NULL, NULL);
INSERT INTO ACCOUNTCONTRAGENTS (ID, NAME, INN, CREATIONTIME, MODIFYTIME) VALUES (3, 'testContragent3', '01234567892', NULL, NULL);


INSERT INTO ACCOUNTEXPENSES (ID, CONTRAGENT_ID, KIND_ID, DATE_DOCUMENT, SUMM, SUMM_TO_OFCET, RESPONSIBLE, CREATIONTIME, MODIFYTIME) VALUES (1, 1, 2, '13-FEB-2013 00:00:00', 1500, 1500, 'Иванов В.', NULL, NULL);
INSERT INTO ACCOUNTEXPENSES (ID, CONTRAGENT_ID, KIND_ID, DATE_DOCUMENT, SUMM, SUMM_TO_OFCET, RESPONSIBLE, CREATIONTIME, MODIFYTIME) VALUES (2, 2, 2, '13-FEB-2013 00:00:00', 2500, 2500, 'Иванов В.', NULL, NULL);
INSERT INTO ACCOUNTEXPENSES (ID, CONTRAGENT_ID, KIND_ID, DATE_DOCUMENT, SUMM, SUMM_TO_OFCET, RESPONSIBLE, CREATIONTIME, MODIFYTIME) VALUES (3, 1, 3, '15-FEB-2013 00:00:00', 3500, 3500, 'Иванов В.', NULL, NULL);
INSERT INTO ACCOUNTEXPENSES (ID, CONTRAGENT_ID, KIND_ID, DATE_DOCUMENT, SUMM, SUMM_TO_OFCET, RESPONSIBLE, CREATIONTIME, MODIFYTIME) VALUES (4, 2, 3, '15-FEB-2013 00:00:00', 4000, 4000, 'Иванов В.', NULL, NULL);
INSERT INTO ACCOUNTEXPENSES (ID, CONTRAGENT_ID, KIND_ID, DATE_DOCUMENT, SUMM, SUMM_TO_OFCET, RESPONSIBLE, CREATIONTIME, MODIFYTIME) VALUES (5, 3, 4, '18-FEB-2013 00:00:00', 4500, 4500, 'Иванов В.', NULL, NULL);
INSERT INTO ACCOUNTEXPENSES (ID, CONTRAGENT_ID, KIND_ID, DATE_DOCUMENT, SUMM, SUMM_TO_OFCET, RESPONSIBLE, CREATIONTIME, MODIFYTIME) VALUES (7, 3, 4, '18-FEB-2013 00:00:00', 5000, 5000, 'Иванов В.', NULL, NULL);
INSERT INTO ACCOUNTEXPENSES (ID, CONTRAGENT_ID, KIND_ID, DATE_DOCUMENT, SUMM, SUMM_TO_OFCET, RESPONSIBLE, CREATIONTIME, MODIFYTIME) VALUES (8, 3, 4, '19-FEB-2013 00:00:00', 5500, 5500, 'Иванов В.', NULL, NULL);

Yoki
Дата: 21.02.2013 14:41:33
Гость333
Объясните принцип, по которому была задвоена эта строка.


2 разных расхода на одну и ту же сумму в течении одного дня :)
Cygapb-007
Дата: 21.02.2013 14:50:17
Это не с SQL-EX.RU :) ?
Yoki
Дата: 21.02.2013 15:19:41
Cygapb-007,

не, я только сегодня вышел с этой проблемой в интернеты.
Cygapb-007
Дата: 21.02.2013 15:22:35
Yoki, тогда
with 
reinc as (select row_number() over (partition by date_document order by id)rn, * from Incomes),
reexp as (select row_number() over (partition by date_document order by id)rn, * from Expenses)
select * 
from reinc i
full join reexp e on e.date_document=i.date_document and e.rn=i.rn
Yoki
Дата: 21.02.2013 15:31:47
Cygapb-007,
как научиться делать такие запросы??
Гость333
Дата: 21.02.2013 16:04:53
Yoki,

Удалось запустить скрипт на вашей СУБД? Что за СУБД, если не секрет?
Yoki
Дата: 21.02.2013 16:07:52
Гость333,
на самом деле нет.

Firebird 2.5. Сделал репост на firebird-овском форуме.

Но скрипт от Cygapb-007 очень крутой.