SQL查询问题：筛选关联haydung=1的CTMAUBOCVATTU数据

初始使用的查询语句如下：

select a.*, b.haydung 
from CTMAUBOCVATTU a, vattu b  
where a.mavt = b.mavt

该查询部分结果符合预期，但有部分不符合需求。

需求说明：获取所有满足以下任一条件的CTMAUBOCVATTU表行数据：

• 同一MADDK分组下，存在关联VATTU表中haydung字段值为0的记录
• 当前行关联的VATTU表中haydung字段值为1

自己尝试编写的查询无法得到正确结果：

SELECT 
    a.MADDK, a.MAVT, a.NOIDUNG, a.SOLUONG, a.GIAVT, a.TIENVT, 
    a.GIANC, a.TIENNC, b.haydung
FROM 
    CTMAUBOCVATTU a
JOIN 
    vattu b ON a.mavt = b.mavt
WHERE 
    a.MADDK IN (SELECT MADDK
                FROM CTMAUBOCVATTU
                WHERE haydung = 0)
    AND b.haydung = 1
GROUP BY 
    a.MADDK, a.MAVT, a.NOIDUNG, a.SOLUONG, a.GIAVT, a.TIENVT, 
    a.GIANC, a.TIENNC, b.haydung
HAVING 
    COUNT(*) = 0;

表结构说明 #

CTMAUBOCVATTU表结构

CREATE TABLE [dbo].[CTMAUBOCVATTU]
(
    [MADDK] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [MAVT] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [NOIDUNG] [nvarchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [SOLUONG] [decimal] (18, 2) NULL,
    [GIAVT] [decimal] (18, 0) NULL,
    [TIENVT] [decimal] (18, 0) NULL,
    [GIANC] [decimal] (18, 0) NULL,
    [TIENNC] [decimal] (18, 0) NULL,
    [ISCTYDTU] [bit] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CTMAUBOCVATTU] 
    ADD CONSTRAINT [PK_MAUBOCVATTU] 
        PRIMARY KEY CLUSTERED ([MADDK], [MAVT]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CTMAUBOCVATTU] 
    ADD CONSTRAINT [FK_CTMAUBOCVATTU_MAUBOCVATTU] 
        FOREIGN KEY ([MADDK]) REFERENCES [dbo].[MAUBOCVATTU] ([MADDK])
GO

ALTER TABLE [dbo].[CTMAUBOCVATTU] 
    ADD CONSTRAINT [FK_MAUBOCVATTU_VATTU] 
        FOREIGN KEY ([MAVT]) REFERENCES [dbo].[VATTU] ([MAVT])

VATTU表结构

CREATE TABLE [dbo].[VATTU]
(
    [MAVT] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [MAHIEU] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TENVT] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DVT] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [GIAVT] [decimal] (19, 0) NULL,
    [GIANC] [decimal] (19, 0) NULL,
    [NGAYAD] [smalldatetime] NULL,
    [MANHOM] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HAYDUNG] [bit] NULL,
    [MANVN] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [THUEVAT] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[VATTU] 
    ADD CONSTRAINT [PK_VATTU] 
        PRIMARY KEY CLUSTERED ([MAVT]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[VATTU] 
    ADD CONSTRAINT [FK_VATTU_DVT] 
        FOREIGN KEY ([DVT]) REFERENCES [dbo].[DVT] ([DVT])
GO

ALTER TABLE [dbo].[VATTU] 
    ADD CONSTRAINT [FK_VATTU_NHANVIEN] 
        FOREIGN KEY ([MANVN]) REFERENCES [dbo].[NHANVIEN] ([MANV])
GO

ALTER TABLE [dbo].[VATTU] 
    ADD CONSTRAINT [FK_VATTU_NHOMVATTU] 
        FOREIGN KEY ([MANHOM]) REFERENCES [dbo].[NHOMVATTU] ([MANHOM])
GO

正确查询语句 #

方案一：窗口函数实现（性能更优）

通过窗口函数统计每个MADDK分组下是否存在haydung=0的记录，再筛选符合条件的行：

SELECT 
    MADDK, MAVT, NOIDUNG, SOLUONG, GIAVT, TIENVT, 
    GIANC, TIENNC, haydung
FROM (
    SELECT 
        a.*, b.haydung,
        MAX(CASE WHEN b.haydung = 0 THEN 1 ELSE 0 END) OVER (PARTITION BY a.MADDK) AS has_haydung0
    FROM CTMAUBOCVATTU a
    JOIN VATTU b ON a.MAVT = b.MAVT
) t
WHERE haydung = 1 OR has_haydung0 = 1;

方案二：子查询实现

通过子查询找出所有包含haydung=0记录的MADDK，再结合当前行haydung=1的条件筛选：

SELECT 
    a.MADDK, a.MAVT, a.NOIDUNG, a.SOLUONG, a.GIAVT, a.TIENVT, 
    a.GIANC, a.TIENNC, b.haydung
FROM CTMAUBOCVATTU a
JOIN VATTU b ON a.MAVT = b.MAVT
WHERE 
    b.haydung = 1
    OR a.MADDK IN (
        SELECT DISTINCT c.MADDK
        FROM CTMAUBOCVATTU c
        JOIN VATTU d ON c.MAVT = d.MAVT
        WHERE d.haydung = 0
    );

原查询错误分析 #

1. 子查询中直接引用haydung=0，但haydung是VATTU表的字段，CTMAUBOCVATTU表无该字段，逻辑错误
2. WHERE条件同时要求b.haydung=1和MADDK在haydung=0的分组中，最后HAVING COUNT(*)=0会过滤掉所有行，因为分组后的计数不可能为0

内容的提问来源于stack exchange，提问作者Anh Dang Vien