明细表有下面的字段
SKU CountSKU表有下面字段
SKU GoodsCode还有排行表 Ranking有下面字段
GoodsCode Count现在要查询Goods排行怎么查Goods总数量 = GoodsCode(明细表.[Sku] = [Sku表].[Sku])的Count + Ranking的Count
SKU CountSKU表有下面字段
SKU GoodsCode还有排行表 Ranking有下面字段
GoodsCode Count现在要查询Goods排行怎么查Goods总数量 = GoodsCode(明细表.[Sku] = [Sku表].[Sku])的Count + Ranking的Count
B.[COUNT] AS 实际销售数量,
C.[COUNT] AS 额外填写的销售数量,
B.[COUNT]+C.[COUNT] AS 总数量,
ROW_NUMBER() OVER(ORDER BY B.[COUNT]+C.[COUNT] DESC) AS 按总数量排序
FROM SKU AS A
JOIN (SELECT SUM([COUNT]) AS [COUNT],SKU FROM 明细表
GROUP BY SKU) AS B
ON A.SKU = B.SKU
JOIN (SELECT SUM([COUNT]) AS [COUNT],GOODSCODE
FROM 排行表 GROUP BY GOODSCODE) AS C
ON A.GOODSCODE=C.GOODSCODE
DECLARE @TA TABLE(
sku varchar(20),
total int
)
INSERT INTO @TA SELECT '1',1
UNION ALL SELECT '2',2
UNION ALL SELECT '3',3DECLARE @TB TABLE(
sku varchar(20),
goodscode varchar(30)
)
INSERT INTO @TB SELECT '1','A'
UNION ALL SELECT '2','A'
UNION ALL SELECT '3','B'DECLARE @TC TABLE(
goodscode varchar(30),
itotal int
)
INSERT INTO @TC SELECT 'A',20
INSERT INTO @TC SELECT 'B',30搜索出来的结果应该是
GoodsCode 实际数量 额外数量 总量
B 3 30 33
A 3 20 23
b.GoodsCode,a.count,c.count,(a.count+c.count) as 总数量
from
明细表 a,SKU表 b,Ranking c
on
a.SKU=b.SKU
and
b.GoodsCode=c.GoodsCode
select c.GoodsCode,
a.[Count] as 实际销售数量
,c.[count] as 额外填写的销售数量
,(a.[Count]+C.[Count]) as 总数量
from GoodsCode a
,SKU b
,Ranking c
where a.SKU=b.SKU and b.GoodsCode=c.GoodsCode
order by a.[Count]+C.[Count] desc楼主,我蒙对没有?
sku varchar(20),
total int
)
INSERT INTO @TA SELECT '1',1
UNION ALL SELECT '2',2
UNION ALL SELECT '3',3 DECLARE @TB TABLE(
sku varchar(20),
goodscode varchar(30)
)
INSERT INTO @TB SELECT '1','A'
UNION ALL SELECT '2','A'
UNION ALL SELECT '3','B' DECLARE @TC TABLE(
goodscode varchar(30),
itotal int
)
INSERT INTO @TC SELECT 'A',20
INSERT INTO @TC SELECT 'B',30
--select * from @ta
--select * from @tb
--select * from @tcselect
a.GoodsCode,a.[count],c.[itotal],(a.[count]+c.[itotal]) as 总数量
from
(select sum(a.total) as [count],b.goodscode from @ta a,@tb b where a.sku=b.sku group by b.goodscode)a
join
@tc c
on
a.goodscode=c.goodscode
/*
GoodsCode count itotal 总数量
------------------------------ ----------- ----------- -----------
A 3 20 23
B 3 30 33(2 行受影响)/*
sku varchar(20),
total int
)
INSERT INTO @TA SELECT '1',1
UNION ALL SELECT '2',2
UNION ALL SELECT '3',3 DECLARE @TB TABLE(
sku varchar(20),
goodscode varchar(30)
)
INSERT INTO @TB SELECT '1','A'
UNION ALL SELECT '2','A'
UNION ALL SELECT '3','B' DECLARE @TC TABLE(
goodscode varchar(30),
itotal int
)
INSERT INTO @TC SELECT 'A',20
INSERT INTO @TC SELECT 'B',30
--select * from @ta
--select * from @tb
--select * from @tcselect
a.GoodsCode,a.[count],c.[itotal],(a.[count]+c.[itotal]) as 总数量
from
(select sum(a.total) as [count],b.goodscode from @ta a,@tb b where a.sku=b.sku group by b.goodscode)a
join
@tc c
on
a.goodscode=c.goodscode
order by 4 desc
/*GoodsCode count itotal 总数量
------------------------------ ----------- ----------- -----------
B 3 30 33
A 3 20 23(2 行受影响)*/
A.[COUNT] AS 实际销售数量,
C.[COUNT] AS 额外填写的销售数量,
A.[COUNT]+C.[COUNT] AS 总数量,
ROW_NUMBER() OVER(ORDER BY A.[COUNT]+C.[COUNT] DESC) AS 按总数量排序
FROM (
SELECT A.GOODSCODE,SUM(B.[COUNT]) AS [COUNT]
FROM @TB AS A
JOIN (SELECT SUM([COUNT]) AS [COUNT],SKU
FROM @TA GROUP BY SKU) AS B
ON A.SKU = B.SKU
GROUP BY A.GOODSCODE) AS A
JOIN (SELECT SUM([COUNT]) AS [COUNT],GOODSCODE
FROM @TC GROUP BY GOODSCODE) AS C
ON A.GOODSCODE=C.GOODSCODE