如何查询Engine表中各引擎对应engine_type是否全为-9并返回标识值

现有表结构与数据 #

Engine表（主键为id） #

id  engine_name
----------------
1       abc
2       def

EngineMode表（主键为id，engine_name是Engine表的外键） #

id  engine_name   engine_type
------------------------------
1       abc        R
2       abc        -9
3       abc        S
4       abc        -9
5       abc        T
6       def        -9
7       def        -9
8       def        -9

查询需求 #

查询所有Engine记录，按以下规则返回approved_engine_types字段：

• 若该引擎对应的所有engine_type不全部是-9（即存在至少一个非-9的类型），返回1（true）
• 若该引擎对应的所有engine_type全部是-9，返回0（false）

期望结果：

id  engine_name   approved_engine_types
---------------------------------------
1       abc                1
2       def                0

建表与插入数据SQL #

CREATE DATABASE test;
USE [test]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[engine](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [engine_name] [varchar](50) NOT NULL,
CONSTRAINT [PK_engine] PRIMARY KEY CLUSTERED
(
    [id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UQ_engine] UNIQUE NONCLUSTERED
(
    [engine_name] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[engine_mode](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [engine_name] [varchar](50) NOT NULL,
    [engine_type] [varchar](50) NOT NULL,
CONSTRAINT [PK_engine_mode] PRIMARY KEY CLUSTERED
(
    [id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


ALTER TABLE [dbo].[engine] ADD CONSTRAINT [DF_engine_engine_name] DEFAULT ('') FOR [engine_name]
GO
ALTER TABLE [dbo].[engine_mode] ADD CONSTRAINT [DF_engine_mode_engine_name] DEFAULT ('') FOR [engine_name]
GO
ALTER TABLE [dbo].[engine_mode] ADD CONSTRAINT [DF_engine_mode_engine_type] DEFAULT ('') FOR [engine_type]
GO
ALTER TABLE [dbo].[engine_mode] WITH CHECK ADD CONSTRAINT [FK_engine_mode_engine] FOREIGN KEY([engine_name]) REFERENCES [dbo].[engine] ([engine_name])
GO
ALTER TABLE [dbo].[engine_mode] CHECK CONSTRAINT [FK_engine_mode_engine] 
GO

USE [test]
GO
INSERT INTO [dbo].[engine] ([engine_name])
VALUES
('abc'), ('def')
GO

USE [test]
GO
INSERT INTO [dbo].[engine_mode] ([engine_name], [engine_type])
VALUES
('abc', 'R'), ('abc', '-9'), ('abc', 'S'), ('abc', '-9'), ('abc', 'T'), ('def', '-9'), ('def', '-9'), ('def', '-9')
GO

解决方案 #

方式1：直接查询语句 #

SELECT 
    e.id,
    e.engine_name,
    CASE 
        WHEN EXISTS (SELECT 1 FROM engine_mode em WHERE em.engine_name = e.engine_name AND em.engine_type != '-9')
        THEN 1
        ELSE 0
    END AS approved_engine_types
FROM engine e;

方式2：创建视图 #

CREATE VIEW vw_approved_engines
AS
SELECT 
    e.id,
    e.engine_name,
    CASE 
        WHEN EXISTS (SELECT 1 FROM engine_mode em WHERE em.engine_name = e.engine_name AND em.engine_type != '-9')
        THEN 1
        ELSE 0
    END AS approved_engine_types
FROM engine e;

查询视图即可得到结果：

SELECT * FROM vw_approved_engines;

原理说明 #

使用EXISTS子查询判断当前引擎是否存在至少一个非-9的engine_type：

• 若存在，说明不是全部为-9，返回1
• 若不存在，说明所有类型都是-9，返回0

该方式效率较高，因为EXISTS在找到第一条匹配记录后就会停止扫描，无需遍历所有数据。

内容的提问来源于stack exchange，提问作者roman_s