时间戳

首页 > 解决方案 > 需要 TSQL 请求

sql - 需要 TSQL 请求

问题描述

任务听起来是这样的：选择下一个开店的前10个城市

栏目：城市 | 优先权

优先级定义为城市内的买家数量 城市内不应有店铺。

初步错误决定：

SELECT
    City,
    COUNT(1) as [Priority]
FROM
    Sales.vIndividualCustomer
GROUP BY City
EXCEPT
SELECT
    City,
    COUNT(1) as [Priority]
FROM
    Purchasing.vVendorWithAddresses 
GROUP BY City
ORDER BY [Priority] DESC
GO

结果：我一开始计算优先级，唯一性就消失了。也许还有另一种方式？

PS：用作Microsoft的AdventureWorks2016数据库。

标签： sqlsql-servertsql

解决方案

您可以使用NOT EXISTS子查询

SELECT
    ic.City,
    COUNT(1) as [Priority]
FROM
    Sales.vIndividualCustomer ic
GROUP BY
    ic.City
HAVING NOT EXISTS (SELECT 1
    FROM
        Purchasing.vVendorWithAddresses va
    WHERE
        va.City = ic.City)
ORDER BY [Priority] DESC;

请注意，如果您希望引用聚合列，则NOT EXISTS必须在WHEREnot aHAVING

推荐阅读