34,575
社区成员
发帖
与我相关
我的任务
分享
DECLARE @t TABLE
(INVNO NVARCHAR(20),Amount DECIMAL(18,2))
INSERT INTO @t
SELECT 'A',300 UNION ALL
SELECT 'B',500
DECLARE @td TABLE
(INVNO NVARCHAR(20),Item NVARCHAR(30),Amount DECIMAL(18,2))
INSERT INTO @td
SELECT 'A','001',50 UNION ALL
SELECT 'A','001',180 UNION ALL
SELECT 'A','002',50 UNION ALL
SELECT 'A','002',100 UNION ALL
SELECT 'B','001',200 UNION ALL
SELECT 'B','001',50 UNION ALL
SELECT 'B','002',100 UNION ALL
SELECT 'B','002',100
'A','001',150
'A','002',150
'B','001',250
'B','002',250
--我就怕这样弄会严重影响速度,我一直在等待,版主给点意见吧。
DECLARE @t TABLE
(INVNO NVARCHAR(20),Amount DECIMAL(18,2))
INSERT INTO @t
SELECT 'A',300 UNION ALL
SELECT 'B',500
DECLARE @td TABLE
(INVNO NVARCHAR(20),Item NVARCHAR(30),Amount DECIMAL(18,2))
INSERT INTO @td
SELECT 'A','001',50 UNION ALL
SELECT 'A','001',180 UNION ALL
SELECT 'A','002',50 UNION ALL
SELECT 'A','002',100 UNION ALL
SELECT 'A','003',20 UNION ALL
SELECT 'B','001',200 UNION ALL
SELECT 'B','001',50 UNION ALL
SELECT 'B','002',100 UNION ALL
SELECT 'B','002',100
select b.invno,b.item,a.amount/b.num,b.num
from @t a ,(select invno,item,num = count(1) from @td group by invno,item ) b
where a.invno = b.invno
有问题
我期望的是这样的。
A 001 100.0000000000000 3
A 002 100.0000000000000 3
A 003 100.0000000000000 3
B 001 250.0000000000000 2
B 002 250.0000000000000 2
--哥哥 代码能够看下吗?
DECLARE @t TABLE
(INVNO NVARCHAR(20),Amount int)
INSERT INTO @t
SELECT 'A',300 UNION ALL
SELECT 'B',500
DECLARE @td TABLE
(INVNO NVARCHAR(20),Item NVARCHAR(30),Amount int)
INSERT INTO @td
SELECT 'A','001',50 UNION ALL
SELECT 'A','001',180 UNION ALL
SELECT 'A','002',50 UNION ALL
SELECT 'A','002',100 UNION ALL
SELECT 'B','001',200 UNION ALL
SELECT 'B','001',50 UNION ALL
SELECT 'B','002',100 UNION ALL
SELECT 'B','002',100
select b.invno,b.item,a.amount/b.num
from @t a ,
(
select invno,item,num = count(1) from @td
group by invno,item
) b
where a.invno = b.invno
(2 行受影响)
(8 行受影响)
invno item
-------------------- ------------------------------ -----------
A 001 150
A 002 150
B 001 250
B 002 250
(4 行受影响)