Блог

У меня есть запрос общего табличного выражения, который возвращает этот набор данных:

 Board_Name  Method          Source          TicketCount Percentage
IT Services NULL            NULL            73          0.7
IT Services Call            Call            6929        69.7
IT Services Call            CallByReception 4303        43.3
IT Services Call            CallBySupport   2626        37.9
IT Services Chat            Chat            8           0.1
IT Services EmailConnector  EmailConnector  2047        20.6
IT Services Internal        Internal        51          0.5
IT Services Portal          Portal          829         8.3
 

В SELECT инструкции я хотел бы вернуть все строки в приведенном выше результате, КРОМЕ строк, значение метода которых равно ‘Call’, а исходное значение равно ‘Call’.

Таким образом, результатом запроса будет это:

 Board_Name      Method          Source          TicketCount Percentage
IT Services     NULL            NULL            73          0.7
IT Services     Call            CallByReception 4303        43.3
IT Services     Call            CallBySupport   2626        37.9
IT Services     Chat            Chat            8           0.1
IT Services     EmailConnector  EmailConnector  2047        20.6
IT Services     Internal        Internal        51          0.5
IT Services     Portal          Portal          829         8.3
 

;WITH CTE
AS
(--select statement returns the above result
--details are not important
)
SELECT *
FROM CTE
WHERE --I need to include all of the rows except the row that has the Method and Source columns are equal to 'Call'

Как я могу создать where предложение, чтобы не включать одну строку?

Одним из способов было бы (демо)

  WHERE NOT 'Call' = ALL(SELECT ISNULL(Method,'') UNION SELECT ISNULL(Source,''))
 

Или аналогичные строки (демонстрация)…

 WHERE  'Call' <> ANY(SELECT ISNULL(MS,'') FROM (VALUES (Method),(Source)) V(MS))
 

Или — Демо

 WHERE NOT EXISTS (SELECT 'Call' INTERSECT SELECT Method INTERSECT SELECT Source)
 

AND/OR Для достижения этой цели используйте логику

 SELECT *
FROM   CTE
WHERE  ( Method <> 'Call' AND Source <> 'Call' )
   OR  ( ( Method = 'Call' OR Method IS NULL ) AND ( Source <> 'Call' OR Source IS NULL ) )
   OR  ( ( Source = 'Call' OR Source IS NULL ) AND ( Method <> 'Call' OR Method IS NULL ) )
 

ДЕМОНСТРАЦИЯ

Схема и примеры данных

 CREATE TABLE cte
    ([Board_Name] varchar(25), [Method] varchar(15), [Source] varchar(15), [TicketCount] int, [Percentage] numeric(22,6))
;

INSERT INTO cte
    ([Board_Name], [Method], [Source], [TicketCount], [Percentage])
VALUES
    ('IT Services',NULL, NULL, 73, 0.7),
    ('IT Services','Call', 'Call', 6929, 69.7),
    ('IT Services','Call', 'CallByReception', 4303, 43.3),
    ('IT Services','Call', 'CallBySupport', 2626, 37.9),
    ('IT Services','Chat', 'Chat', 8, 0.1),
    ('IT Services','EmailConnector', 'EmailConnector', 2047, 20.6),
    ('IT Services','Internal', 'Internal', 51, 0.5),
    ('IT Services','Portal', 'Portal', 829, 8.3)
 

Результат :

  ------------- ---------------- ----------------- ------------- ------------ 
| Board_Name  |     Method     |     Source      | TicketCount | Percentage |
 ------------- ---------------- ----------------- ------------- ------------ 
| IT Services | NULL           | NULL            |          73 | 0.700000   |
| IT Services | Call           | CallByReception |        4303 | 43.300000  |
| IT Services | Call           | CallBySupport   |        2626 | 37.900000  |
| IT Services | Chat           | Chat            |           8 | 0.100000   |
| IT Services | EmailConnector | EmailConnector  |        2047 | 20.600000  |
| IT Services | Internal       | Internal        |          51 | 0.500000   |
| IT Services | Portal         | Portal          |         829 | 8.300000   |
 ------------- ---------------- ----------------- ------------- ------------