请大侠们帮忙弄下,因为从表数据不准确,想汇总后取主表的平均值,分配下去,可以一步到位吗?谢谢。
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 '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
解决方案 »
- 我是菜鸟,现在晕了
- 多表联合查询问题
- server 2008 + sql 2008 报表
- SQL SERVER会自动对这样的语句进行优化处理么?
- 请教一个SQL语句
- help:我用dts导库,成功后调用导入的数据库中的资料,但是显示却是乱码,数据库重原来是big5信息现在都是乱吗!!
- SqlServer 自定义函数里面不能执行getdate函数?
- 急!急!急!如何全部更改用户表的所有者?(sql server2000)或(sqlserver7)
- 如何做数据库的备份与恢复问题
- xampp#2002无法登录mysql服务器解决办法
- 请问在sql2008R2中,如何快速检查所有的视图是否为可正常运行的?
- 【求助】请问怎样将一张表中的一个字段所有行的值给另一张表的一个字段?详细里面说。。。
(2 行受影响)(8 行受影响)
invno item
-------------------- ------------------------------ -----------
A 001 150
A 002 150
B 001 250
B 002 250(4 行受影响)
(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
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
(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,c.item,a.amount/b.num num_avg,b.num
from @t a inner join (select invno,COUNT(1) num from (select distinct INVNO,Item from @td ) d group by invno ) b
on a.invno = b.invno inner join (select distinct invno,item from @td) c on a.INVNO=c.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