sql server express -如何在OVER中使用COUNT子句

Question

我目前是sql查询语言的初学者，我目前正在努力使以下查询工作:

USE test;
GO
SELECT deal_type, price_type, 
    COUNT(deal_type) OVER(PARTITION BY deal_type) AS "Count1"
,COUNT(price_type) OVER(PARTITION BY deal_type) AS "Count2"  
FROM deal_price
WHERE deal_type = "rmbs", "Abs"
GO

我目前得到错误("msg 156")

所需的输出如下所示:

--deal_type, price_type, count_1
--rmbs, talk, 23
--rmbs, cvr, 40
--abs, talk, 40 

任何帮助都将非常感激。谢谢你！

Answer 1

使用In-clause:

USE test;
GO
SELECT deal_type, price_type, 
COUNT(deal_type) OVER(PARTITION BY deal_type) AS 'Count1',
COUNT(price_type) OVER(PARTITION BY deal_type) AS 'Count2'  
FROM deal_price
WHERE deal_type in ('rmbs', 'Abs')
GO