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

Откуда:
Сообщений: 16
Есть одна БД.
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

не помог.
Что делать?
21 фев 13, 14:07    [13960997]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сделать запрос  [new]
Гость333
Member

Откуда:
Сообщений: 3683
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
}
21 фев 13, 14:21    [13961114]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сделать запрос  [new]
Yoki
Member

Откуда:
Сообщений: 16
Гость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);

21 фев 13, 14:39    [13961270]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сделать запрос  [new]
Yoki
Member

Откуда:
Сообщений: 16
Гость333
Объясните принцип, по которому была задвоена эта строка.


2 разных расхода на одну и ту же сумму в течении одного дня :)
21 фев 13, 14:41    [13961282]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сделать запрос  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
Это не с SQL-EX.RU :) ?
21 фев 13, 14:50    [13961325]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сделать запрос  [new]
Yoki
Member

Откуда:
Сообщений: 16
Cygapb-007,

не, я только сегодня вышел с этой проблемой в интернеты.
21 фев 13, 15:19    [13961572]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сделать запрос  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
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
21 фев 13, 15:22    [13961600]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сделать запрос  [new]
Yoki
Member

Откуда:
Сообщений: 16
Cygapb-007,
как научиться делать такие запросы??
21 фев 13, 15:31    [13961689]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сделать запрос  [new]
Гость333
Member

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

Удалось запустить скрипт на вашей СУБД? Что за СУБД, если не секрет?
21 фев 13, 16:04    [13961983]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сделать запрос  [new]
Yoki
Member

Откуда:
Сообщений: 16
Гость333,
на самом деле нет.

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

Но скрипт от Cygapb-007 очень крутой.
21 фев 13, 16:07    [13962001]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сделать запрос  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
нет там ничего крутого, типовой скрипт
21 фев 13, 16:17    [13962063]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сделать запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Yoki
Гость333,
на самом деле нет.

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

Но скрипт от Cygapb-007 очень крутой.
Ну, может, заменить нумерацию ROW_NUMBER() на COUNT(*) в коррелированном подзапросе,
а вместо CTE - произодные таблицы?
И всё заработает?
21 фев 13, 16:25    [13962134]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сделать запрос  [new]
Yoki
Member

Откуда:
Сообщений: 16
iap
а вместо CTE - произодные таблицы?

Еще бы знать, что это.
21 фев 13, 16:44    [13962273]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сделать запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Yoki
iap
а вместо CTE - произодные таблицы?

Еще бы знать, что это.
FROM (SELECT ... FROM ... WHERE ...) T1 FULL JOIN (SELECT ... FROM ... WHERE ...) T2 ON T1.rn=T2.rn AND ...
То, что в скобках - derived tables (производные таблицы)
21 фев 13, 16:56    [13962402]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сделать запрос  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Yoki
Еще бы знать, что это.

Это примерно так (по мотивам запроса Cygapb-007):
select *
from (
        select (select count(*) from ACCOUNTINCOMES i2 where i2.date_document = i1.date_document and i2.id < i1.id) + 1 as rn,
               *
        from ACCOUNTINCOMES i1
     ) reinc
     full join
     (
        select (select count(*) from ACCOUNTEXPENSES e2 where e2.date_document = e1.date_document and e2.id < e1.id) + 1 as rn,
               *
        from ACCOUNTEXPENSES e1
     ) reexp on reinc.date_document = reexp.date_document and reinc.rn = reexp.rn
order by coalesce(reinc.date_document, reexp.date_document)
21 фев 13, 16:57    [13962409]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сделать запрос  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
ого, а я думал этот запрос тяжелый...
with 
Incomes  as (select * from (values(1,'20130115',1), (2,'20130115',2), (1,'20130116',1), (1,'20130118',1))t(id, date_document, val)),
Expenses as (select * from (values(1,'20130115',1), (1,'20130116',1), (2,'20130116',2), (1,'20130117',1))t(id, date_document, val))
select ISNULL(i.date_document, e.date_document)date_document, i.id i_id, i.val i_val, e.id e_id, e.val e_val
from Incomes i
left join Expenses e on e.date_document=i.date_document
   and (select COUNT(*) from Incomes  i0 where i0.date_document=i.date_document and i0.id<=i.id)
      =(select COUNT(*) from Expenses e0 where e0.date_document=e.date_document and e0.id<=e.id)
union all
select ISNULL(i.date_document, e.date_document)date_document, i.id i_id, i.val i_val, e.id e_id, e.val e_val
from Incomes i
right join Expenses e on e.date_document=i.date_document
   and (select COUNT(*) from Incomes  i0 where i0.date_document=i.date_document and i0.id<i.id)
      =(select COUNT(*) from Expenses e0 where e0.date_document=e.date_document and e0.id<e.id)
order by date_document, i_id, e_id
а он в 2 раза легче (29%-71%), чем
with 
ACCOUNTINCOMES  as (select * from (values(1,'20130115',1), (2,'20130115',2), (1,'20130116',1), (1,'20130118',1))t(id, date_document, val)),
ACCOUNTEXPENSES as (select * from (values(1,'20130115',1), (1,'20130116',1), (2,'20130116',2), (1,'20130117',1))t(id, date_document, val))
select *
from (
        select (select count(*) from ACCOUNTINCOMES i2 where i2.date_document = i1.date_document and i2.id < i1.id) + 1 as rn,
               *
        from ACCOUNTINCOMES i1
     ) reinc
     full join
     (
        select (select count(*) from ACCOUNTEXPENSES e2 where e2.date_document = e1.date_document and e2.id < e1.id) + 1 as rn,
               *
        from ACCOUNTEXPENSES e1
     ) reexp on reinc.date_document = reexp.date_document and reinc.rn = reexp.rn
