sql涉及到多对多的查询

数据结构如下：第一个表【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行是由用户传入的查询条件