简化MS SQL语句

Question

我在JOINS方面读了一篇很棒的文章，“INNER JOIN”和“OUTER JOIN”有什么区别？但我认为它并不能解决我的问题。

我想将两个选择语句合并为一个选择语句，并且使其对任何数据库查询都更加高效。

以下是我的内容：

SELECT
(SELECT COUNT(*) 
                        FROM dbo.sem_computer, [dbo].[V_SEM_COMPUTER], dbo.IDENTITY_MAP, dbo.SEM_CLIENT,dbo.SEM_AGENT,dbo.PATTERN
                        WHERE [dbo].[V_SEM_COMPUTER].COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
                        AND dbo.SEM_CLIENT.GROUP_ID = IDENTITY_MAP.ID
                        AND dbo.SEM_CLIENT.COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
                        AND SEM_COMPUTER.COMPUTER_ID = dbo.SEM_AGENT.COMPUTER_ID
                        AND dbo.PATTERN.PATTERN_IDX = dbo.SEM_AGENT.PATTERN_IDX
                        AND Name like '%Staten Island%'
                        AND dbo.SEM_AGENT.AGENT_VERSION LIKE '11.%'
                        AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Windows%') AS 'Windows-SEP-11',
(SELECT COUNT(*) 
                        FROM dbo.sem_computer, [dbo].[V_SEM_COMPUTER], dbo.IDENTITY_MAP, dbo.SEM_CLIENT,dbo.SEM_AGENT,dbo.PATTERN
                        WHERE [dbo].[V_SEM_COMPUTER].COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
                        AND dbo.SEM_CLIENT.GROUP_ID = IDENTITY_MAP.ID
                        AND dbo.SEM_CLIENT.COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
                        AND SEM_COMPUTER.COMPUTER_ID = dbo.SEM_AGENT.COMPUTER_ID
                        AND dbo.PATTERN.PATTERN_IDX = dbo.SEM_AGENT.PATTERN_IDX
                        AND Name like '%Staten Island%'
                        AND dbo.SEM_AGENT.AGENT_VERSION LIKE '12.%'
                        AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Windows%') AS 'Windows-SEP-12',
(SELECT COUNT(*) 
                        FROM dbo.sem_computer, [dbo].[V_SEM_COMPUTER], dbo.IDENTITY_MAP, dbo.SEM_CLIENT,dbo.SEM_AGENT,dbo.PATTERN
                        WHERE [dbo].[V_SEM_COMPUTER].COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
                        AND dbo.SEM_CLIENT.GROUP_ID = IDENTITY_MAP.ID
                        AND dbo.SEM_CLIENT.COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
                        AND SEM_COMPUTER.COMPUTER_ID = dbo.SEM_AGENT.COMPUTER_ID
                        AND dbo.PATTERN.PATTERN_IDX = dbo.SEM_AGENT.PATTERN_IDX
                        AND Name like '%Staten Island%'
                        AND dbo.SEM_AGENT.AGENT_VERSION LIKE '11.%'
                        AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Mac%') AS 'Mac-SEP-11',
(SELECT COUNT(*)
                        FROM dbo.sem_computer, [dbo].[V_SEM_COMPUTER], dbo.IDENTITY_MAP, dbo.SEM_CLIENT,dbo.SEM_AGENT,dbo.PATTERN
                        WHERE [dbo].[V_SEM_COMPUTER].COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
                        AND dbo.SEM_CLIENT.GROUP_ID = IDENTITY_MAP.ID
                        AND dbo.SEM_CLIENT.COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
                        AND SEM_COMPUTER.COMPUTER_ID = dbo.SEM_AGENT.COMPUTER_ID
                        AND dbo.PATTERN.PATTERN_IDX = dbo.SEM_AGENT.PATTERN_IDX
                        AND Name like '%Staten Island%'
                        AND dbo.SEM_AGENT.AGENT_VERSION LIKE '12.%'
                        AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Mac%')  AS 'Mac-SEP-12'

如何缩小这个 SQL 语句？即使我使用内连接（我正在努力），这个语句也非常耗费处理器资源（更不用说它似乎非常低效了）。

- Rhonda

Answer 1

你可以使用条件聚合来完成这个任务。此外，为了连接两个表格，使用适当的内联接语法代替旧式逗号分隔的连接。将筛选条件放在Where子句中。

SELECT Count(CASE
               WHEN dbo.SEM_AGENT.AGENT_VERSION LIKE '11.%'
                    AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Windows%' THEN 1
             END) AS 'Windows-SEP-11',
       Count(CASE
               WHEN dbo.SEM_AGENT.AGENT_VERSION LIKE '12.%'
                    AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Windows%' THEN 1
             END) AS 'Windows-SEP-12',
       Count(CASE
               WHEN dbo.SEM_AGENT.AGENT_VERSION LIKE '11.%'
                    AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Mac%' THEN 1
             END) AS 'Mac-SEP-11',
       Count(CASE
               WHEN dbo.SEM_AGENT.AGENT_VERSION LIKE '12.%'
                    AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Mac%' THEN 1
             END) AS 'Mac-SEP-12'
FROM   dbo.sem_computer
       INNER JOIN [dbo].[V_SEM_COMPUTER]
               ON [dbo].[V_SEM_COMPUTER].COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
       INNER JOIN dbo.SEM_AGENT
               ON SEM_COMPUTER.COMPUTER_ID = dbo.SEM_AGENT.COMPUTER_ID
       INNER JOIN dbo.SEM_CLIENT
               ON dbo.SEM_CLIENT.COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
       INNER JOIN dbo.IDENTITY_MAP
               ON dbo.SEM_CLIENT.GROUP_ID = IDENTITY_MAP.ID
       INNER JOIN dbo.PATTERN
               ON dbo.PATTERN.PATTERN_IDX = dbo.SEM_AGENT.PATTERN_IDX
WHERE  Name LIKE '%Staten Island%' 

- Pரதீப்

---

THEN 1 是什么意思？1 是否表示为 TRUE？ - Rhonda

@SohniMahiwal - 这只是一个计数值，你可以保留任何东西。 - Pரதீப்

Answer 2

在提供的答案基础上，我认为更加优雅的解决方案是将过滤值保存在一个单独的表中（将逻辑与数据分开）。

然而，只有在条件是不相交的情况下才能起作用（一对值最多只会符合一个条件），否则多个“命中”将计入更多选项。

CREATE TABLE dbo.AgentAndOsFilter
(
   Id INT IDENTITY(1, 1),
   AgentVersion VARCHAR(8),
   Os VARCHAR(16)
)

这可以用你的筛选对来填充：

INSERT INTO dbo.AgentAndOsFilter (AgentVersion, Os)
VALUES ('11.%', 'Windows%'), ('12.%', 'Windows%'), ('11.%', 'Mac%'), ('12.%', 'Mac%')

查询可以长这样：

SELECT F.Id, Count(1) Cnt
FROM   dbo.sem_computer
       INNER JOIN [dbo].[V_SEM_COMPUTER]
               ON [dbo].[V_SEM_COMPUTER].COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
       INNER JOIN dbo.SEM_AGENT
               ON SEM_COMPUTER.COMPUTER_ID = dbo.SEM_AGENT.COMPUTER_ID
       INNER JOIN dbo.SEM_CLIENT
               ON dbo.SEM_CLIENT.COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
       INNER JOIN dbo.IDENTITY_MAP
               ON dbo.SEM_CLIENT.GROUP_ID = IDENTITY_MAP.ID
       INNER JOIN dbo.PATTERN
               ON dbo.PATTERN.PATTERN_IDX = dbo.SEM_AGENT.PATTERN_IDX
       INNER JOIN dbo.AgentAndOsFilter F 
               ON dbo.SEM_AGENT.AGENT_VERSION LIKE F.AgentVersion 
                   AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE F.Os
WHERE  Name LIKE '%Staten Island%' 
GROUP BY F.Id

这种解决方案的另一个优点是，配置更改（代理的新版本，新的操作系统）只需要在表格中更改一些记录即可。

- Alexei - check Codidact