可以说我有在SQL Server 2008下表
学校表
School_Id |Course_Id | Total Students |
---------------------------------------
1 Acct101 150
1 Acct102 100
2 Acct101 110
2 Acct102 130
类表
School_Id |Course_Id | Class_ID | Capacity
---------------------------------------
1 Acct101 A1 65
1 Acct101 A2 50
1 Acct101 A3 70
1 Acct102 Ab1 100
1 Acct102 Ab2 100
2 Acct101 B1 80
2 Acct101 B2 90
根据给定的信息，我需要显示每班学生总数如下：
School_Id |Course_Id | Class_ID | Capacity | Students
-------------------------------------------------
1 Acct101 A1 65 60
1 Acct101 A2 45 40
1 Acct101 A3 70 50
1 Acct102 Ab1 100 70
1 Acct102 Ab2 100 30
2 Acct101 B1 80 60
2 Acct101 B2 90 50
我根据在校表的第一行是150在学生总数值在第3排的学生值
所以，我把150分为3类，并把阶级容量因素而分裂。 （我只需要数字，使类限制之内我的总的任何compinations。此外，所有的教室必须有学生，让学生零一类是不能接受的）
我怎样才能达致这？
请注意，我有50万行的表和另一个合计汇总值，所以我需要在值插上基于总计汇总表头。
你能做到这样，该查询首先填充具有最大容量教室：
DECLARE @School TABLE (School_Id INT,Course_Id
VARCHAR(50), Total_Students INT)
DECLARE @Class TABLE (School_Id INT,Course_Id
VARCHAR(50), Class_ID VARCHAR(50), Capacity INT)
INSERT @School VALUES
(1, 'Acct101' ,150),
(1, 'Acct102' ,100),
(2, 'Acct101' ,110),
(2, 'Acct102' ,130)
INSERT @Class VALUES
(1, 'Acct101' ,'A1' ,65),
(1, 'Acct101' ,'A2' ,50),
(1, 'Acct101' ,'A3' ,70),
(1, 'Acct102' ,'Ab1' ,100),
(1, 'Acct102' ,'Ab2' ,100),
(2, 'Acct101' ,'B1' ,80),
(2, 'Acct101' ,'B2' ,90)
;WITH y AS (
SELECT a.*,
ROW_NUMBER() OVER
(PARTITION BY a.School_ID, a.Course_ID ORDER BY a.Capacity DESC)
CapacitiyOrderPerSchoolAndCourse,
SUM(a.Capacity) OVER
(PARTITION BY a.School_ID, a.Course_ID)
TotalCapacityForSchoolAndCourse,
b.Total_Students TotalParticipants
FROM @Class a
JOIN @School b ON
b.School_Id = a.School_Id
AND b.Course_Id = a.Course_Id
), z AS(
SELECT x.School_Id,
x.Course_Id,
y.TotalCapacityForSchoolAndCourse,
y.TotalParticipants,
CASE WHEN y.TotalParticipants < SUM(x.Capacity) THEN
y.TotalParticipants
ELSE
SUM(x.Capacity)
END NumberOfStudentsInClasses,
MIN(y.Capacity) ClassCapacity,
y.Class_ID ClassName,
MIN(y.Capacity) -
CASE WHEN y.TotalParticipants - SUM(x.Capacity) < 0 THEN
ABS(y.TotalParticipants - SUM(x.Capacity))
ELSE
0
END StudentsInClass
FROM y
JOIN y x ON x.School_Id = y.School_Id
AND x.Course_Id = y.Course_Id
AND x.CapacitiyOrderPerSchoolAndCourse
<= y.CapacitiyOrderPerSchoolAndCourse
GROUP BY x.School_Id,
x.Course_Id,
y.CapacitiyOrderPerSchoolAndCourse,
y.Class_ID,
y.TotalCapacityForSchoolAndCourse,
y.TotalParticipants
)
SELECT
z.School_Id,
z.Course_Id,
z.TotalCapacityForSchoolAndCourse,
z.TotalParticipants,
z.ClassName,
z.ClassCapacity,
CASE WHEN StudentsInClass < 0 THEN
0
ELSE
StudentsInClass
END StudentsInClass
FROM z
如果你希望每个教室有学生的一些号码，你可以做这样的（它根据它分配了一些学生的每个教室的容量）：
;WITH y AS (
SELECT a.*,
SUM(a.Capacity) OVER
(PARTITION BY a.School_ID, a.Course_ID)
AS TotalCapacityForSchoolAndCourse,
b.Total_Students TotalParticipants
FROM @Class a
JOIN @School b ON
b.School_Id = a.School_Id
AND b.Course_Id = a.Course_Id
), z AS(
SELECT y.School_Id,
y.Course_Id,
y.TotalCapacityForSchoolAndCourse,
y.TotalParticipants,
MIN(y.Capacity) ClassCapacity,
y.Class_ID,
MIN(y.Capacity) * 1.0 / y.TotalCapacityForSchoolAndCourse
AS PercentOfCapacity,
ROUND(
MIN(y.Capacity) * 1.0 / y.TotalCapacityForSchoolAndCourse
* TotalParticipants
, 0, 0)
AS NumberOfStudents
FROM y
GROUP BY y.School_Id,
y.Course_Id,
y.Class_ID,
y.TotalCapacityForSchoolAndCourse,
y.TotalParticipants
)
, i AS(
SELECT
z.School_Id,
z.Course_Id,
z.TotalCapacityForSchoolAndCourse,
z.TotalParticipants,
z.Class_ID,
z.ClassCapacity,
PercentOfCapacity,
NumberOfStudents,
SUM(NumberOfStudents) OVER
(PARTITION BY z.School_Id, z.Course_Id)
AS SumNumberOfStudents,
ROW_NUMBER() OVER
(PARTITION BY z.School_Id, z.Course_Id
ORDER BY NumberOfStudents)
AS ClassWithSmallestCapacity
FROM z
), j AS(
SELECT i.School_Id,
i.Course_Id,
i.TotalCapacityForSchoolAndCourse,
i.TotalParticipants,
i.Class_ID,
i.ClassCapacity,
i.PercentOfCapacity,
i.NumberOfStudents,
i.NumberOfStudents +
CASE WHEN ClassWithSmallestCapacity = 1 THEN
TotalParticipants - SumNumberOfStudents
ELSE 0
END AS NumberOfStudents2
FROM i
)
SELECT *
FROM j
如果你有满负荷继续前进，然后直到你达到150说你example.But为第二类的情况下，你有能力为100，总学生是100，你可能会错过在这种情况下，一个或多个classes.But你可以做总运行因此只要求1将被填充和类2将具有0 student.This是更容易的方法。
另一种方法是，你得到学生总数的比值，即150和总capcty即180和使用比例，每行乘以获得学生每个类，但这里的问题是，对于某些类，你可能会得到的分数值，这是不可能的，因此你必须使用圆形或天花板上，但这些都会导致1或2名学生因是什么每一行的总分数失踪。 这是棘手的一个，有这么多排它可能会更加棘手。
编辑：在这里添加第二个方法..试试这个..
select cs.*,round(cs.capacity*rt.ratio,0) from
(
select cp.*,sc.totalstudents/cp.cap as ratio
from (select schoolid,courseid,sum(capacity) cap from class ) as cp
inner join
school sc on cp.schoolid = sc.schoolid and sc.courseid = cp.courseid
) rt
inner join class cs
on cs.schoolid = rt.schoolid and rt.courseid = cs.courseid
这应该approaximately工作，但可能会错过一些或有额外student.This以及基于索引应该是相当有效的。
你可以使用NTILE很好，但可能会妨碍性能的每一行都将与使用nesteed循环连接最可能是一个数字表结合。
尝试这个。 你必须为well.You需要一个数字表，以及这种方法使用基于this.Ntile用光标将返回完美的数据，应该是安静的快自己的表和逻辑。
set nocount on
go
drop table soh
go
select salesorderid,round(totaldue -10,0)%180 totaldue into soh
from Sales.SalesOrderHeader
go
create unique clustered index idx_soh_id on soh(salesorderid)
go
drop table sod
go
select salesorderid,salesorderdetailid,round(linetotal,0)%200 as linetotal into sod
from sales.SalesOrderDetail
go
create unique clustered index idx_sod_id on sod(salesorderid,salesorderdetailid)
go
drop table #sod
go
declare @salesorderid int,@totaldue float,@cnt int,@sm float
declare cr_cursor cursor fast_forward
for select salesorderid ,totaldue from soh
select salesorderid,salesorderdetailid, linetotal,0 as val,ROW_NUMBER() over(partition by salesorderid order by linetotal asc) as rn
into #sod from sod
create unique clustered index idx_#sod on #sod(salesorderid,salesorderdetailid)
open cr_cursor
while(1=1)
begin
fetch cr_cursor into @salesorderid,@totaldue
if (@@FETCH_STATUS <> 0)
begin
break
end
select @sm =sUM(linetotal) ,@cnt = COUNT(salesorderdetailid)
from #sod where salesorderid = @salesorderid
;with cte as
(select @salesorderid salesorderid,nt,count(*) cnt from
(select NTILE(@cnt) over( order by n.cnt asc) nt from nums n where n.cnt <= @sm - @totaldue ) dta
group by nt
)
update s
set val = c.cnt
from #sod s inner join cte c on s.SalesOrderID= c.salesorderid and s.rn= c.nt
end
close cr_cursor
deallocate cr_cursor
select * from #sod
go
