問題描述
我正在使用以下查詢來計算所有腳本運行的次數，但是當一個軟件有 2 種類型時，它會將計數結果加倍.
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
此查詢返回以下結果(從表中刪除了不必要的信息):
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
它應該返回的地方:
produto | pacote | Execu??es
---------+---------------+-----------
SafeSign | Desinstala??o | 3
SafeSign | Instala??o | 9
ScanBack | Instala??o | 128
在 infra.tempo_medio_execucao 表中，我有這個數據:
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
我創建的視圖返回:(但對于 Safesign，它應該返回 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
提前致謝！
推薦答案
我懷疑這符合您在問題中指定的內容:
I suspect that this does what you specify in the question:
SELECT p.produto, p.pacote,
COUNT(DISTINCT p.produto) AS Execu??es,
. . .
你會從你的連接中得到重復——基本上是意外的匹配.
You would be getting duplicates from your joins -- basically unexpected matches.
但是，其他列也很可能(很可能)不準確.如果這解決了 Execu??es 的問題，但您仍然有其他問題，請詢問另一個問題.非常清楚示例數據、預期結果以及需要修復的內容.
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.
這篇關于在 TSQL 上使用 INNER JOIN 時的雙計數結果的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！