代码之家 › 专栏 › 技术社区 › Jay Chilled

SQL server management studio子查询

subquery sql-server sql

-1

Jay Chilled · 技术社区 · 8 年前

我试图在SQL Server management Studio 2016中运行此子查询,但它出错。它在MySQL中工作。请告知:

select count(distinct company) 
from 
(
select company, sum(net_value_gbp) as last2yr_spend 
from Orders 
where bill_date >='01-Jan-2016' 
group by company)
where last2yr_spend >50

6 回复 | 直到 8 年前

Gordon Linoff 8 年前

别名是一个问题。但是,您应该这样编写查询:

select count(*) 
from (select company, sum(net_value_gbp) as last2yr_spend 
      from Orders  o
      where bill_date >= '2016-01-01'
      group by company
     ) c
where last2yr_spend > 50;

笔记:

注意子查询的别名(您的直接问题)。
COUNT(DISTINCT) 不需要。子查询为每个公司返回一行。 计数(不同) 会产生额外的开销,所以 COUNT(*) 足够了。
日期格式使用ISO标准YYYY-MM-DD。对于大多数国际化设置,SQL Server都理解这种格式(为了百分之百的完整性,您可以删除连字符,但我喜欢它们的可读性)。
你只想 COUNT(company) (而不是 计数(*) )如果 company 曾经 NULL *你不想数一数。

Yogesh Sharma 8 年前

子查询的别名如下所示

SELECT COUNT(DISTINCT company)
FROM
(
    SELECT company,
           SUM(net_value_gbp) AS last2yr_spend
    FROM Orders o
    WHERE bill_date >= '01-Jan-2016'
    GROUP BY company
) A
WHERE A.last2yr_spend > 50;

Denis Rubashkin 8 年前

select count(company) 
from 
(
select company, sum(net_value_gbp) as last2yr_spend 
from Orders 
where bill_date >='01-Jan-2016' 
group by company
having sum(net_value_gbp) > 50) as T1

Md. Suman Kabir 8 年前

按以下方式操作:

select count(distinct company) 
from 
( select company, sum(net_value_gbp) as last2yr_spend 
from Orders 
where bill_date >= '01-Jan-2016' 
group by company ) 
AS T1
where T1.last2yr_spend > 50

您必须使用 ALIAS 用于sql server中的子查询。

Alexei - check Codidact 8 年前

子查询的另一种选择是CTE,我发现它的可读性稍高一些。如下所示:

    ;with orders_by_comp as (
          select company, sum(net_value_gbp) as last2yr_spend 
          from Orders  o
          where bill_date >= '2016-01-01'
          group by company)
    select count(distinct company) 
    from orders_by_comp 
    where last2yr_spend > 50;

user9152967 user9152967 8 年前

您可以使用 having 这样地:

with cte
AS
(
    select company
    from Orders 
    where bill_date >='01-Jan-2016' 
    group by company
    having sum(net_value_gbp) > 50
) 
select count(distinct company)
from cte;