問題描述

我正在使用以下查詢來計算所有腳本運行的次數(shù)，但是當(dāng)一個軟件有 2 種類型時，它會將計數(shù)結(jié)果加倍.

I'm using the following query to count all scripts runs, but when there are 2 types for a software, it doubles the count result.

這是我正在使用的查詢:

Here is query I'm using:

SELECT 
    p.produto, p.pacote, 
    COUNT(p.produto) AS Execu??es, 
    CONVERT(VARCHAR, AVG(DATEDIFF(SECOND, p.inicio, p.fim)) / 60) + ':' + RIGHT('0' + CONVERT(VARCHAR, AVG(DATEDIFF(SECOND, p.inicio, p.fim)) % 60), 2) AS [Tempo Médio (Automatizado)], 
    t.tempo_minutos AS [Tempo Médio (Manual)], 
    CONVERT(VARCHAR, (t.tempo_minutos * 60 - AVG(DATEDIFF(SECOND, p.inicio, p.fim))) / 60) + ':' + RIGHT('0' + CONVERT(VARCHAR, (t.tempo_minutos * 60 - AVG(DATEDIFF(SECOND, p.inicio, p.fim))) % 60), 2) AS [Economia Média], 
    CONVERT(VARCHAR, (t.tempo_minutos * 60 - AVG(DATEDIFF(SECOND, p.inicio, p.fim))) * COUNT(p.produto) / 60)  + ':' + RIGHT('0' + CONVERT(VARCHAR, (t.tempo_minutos * 60 - AVG(DATEDIFF(SECOND, p.inicio, p.fim))) * COUNT(p.produto) % 60), 2) AS [Economia Total]
FROM 
    [log].pdq AS p 
INNER JOIN 
    infra.tempo_medio_execucao AS t ON t.produto = p.produto
WHERE 
    (p.equipamento NOT LIKE 'XXX%') AND (p.status = 'Sucesso')
GROUP BY 
    p.produto, p.pacote, t.tempo_minutos

此查詢返回以下結(jié)果(從表中刪除了不必要的信息):

This query is returning the following result (removed the unnecessary info from the table):

produto  | pacote        | Execu??es
---------+---------------+-----------    
SafeSign | Desinstala??o | 6
SafeSign | Instala??o    | 18
ScanBack | Instala??o    | 128

它應(yīng)該返回的地方:

produto  | pacote        |  Execu??es
---------+---------------+-----------    
SafeSign | Desinstala??o | 3
SafeSign | Instala??o    | 9
ScanBack | Instala??o    | 128

在 infra.tempo_medio_execucao 表中，我有這個數(shù)據(jù):

In the infra.tempo_medio_execucao table, I have this data:

produto      | pacote        | tempo_minutos 
-------------+---------------+--------------
ScanBack     | Instala??o    | 20
Siric Zero   | Instala??o    | 20
GRRF         | Instala??o    | 90
SICCH        | Instala??o    | 15
Outlook 2013 | Instala??o    | 25
7-Zip        | Instala??o    | 20
7-Zip        | Desinstala??o | 20
SafeSign     | Instala??o    | 20
SafeSign     | Desinstala??o | 20

表 log.pdq 將返回:

The table log.pdq will return:

id | produto   | pacote      | inicio                  | fim                     | duracao          | status 
---+-----------+-------------+-------------------------+-------------------------+------------------+--------
1  | ScanBack  | Instala??o  | 2018-09-18 11:22:54.000 | 2018-09-18 11:27:43.000 | 00:04:49.0000000 | Sucesso 
2  | ScanBack  | Instala??o  | 2018-09-18 12:10:46.000 | 2018-09-18 12:11:04.000 | 00:00:17.0000000 | Sucesso 
3  | ScanBack  | Instala??o  | 2018-09-18 12:10:49.000 | 2018-09-18 12:11:17.000 | 00:00:27.0000000 | Sucesso 
4  | GRRF      | Instala??o  | 2018-09-18 12:28:43.000 | 2018-09-18 12:29:14.000 | 00:00:30.0000000 | Sucesso

我創(chuàng)建的視圖返回:(但對于 Safesign，它應(yīng)該返回 3 和 9，而不是 6 和 18)

And the view I've created returns: (but for Safesign, it should return 3 and 9, not 6 and 18)

Produto    |  Pacote       | Execu??es  | Tempo Médio (Automatizado) | Tempo Médio (Manual) | Economia Média  | Economia Total
-----------+---------------+------------+----------------------------+----------------------+-----------------+----------------
GRRF       | Instala??o    | 1          | 0:31                       | 90                   | 89:29           | 89:29
SafeSign   | Desinstala??o | 6          | 0:00                       | 20                   | 20:00           | 120:00
SafeSign   | Instala??o    | 18         | 1:19                       | 20                   | 18:41           | 336:18
ScanBack   | Instala??o    | 128        | 1:23                       | 20                   | 18:37           | 2382:56
SICCH      | Instala??o    | 7          | 0:34                       | 15                   | 14:26           | 101:02
Siric Zero | Instala??o    | 208        | 0:33                       | 20                   | 19:27           | 4045:36

提前致謝！

推薦答案

我懷疑這符合您在問題中指定的內(nèi)容:

I suspect that this does what you specify in the question:

SELECT p.produto, p.pacote, 
       COUNT(DISTINCT p.produto) AS Execu??es, 
       . . .

你會從你的連接中得到重復(fù)——基本上是意外的匹配.

You would be getting duplicates from your joins -- basically unexpected matches.

但是，其他列也很可能(很可能)不準(zhǔn)確.如果這解決了 Execu??es 的問題，但您仍然有其他問題，請詢問另一個問題.非常清楚示例數(shù)據(jù)、預(yù)期結(jié)果以及需要修復(fù)的內(nèi)容.

However, it is quite possible (likely) that other columns are also inaccurate. If this fixes the issue with Execu??es, but you still have other issues, then ask another question. Be very clear about sample data, desired results, and what needs to be fixed.

這篇關(guān)于在 TSQL 上使用 INNER JOIN 時的雙計數(shù)結(jié)果的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！