SQL Server查询选择

Question

我有选择的所有汽车的查询，使并计算各使数量响应

SELECT 
    q.Make, Count(q.ID) 
FROM 
    (SELECT 
     cars.ID, cars.Make, cars.Model, 
     cars.Year1, cars.Month1, cars.KM, cars.VIN, 
     cars.Fuel, cars.EngineCap, cars.PowerKW, 
     cars.GearBox, cars.BodyType, cars.BodyColor, 
     cars.Doors, cars.FullName, Transport.address, 
     (DateDiff(second,Getdate(),cars.AuEnd)) as r, 
     cars.AuEnd, cars.BuyNowPrice, cars.CurrentPrice 
    FROM 
     cars 
    LEFT JOIN 
     Transport ON Cars.TransportFrom = Transport.ID 
    WHERE 
     Active = 'True' 
     AND AuEnd > GETDATE() 
     AND year1 >= 1900 AND year1 <= 2015 
     AND Make in ('AUDi', 'AIXAM', 'ALPINA') 
    ORDER BY 
     cars.make ASC, cars.model ASC 
     OFFSET 50 ROWS FETCH NEXT 50 ROWS ONLY) AS q 
GROUP BY 
    q.make ORDER BY q.make ASC; 

现在我需要的结果作为第三场得到各占总数无偏移， 所以现在我得到结果

Make CountInResponse 
AIXAM 1     
ALPINA 1     
AUDI 48     

但我需要得到

Make CountInResponse Total 
AIXAM 1     1 
ALPINA 1     1 
AUDI 48    100 

我想，我需要这样的东西

SELECT 
    q.Make, Count(q.ID), 
    (SELECT Make, Count(ID) 
    FROM cars 
    WHERE Active = 'True' AND AuEnd > GETDATE() 
     AND year1 >= 1900 AND year1 <= 2015 
     AND Make in ('AUDI', 'AIXAM', 'ALPINA') 
    GROUP BY Make) as q2 
FROM 
    (SELECT    
     cars.ID, cars.Make, cars.Model, 
     cars.Year1, cars.Month1, cars.KM, cars.VIN, 
     cars.Fuel, cars.EngineCap, cars.PowerKW, 
     cars.GearBox, cars.BodyType, cars.BodyColor, 
     cars.Doors, cars.FullName, Transport.address, 
     (DateDiff(second,Getdate(),cars.AuEnd)) as r, 
     cars.AuEnd, cars.BuyNowPrice, cars.CurrentPrice 
    FROM 
     cars 
    LEFT JOIN 
     Transport ON Cars.TransportFrom = Transport.ID 
    WHERE 
     Active = 'True' 
     AND AuEnd > GETDATE() 
     AND year1 >= 1900 AND year1 <= 2015 
     AND Make in ('AUDi', 'AIXAM', 'ALPINA') 
    ORDER BY 
     cars.make ASC, cars.model ASC 
     OFFSET 50 ROWS FETCH NEXT 50 ROWS ONLY) AS q 

但我得到一个错误

消息116，级别16，状态1，10号线
只有一个表达式可以在选择列表中指定当子查询不会与EXISTS一起引入。

如何编写正确的语法？

Answer 1

问题是你正在选择q2（即）Make, Count(ID)中的两列，你不能在SQL Server中做到这一点。

尝试类似这样的东西。

WITH cte AS 
( 
    SELECT  
     row_number() OVER(order by cars.make ASC,cars.model ASC) AS rn, 
     cars.id, cars.make 
    FROM  
     cars 
    LEFT JOIN 
     transport ON cars.transportfrom = transport.id 
    WHERE  
     active = 'True' 
     AND auend > getdate() 
     AND year1 >= 1900 AND year1 <= 2015 
     AND make IN ('AUDI', 'AIXAM', 'ALPINA') 
) 
SELECT 
    make , 
    count(CASE WHEN RN BETWEEN 50 AND 100 THEN 1 END) AS countinresponse, 
    count(1) AS total 
FROM 
    cte 
GROUP BY 
    make 

，或者您需要的sub-query在select转换为correlated sub-query

SELECT q.make, 
     Count(q.id) countinresponse, 
     ( 
        SELECT Count(id) 
        FROM  cars C1 
        WHERE c1.id = q.id 
        AND  active='True' 
        AND  auend > Getdate() 
        AND  year1 >= 1900 
        AND  year1 <= 2015 
        AND  make IN ('AUDi', 
            'AIXAM', 
            'ALPINA') 
        GROUP BY make) AS total 
FROM  ( 
        SELECT cars.id, 
          cars.make 
        FROM  cars 
        LEFT JOIN transport 
        ON  cars.transportfrom=transport.id 
        WHERE  active='True' 
        AND  auend > Getdate() 
        AND  year1 >= 1900 
        AND  year1 <= 2015 
        AND  make IN ('AUDi', 
             'AIXAM', 
             'ALPINA') 
        ORDER BY cars.make ASC, 
          cars.model ASC offset 50 rowsfetch next 50 rows only) AS q 
GROUP BY q.make 
ORDER BY q.make ASC;