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

[Akina]

Цитата ^D^ima @ 16.01.19, 21:08

как-то можно это выполнить в отладочном режиме без записи самих данных в таблицу?

Конечно... комментируешь первую строку - остаётся нормальный SELECT.

Вернее, не очень нормальный. У тебя:

from Project
left JOIN Company ON Company.ID = Project.CustomerCompanyID
where (что-то) and Company.FileAs is not null

что вырождается в

from Project
inner JOIN Company ON Company.ID = Project.CustomerCompanyID
where (что-то)

Зачем заставлять сервер делать ненужную работу?

Цитата ^D^ima @ 16.01.19, 21:08

Получается как-то так тогда?

Нет. У тебя отсутствует:
- класс совмещения данных ON <merge_search_condition> - т.е. собственно то условие, по которому будет определяться, MATCHED текущая запись или NOT MATCHED.
- описание источника AS table_alias ( column_alias [ ,...n ] ), соответственно неверна ссылка на вставляемые значения.
Т.е. должно быть что-то типа

MERGE DocumentCategory AS target /* Слияние с чем */
USING ( select 24,Company.ID,12
        from Project
        inner JOIN Company ON Company.ID = Project.CustomerCompanyID
        where ((Project. EndDate BETWEEN '01.01.2017' AND '01.01.2019')or (Project.ContractDate<'01.01.2017'and Project.State<>4))
        group by Company.id) AS source (DocumentClass,DocumentID,CategoryID) /* Слияние откуда */
ON (target.DocumentID = source.DocumentID) /* Слияние по условию */
WHEN NOT MATCHED THEN /* действие при невыполнении условия */
    INSERT (DocumentClass,DocumentID,CategoryID) /* в какие поля вставлять */
    VALUES (source.DocumentClass,source.DocumentID,source.CategoryID) /* какие данные вставлять */

[^D^ima]

Цитата Akina @ 17.01.19, 04:35

ON (target.DocumentID = source.DocumentID) /* Слияние по условию */

А остальные условия не должны там быть?

ON (target.DocumentClass=24, target.DocumentID = source.DocumentID, target.CategoryID=12)

[Akina]

Цитата ^D^ima @ 17.01.19, 06:30

А остальные условия не должны там быть?

Там должно быть выражение, по которому выявляется дубликат. У тебя дубликат - по одному полю.

[^D^ima]

Цитата Akina @ 17.01.19, 11:31

У тебя дубликат - по одному полю.

По 3 получается.

Т.е. если в таблице нет записи 24,1023,12 то она создается, если есть не создается. Но если смотреть просто по 2 полю, например есть ли такое число 1023, то это не верно, т.к. уже писал что поле не уникально

[Akina]

ааа, вон оно как... тогда да, все три поля...

ON (targetDocumentClass = 24 AND target.DocumentID = source.DocumentID AND target.CategoryID = 12)

[^D^ima]

На что ругается?

Сообщение 547, уровень 16, состояние 0, строка 5
Конфликт инструкции MERGE с ограничением FOREIGN KEY "DocumentCategoriesFK". Конфликт произошел в базе данных "PJM10", таблица "dbo.Document".

Прикреплённый файлsql.png (53,79 Кбайт, скачиваний: 1173)

[^D^ima]

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

USE [PJM10]
GO
/****** Object:  StoredProcedure [dbo].[adfDocumentCategoryUpdate]    Script Date: 23.01.2019 17:47:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**      File:
**      Name: adfDocumentCategoryUpdate
**      Desc:
**
**      This template can be customized:
**              
**      Return values:
**
**      Called by:  
**              
**      Parameters:
**      Input                           Output
**      ----------                      -----------
**
**      Auth:
**      Date:
*******************************************************************************
**      Change History
*******************************************************************************
**      Date:       Author:         Description:
**      --------    --------        -------------------------------------------
**      19.02.13    SAP             Отказ от OPENXML
*******************************************************************************/
ALTER Procedure [dbo].[adfDocumentCategoryUpdate]
    @DocumentID TIdentifier,
    @DocumentClass TClass,
    @Xml XML
AS
    DECLARE @ID TIdentifier
    SET NOCOUNT ON
 
    DECLARE categories CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
    SELECT
        tab.col.value('Name[1]','nvarchar(256)'),
        tab.col.value('State[1]','int')
    FROM @Xml.nodes('//Item') tab(col)
 
    OPEN categories
    DECLARE @name TName, @state INT
    
    FETCH NEXT FROM categories INTO @name, @state
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @ID = dbo.adfGetCategoryID(@name, @DocumentClass)
        PRINT @name
        PRINT @ID
        IF @state = 2 -- added
        BEGIN
            IF NOT EXISTS(SELECT * FROM [DocumentCategory] WHERE [DocumentID] = @DocumentID AND [DocumentClass] = @DocumentClass AND [CategoryID] = @ID)
            BEGIN
                INSERT [DocumentCategory] ([DocumentID], [DocumentClass], [CategoryID])
                VALUES(@DocumentID, @DocumentClass, @ID)
            END
        END
        ELSE IF @state = 3 -- deleted
        BEGIN
            IF EXISTS(SELECT * FROM [DocumentCategory] WHERE [DocumentID] = @DocumentID AND [DocumentClass] = @DocumentClass AND [CategoryID] = @ID)
            BEGIN
                DELETE [DocumentCategory] WHERE [DocumentID] = @DocumentID AND [DocumentClass] = @DocumentClass AND [CategoryID] = @ID
            END
        END
        IF @@error != 0 GOTO ERROR_HANDLER
        FETCH NEXT FROM categories INTO @name, @state
    END
    
    CLOSE categories
    DEALLOCATE categories
    
    RETURN 0
