22,209
社区成员
发帖
与我相关
我的任务
分享
DECLARE @TB TABLE
(
F1 NVARCHAR(10),
F2 FLOAT,
F3 FLOAT
)
INSERT INTO @TB
SELECT 'A', 40, 0
UNION SELECT 'A', 20, 0
UNION SELECT 'A', 10, 0
UNION SELECT 'B', 20, 0
UNION SELECT 'B', 30, 0
有另外一个表限定A的数量总和必须为200, B必须为100.
如果总数达不到的话, 按照百分比进行分配, 将得到的数量填入C.如下
F1 F2 F3 F3最终
A 40 74.28 73
A 20 37.14 38
A 10 18.57 19
B 20 20 20
B 30 30 30
A的总数为70, 则需要添加数量为200-70=130. 然后按照百分比分配
因为数量必须为整数且不允许超出200的总数,所以除最大数量(40)之外,
其他做CEILING, 然后最大数量在用130减去已经分配的数量130-38-19=73
头都搞大了, 怎么也算不好, 也不知道描述清楚没, 还有可能都出现2个40的情况, 还要随机取一个去分配..能不能搞定啊.
F1 F2 F3 F3最终
A 40 74.28 73
A 20 37.14 38
A 10 18.57 19
-- 能憋出来结果我就开心了。。。看看有人比我复杂吗
create TABLE #tb
(
F1 NVARCHAR(10),
F2 FLOAT,
F3 FLOAT
)
INSERT INTO #tb
SELECT 'A', 40, 0
UNION SELECT 'A', 20, 0
UNION SELECT 'A', 10, 0
UNION SELECT 'B', 20, 0
UNION SELECT 'B', 30, 0
create table #TB2(F1 char(1),MaxF2 int)
insert #TB2 select 'a',200
insert #TB2 select 'B',100
with cte as(
select t.F1,T.F2, t.F2/sm.SumF2*(t2.MaxF2 - sm.SumF2) as q, t2.MaxF2 - sm.SumF2 as P from
#tb t
join
(select F1,SUM(F2) As SumF2 from #tb group by F1) sm on(t.F1 = sm.F1)
join
#TB2 t2 on (t.F1 = t2.F1)
)
,cte2 as (
select xx.F1, f2,
case when SeqNo > 1 then ceiling(q) else 0 end as q1
,q,p,SeqNo
from
(
select F1,f2,Q ,P ,ROW_NUMBER() over (partition by F1 order by q desc) as SeqNo from cte x
) xx
)
select f1,F2,Q,
(case when seqno = 1 then (select P-SUM(q1) from cte2 where F1 = xxx.F1 and seqno >1 group by F1,p)
else q1
end)
from cte2 xxx
--A 40 74.2857142857143 73
--A 20 37.1428571428571 38
--A 10 18.5714285714286 19
--B 30 30 30
--B 20 20 20
--有多个最大值就用临时表
DECLARE @table TABLE
(
F1 NVARCHAR(10),
F2 FLOAT,
F3 FLOAT
)
INSERT INTO @table
SELECT 'A', 40, 0
UNION all SELECT 'A', 40, 0
UNION all SELECT 'A', 10, 0
UNION all SELECT 'B', 20, 0
UNION all SELECT 'B', 30, 0
UNION all SELECT 'C', 20, 0
UNION all SELECT 'C', 30, 0
UNION all SELECT 'C', 10, 0
select identity(int,1,1) as id,* into #table from @table order by F1,F2 desc
select F1,F2,case id when (select min(id) from #table where r.F1=F1) then
(case F1 when 'A' then 200 else 100 end) - (select sum(F2) from #table where r.F1=F1) -
(select sum(F3) from (select id,F1,F2,case id when (select min(id) from #table where t.F1=F1) then 0
else ceiling(((case F1 when 'A' then 200 else 100 end) -
(select sum(F2) from #table where t.F1=F1))*F2/(select sum(F2) from #table where t.F1=F1)) end as [F3]
from #table t) k where k.F1 = R.F1)
else F3 end as [F3]
from (select id,F1,F2,
case id when (select min(id) from #table where t.F1=F1) then 0
else ceiling(((case F1 when 'A' then 200 else 100 end) -
(select sum(F2) from #table where t.F1=F1))*F2/(select sum(F2) from #table where t.F1=F1)) end as [F3]
from #table t) r
drop table #table
--Ken Wong
--测试数据
DECLARE @table TABLE
(
F1 NVARCHAR(10),
F2 FLOAT,
F3 FLOAT
)
INSERT INTO @table
SELECT 'A', 40, 0
UNION SELECT 'A', 20, 0
UNION SELECT 'A', 10, 0
UNION SELECT 'B', 20, 0
UNION SELECT 'B', 30, 0
--查询
select F1,F2,case F2 when (select max(F2) from @table where r.F1=F1) then
(case F1 when 'A' then 200 else 100 end) - (select sum(F2) from @table where r.F1=F1) -
(select sum(F3) from (select F1,F2,
case F2 when (select max(F2) from @table where t.F1=F1) then 0
else ceiling(((case F1 when 'A' then 200 else 100 end) -
(select sum(F2) from @table where t.F1=F1))*F2/(select sum(F2) from @table where t.F1=F1)) end as [F3]
from @table t) k where k.F1 = R.F1 )
else F3 end as [F3]
from (select F1,F2,
case F2 when (select max(F2) from @table where t.F1=F1) then 0
else ceiling(((case F1 when 'A' then 200 else 100 end) -
(select sum(F2) from @table where t.F1=F1))*F2/(select sum(F2) from @table where t.F1=F1)) end as [F3]
from @table t) r
--结果
--------------------------
A 10.0 19.0
A 20.0 38.0
A 40.0 73.0
B 20.0 20.0
B 30.0 30.0
Msg 8120, Level 16, State 1, Line 14
选择列表中的列 '@TB.F3' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。
SELECT A.*,
(SELECT SUM(F2)*F3 FROM TB WHERE F1=A.F1 GROUP BY F1)AS F3最终
FROM TB A