order by coalesce(reinc.date_document, reexp.date_document)
21 фев 13, 17:13    [13962502]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сделать запрос  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
был не прав, звиняйте...)
21 фев 13, 18:10    [13962820]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сделать запрос  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Cygapb-007,

В чём неправ?
21 фев 13, 18:13    [13962829]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сделать запрос  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
Гость333
Cygapb-007,
В чём неправ?
первый запрос намноого тяжелее :)
+ 10000 строк... :)
declare @maxrecurs int = 10000
if OBJECT_ID('tempdb..#ACCOUNTINCOMES') is null begin
   create table #ACCOUNTINCOMES  (id int, date_document datetime, val money);
   create table #ACCOUNTEXPENSES (id int, date_document datetime, val money);
   
   with recurs as(
      select 1 id, dateadd(dd,-300, getdate()) date_document, round(rand(checksum(newid()))*1000,2) val, 0 iter
      union all 
      select 
         case t.chck when 1 then 1 else r.id+1 end, 
         case t.chck when 1 then dateadd(dd,cast(rand(checksum(newid()))*3 AS int)+1,r.date_document) else r.date_document end, 
         round(rand(checksum(newid()))*1000,2), iter+1
      from recurs r 
      cross apply(select rand(checksum(newid())) rnd)g 
      cross apply(select case when g.rnd<0.3 then 1 else 0 end chck)t
      where iter<@maxrecurs)
   insert #ACCOUNTINCOMES
   select r.id, r.date_document, r.val from recurs r
   order by 2,1 option (maxrecursion 32767);
   
   with recurs as(
      select 1 id, dateadd(dd,-300, getdate()) date_document, round(rand(checksum(newid()))*1000,2) val, 0 iter
      union all
      select 
         case t.chck when 1 then 1 else r.id+1 end, 
         case t.chck when 1 then dateadd(dd,cast(rand(checksum(newid()))*3 AS int)+1,r.date_document) else r.date_document end, 
         round(rand(checksum(newid()))*1000,2), iter+1
      from recurs r cross apply(select rand(checksum(newid())) rnd)g cross apply(select case when g.rnd<0.3 then 1 else 0 end chck)t
      where iter<@maxrecurs)
   insert #ACCOUNTEXPENSES
   select r.id, r.date_document, r.val from recurs r
   order by 2,1 option (maxrecursion 32767);
end

--select * from #ACCOUNTINCOMES i
--select * from #ACCOUNTEXPENSES e


select ISNULL(i.date_document, e.date_document)date_document, i.date_document i_date_document, i.id i_id, i.val i_val, e.date_document e_date_document, e.id e_id, e.val e_val
from #ACCOUNTINCOMES i
left join #ACCOUNTEXPENSES e on e.date_document=i.date_document
   and (select COUNT(*) from #ACCOUNTINCOMES  i0 where i0.date_document=i.date_document and i0.id<i.id)
      =(select COUNT(*) from #ACCOUNTEXPENSES e0 where e0.date_document=e.date_document and e0.id<e.id)
union 
select ISNULL(i.date_document, e.date_document)date_document, i.date_document i_date_document, i.id i_id, i.val i_val, e.date_document e_date_document, e.id e_id, e.val e_val
from #ACCOUNTINCOMES i
right join #ACCOUNTEXPENSES e on e.date_document=i.date_document
   and (select COUNT(*) from #ACCOUNTINCOMES  i0 where i0.date_document=i.date_document and i0.id<i.id)
      =(select COUNT(*) from #ACCOUNTEXPENSES e0 where e0.date_document=e.date_document and e0.id<e.id)
order by date_document, i_id, e_id

--select ISNULL(i.date_document, e.date_document)date_document, i.date_document i_date_document, i.id i_id, i.val i_val, e.date_document e_date_document, e.id e_id, e.val e_val
--from #ACCOUNTINCOMES i
--full join #ACCOUNTEXPENSES e on e.date_document=i.date_document
--   and (select COUNT(*) from #ACCOUNTINCOMES  i0 where i0.date_document=i.date_document and i0.id<i.id)
--      =(select COUNT(*) from #ACCOUNTEXPENSES e0 where e0.date_document=e.date_document and e0.id<e.id)
--order by date_document, i_id, e_id

select *
from (
        select (select count(*) from #ACCOUNTINCOMES i2 where i2.date_document = i1.date_document and i2.id < i1.id) + 1 as rn,
               *
        from #ACCOUNTINCOMES i1
     ) reinc
     full join
     (
        select (select count(*) from #ACCOUNTEXPENSES e2 where e2.date_document = e1.date_document and e2.id < e1.id) + 1 as rn,
               *
        from #ACCOUNTEXPENSES e1
     ) reexp on reinc.date_document = reexp.date_document and reinc.rn = reexp.rn
order by coalesce(reinc.date_document, reexp.date_document)

-- drop table #ACCOUNTEXPENSES, #ACCOUNTINCOMES
21 фев 13, 18:42    [13962931]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сделать запрос  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
Yoki
Cygapb-007,
как научиться делать такие запросы??
Поделать упражнения на SQL-EX.RU :) Научитесь многому нехорошему
21 фев 13, 21:35    [13963665]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить