作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
问题
我们有一个 Users 表和一个 Addresses 表。每个用户可以有多个地址。我们有一个名为 IsDefault
的位字段,用户可以在其中选择他们的默认地址。这个字段目前不是强制性的,将来可能会是,所以我需要做一些分析。现在我想验证地址以查看:
基本上,我想了解有多少拥有多个地址的用户没有打开他们的任何地址作为默认地址。
到目前为止,我有以下 SQL 查询:
SELECT AD.User_Id,
COUNT(AD.User_Id) AS HowManyAddresses,
SUM(
CASE WHEN
AD.IsDefault IS NULL
OR
AD.IsDefault = 0
THEN
1
ELSE
0
END
) AS DefaultEmpty,
SUM(
CASE WHEN
AD.IsDefault = 1
THEN
1
ELSE
0
END
) AS DefaultAddress
FROM dbo.Addresses AS AD
JOIN dbo.Users AS U
ON U.Id = AD.User_Id
GROUP BY AD.User_ID
ORDER BY AD.User_Id
我发现的问题是我想检查 DefaultAddress
和 DefaultEmpty
SELECT SUM 字段中的值,但是在尝试使用以下方法引用它们时出现以下错误哪里或拥有:
Invalid column name 'DefaultEmpty'.
是否无法引用 SUM 值进行选择?
使用的技术:
最佳答案
实际上你需要像这样用 HAVING
重复整个 SUM
子句 -
SELECT
AD.User_Id
,COUNT(AD.User_Id) AS HowManyAddresses
,SUM(
CASE
WHEN
AD.IsDefault IS NULL OR
AD.IsDefault = 0 THEN 1
ELSE 0
END
) AS DefaultEmpty
,SUM(
CASE
WHEN
AD.IsDefault = 1 THEN 1
ELSE 0
END
) AS DefaultAddress
FROM dbo.Addresses AS AD
JOIN dbo.Users AS U
ON U.Id = AD.User_Id
GROUP BY AD.User_ID
HAVING SUM(
CASE
WHEN
AD.IsDefault IS NULL OR
AD.IsDefault = 0 THEN 1
ELSE 0
END
) = 0
ORDER BY AD.User_Id
或
DECLARE @address TABLE(UserID INT,Address VARCHAR(100),IsDefault BIT);
INSERT INTO @address VALUES
(1,'User 1 default',1)
,(2,'User 2 non default',0)
,(3,'User 3 non default',0)
,(3,'User 3 default',1)
,(4,'User 4 default',1)
,(4,'User 4 second default',1);
SELECT
COUNT(*) OVER () AS HowManyAddresses
,ISNULL(def0.DefaultEmpty, 0) AS DefaultEmpty
,ISNULL(def1.DefaultAddress, 0) AS DefaultAddress
FROM (SELECT
AD.Address
,COUNT(AD.UserID) OVER (PARTITION BY AD.UserID) AS DefaultEmpty
FROM @address AS AD
WHERE (AD.IsDefault = 0)) def0
FULL JOIN (SELECT
AD.Address
,COUNT(AD.UserID) OVER (PARTITION BY AD.UserID) AS DefaultAddress
FROM @address AS AD
WHERE (AD.IsDefault = 1)) def1
ON def0.Address = def1.Address
关于sql-server - 在 SUM 字段上使用 HAVING 或 WHERE,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/34721063/
我是一名优秀的程序员,十分优秀!