在 WHERE 子句中使用 != 时 SELECT 不起作用(使用 GROUP BY 和 HAVING COUNT)

问题描述

这个问题基于:选择多个条件为真的左连接表中的行

我现在尝试从表 1 中选择行，这些行在表 2 中与某个属性 ID 没有连接.

I am now trying to select rows from Table 1, which do not have a connection in Table 2 to a certain property ID.

这些是表格:

表 1

| ID  |   Name    |
|  1  |   test    |
|  2  |   hello   |

表 2

| ID  | PropertyID |
|  1  |     3      |
|  1  |     6      |
|  1  |     7      |
|  2  |     6      |
|  2  |     1      |

我正在使用以下查询(使用="):

I am using the following query (which is working with '='):

SELECT tab1ID
FROM table2
WHERE propertyID != 3 OR propertyID = 6
GROUP BY tab1ID
HAVING COUNT(*) = 2;

此查询应返回 ID=2，但它返回零行.我做错了什么?

This query should return ID=2, but it returns zero rows. What I am doing wrong?

非常感谢任何帮助！

我给出了一个 MWE，但这是我的实际查询:

I had given a MWE but this is my actual query:

SELECT transactionline.total FROM transactionline
LEFT JOIN product_variant ON product_variant.SKU = transactionline.SKU
LEFT JOIN product ON product_variant.productID = product.productID
LEFT JOIN connect_option_product ON connect_option_product.productID = product.productID
LEFT JOIN productattribute_option ON productattribute_option.optionID = connect_option_product.optionID
WHERE productattribute_option.optionID = 4 OR productattribute_option.optionID = 9
GROUP BY transactionline.lineID
HAVING COUNT(*) = 1
AND SUM(productattribute_option.optionID = 4) = 0
AND SUM(productattribute_option.optionID = 9) > 0

一个产品可以有多个与 optionID 的连接.此查询的目标是选择某些过滤器为真或为假的总金额.

A product can have multiple connections to the optionID's. The goal of this query is to select the total amount where some filters are true or false.

推荐答案

您的分组是正确的.但是您需要计算您不想要的价值在您的团队中的次数.该计数必须为零.

Your grouping is correct. But you need to count how many times the value you do not want is in your group. That count must be zero.

SELECT tab1ID
FROM table2
GROUP BY tab1ID
HAVING sum(propertyID = 6) > 0
AND sum(propertyID = 3) = 0

这篇关于在 WHERE 子句中使用 != 时 SELECT 不起作用(使用 GROUP BY 和 HAVING COUNT)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！