数据结构如下:第一个表【User】,第二个表【AbsorbedDetails】
AbsName :律师专注的领域
AbsNum : 律师接到的案子个数
某个律师的专注比例:一个律师的某领域的案子数量/一个律师所有领域案子个数和
SQL 思考过程:
1.
select * from [User]
where
单人案子个数/单人案子个数总和>0.5 //比如查专注比例大于50%的律师
2.
select * from [User]
where
(select CONVERT(float,SUM(AbsorbedDetails.AbsNum))
from AbsorbedDetails
where AbsorbedDetails.UserID=[User].ID and AbsorbedDetails.AbsID=3)
/
(select CONVERT(float,SUM(AbsorbedDetails.AbsNum))
from AbsorbedDetails
where AbsorbedDetails.UserID=[User].ID)>0.5
//这样基本sql已经完成,只是除数会为零,下一步只要做除数不为零操作
3.
select * from [User]
where
(select CONVERT(float,SUM(AbsorbedDetails.AbsNum))
from AbsorbedDetails
where AbsorbedDetails.UserID=[User].ID and AbsorbedDetails.AbsID=3)
/
case
(select CONVERT(float,SUM(AbsorbedDetails.AbsNum))
from AbsorbedDetails
where AbsorbedDetails.UserID=[User].ID)
when 0 then 1 else <span id="transmark"></span>
(select CONVERT(float,SUM(AbsorbedDetails.AbsNum))
from AbsorbedDetails
where AbsorbedDetails.UserID=[User].ID)
end
>0.5
第22行和33行是由用户传入的查询条件