我有一个调查数据数据库,如下所示:
create table Products(
Id int primary key,
Name nvarchar(max)
)
create table Opinions(
Id int primary key,
ProductId int foreign key references Products(Id),
IsEasyToUse bit default((0)),
IsGoodPrice bit default((0)),
IsDurable bit default((0)),
-- and so on...
)
我想创建一个报表,显示Opinions
表中的每个产品和所有意见列的百分比.
到目前为止,我一直在用天真的方式做这件事:
select
p.Name,
1.0 *
(select count(*) from Opinions o where o.ProductId = p.Id and o.IsEasyToUse = 1) /
(select count(*) from Opinions o where o.ProductId = p.Id) as EasyToUse,
1.0 *
(select count(*) from Opinions o where o.ProductId = p.Id and o.IsGoodPrice = 1) /
(select count(*) from Opinions o where o.ProductId = p.Id) as GoodPrice,
1.0 *
(select count(*) from Opinions o where o.ProductId = p.Id and o.IsDurable = 1) /
(select count(*) from Opinions o where o.ProductId = p.Id) as Durable
from
(select Id, Name from Products) p
这在技术上是可行的,但我对重复出现的条件并不满意.另外,Options表的大小越来越大,因此性能问题开始变得明显(现在1M行需要大约一分钟的时间).
有没有一种方法可以用一种更快的方式重写它(将来如果我想要应用更多的过滤器,也更容易修改)?