sql涉及到多对多的查询

来源：互联网发布：java classpath 用法编辑：程序博客网时间：2024/05/28 15:25

数据结构如下：第一个表【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 AbsorbedDetailswhere 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 AbsorbedDetailswhere AbsorbedDetails.UserID=[User].ID)when 0 then 1 else   <span id="transmark"></span>(select CONVERT(float,SUM(AbsorbedDetails.AbsNum)) from AbsorbedDetailswhere AbsorbedDetails.UserID=[User].ID)end>0.5

第22行和33行是由用户传入的查询条件

0 0