ERROR_HANDLER:
    CLOSE categories
    DEALLOCATE categories
    
    RETURN 1

Кстати там не MARGE используется почему-то а INSERT при IF EXISTS, так лучше?

Добавлено 23.01.19, 15:15
Он вот с таким запускается параметрами:

declare @p3 xml
set @p3=convert(xml,N'<List><Item><Name>Новый Клиент</Name><State>2</State></Item></List>')
exec adfDocumentCategoryUpdate @DocumentID=1042,@DocumentClass=9,@Xml=@p3

Я правильно понимаю что можно тупо этот код напрямую вызывать, как-то обернув через select? Не подскажешь как?
Я имею в виду что вызвать эту процедуру для каждой выборки select?

select 24,Company.ID,12
        from Project
        inner JOIN Company ON Company.ID = Project.CustomerCompanyID
        where ((Project. EndDate BETWEEN '01.01.2017' AND '01.01.2019')or (Project.ContractDate<'01.01.2017'and Project.State<>4))
        group by Company.id

[^D^ima]

Друзья, видимо пятница, не пойму как решить простую с виду задачу:

Взять клиентов, проекты по которым были в 2016-2017 годах, но не были в 2018-2019

Сделал так вначале, но понял что это не сработает

select query1.ID,query1.FileAs
from
 
(select Company.ID, Project.FileAs
from Company
inner JOIN Project ON Company.ID = Project.CustomerCompanyID
where (Project.Date BETWEEN '01.01.2016' AND '01.01.2018' and Project.State=4/*Завершен*/)
group by Company.id,Project.FileAs) as query1,
 
(select Company.ID
from Company
inner JOIN Project ON Company.ID = Project.CustomerCompanyID
where not (Project.Date BETWEEN '02.01.2018' AND '01.01.2019')
group by Company.id) as query2
 
where query1.ID=query2.ID

Т.е. идея была взять компании с проектами в 2016-2017 и отдельно без проектов в 2018-2019 и соединить по ID, но получается что 2-й подзапрос не работает, т.к. там будут те-же клиенты что и из 1-го запроса

[LMM]

А просто по-деревенски выбрать из Company клиентов, для которых where (Project.Date BETWEEN '01.01.2016' AND '01.01.2018' and Project.State=4/*Завершен*/) не рулит? Или я чего-то не понял?

[^D^ima]

LMM
А как ты поймешь что по ним не было проектов в 18 и 19 годах?

[LMM]

Цитата ^D^ima @ 22.03.19, 10:25

Взять клиентов, проекты по которым были в 2016-2017 годах, но не были в 2018-2019

Цитата ^D^ima @ 22.03.19, 19:41

А как ты поймешь что по ним не было проектов в 18 и 19 годах?

select c.Id, p.FileAs
  from company c
 inner join projects p on p.CustomerCompanyID = c.Id
   and ((p.Date between '01.01.2016' and '01.01.2018' and p.State = 4) and (p.Date not between '01.01.2016' and '01.01.2018'))
 order by c.Id, p.FileAs

^D^ima, сам понимаешь - без живой БД всё это пляски с бубном .

[Akina]

Цитата ^D^ima @ 22.03.19, 19:41

А как ты поймешь что по ним не было проектов в 18 и 19 годах?

GROUP BY проект
HAVING SUM(CASE WHEN год IN (2016, 2017) THEN 1 ELSE 0 END) > 0
   AND SUM(CASE WHEN год IN (2018, 2019) THEN 1 ELSE 0 END) = 0

[^D^ima]

Akina
Гениально!

[^D^ima]

10 месяцев не писал запросов и опять память подводит, час потратил, не решил.

Есть дерево проектов.
Как мне его свернуть до корневых проектов, суммировав деньги?

Есть таблица

ID_КорневойПроект	ID_ПодчиненныйПроект	ID_Проект	Проект	Деньги
2	1	1	Проект 1	500
2	3	3	Проект 3	200
null	null	2	Проект 2	300
null	null	4	Проект 4	2000

На выходе должно быть:

ID_КорневойПроект	ID_ПодчиненныйПроект	ID_Проект	Проект	Деньги
null	null	2	Проект 2	1000
null	null	4	Проект 4	2000

Все записи где есть ID_КорневойПроект должны схлопнуться в ID_Проект с суммированием денег. Название должно остаться корневого проекта

[Akina]

Для SQL Server:

WITH cte AS ( SELECT id id_k, id_p, id, p, money
              FROM test
              WHERE id_k IS NULL
            UNION ALL
              SELECT cte.id_k, test.id_p, test.id, test.p, test.money
              FROM test, cte
              WHERE cte.id = test.id_k )
 
SELECT test.id, test.id_p, test.id, test.p, SUM(cte.money) money
FROM cte, test
WHERE cte.id_k = test.id
GROUP BY test.id, test.id_p, test.id, test.p

fiddle

Для MySQL/MariaDB/SQLite/PosttgreSQL добавить RECURSIVE.