1、有一个物料所需表A:
A1(物料) A2(规格1) A3(规格2) A4(数量)
----------------------------------------------------
s001 825 890 1
s002 799 1050 2
s003 1010 950 2
s004 859 900 2
s005 859 1060 3
2、标准库存表B
B1 B2 B3
-----------------------
t001 900 900
t002 1000 1100
t003 900 1000
3、现在想汇总成标准B的规格，表A规格不满整数的取其离规格最近的,两个规格参数B2和B3都不能比A2和A3小，但是A2和A3或者B2和B3可以颠倒过来比对是可以的，比如：
s001(825*890) --> 取t001(900*900)
s002(799*050) --> 取t002(1000*1100)
s003(1010*950) --> 取t002(1000*1100)
s004(859*900) --> 取t001(900*900)
s005(859*1060) --> 取t003(900*1000)
4、最后的汇总效果如下：
规格 合计
---------------
t001 3 注释分别是s001(A4 x 1)+s004(A4 x 2)=3
t002 2 同上
t003 1
请问一下大家怎样来实现上述这种汇总？不知道大家看明白了没有。如有不明白处我再次补充说明一下。
汇总 SQL 找最近
------解决方案--------------------
case when语句 .
游标.
------解决方案--------------------
WITH cte1 (a1,a2,a3,a4) AS
(
SELECT 's001',825,890,1 UNION ALL
SELECT 's002',799,1050,2 UNION ALL
SELECT 's003',1010,950,2 UNION ALL
SELECT 's004',859,900,2 UNION ALL
SELECT 's005',859,1060,3
)
,cte2 (b1,b2,b3) AS
(
SELECT 't001',900,900 UNION ALL
SELECT 't002',1000,1100 UNION ALL
SELECT 't003',900,1000
)
,cte3 AS
(
SELECT *
FROM cte1 a
OUTER APPLY
(
SELECT TOP 1 * FROM cte2
WHERE (b2>=a.a2 AND b3>=a.a3) OR (b2>=a.a3 AND b3>=a.a2)
ORDER BY b2*b3
) b
)
SELECT b1,SUM(a4) a4
FROM cte3
GROUP BY b1
------解决方案--------------------
WITH cte1 (a1,a2,a3,a4) AS
(
SELECT 's001',825,890,1 UNION ALL
SELECT 's002',799,1050,2 UNION ALL
SELECT 's003',1010,950,2 UNION ALL
SELECT 's004',859,900,2 UNION ALL
SELECT 's005',859,1060,3
)
,cte2 (b1,b2,b3) AS
(
SELECT 't001',900,900 UNION ALL
SELECT 't002',1000,1100 UNION ALL
SELECT 't003',900,1000
),
cte3 as
(
select a.*,b2=(select top 1 b1 from cte2 b
where (b.b2>=a.a2 and b.b3>=a.a3) or (b.b3>=a.a2 and b.b2>=a.a3)
order by abs(b.b3-a.a2)+abs(b.b2-a.a3)) -->就看你这里的排序规则是什么？
from cte1 a
)
select * from cte3
------解决方案--------------------
呵呵，大概写了一下，不知道对不对：
create table a (a1 varchar(100),a2 int,a3 int,a4 int)
insert into a
SELECT 's001',825 ,890 ,1 UNION ALL
SELECT 's002',799 ,1050,2 UNION ALL
SELECT 's003',1010,950 ,2 UNION ALL
SELECT 's004',859 ,900 ,2 UNION ALL
SELECT 's005',859 ,1060,3
create table b (B1 varchar(100),B2 int,B3 int)
insert into b
SELECT 't001',900 ,900 UNION ALL
SELECT 't002',1000,1100 UNION ALL
SELECT 't003',900 ,1000
;with tt
as
(
select a.*,
b.*,
row_number() over(partition by a.a1