几个表
1货品档案mmf 字段matid,name,pice 为货品编号,名称,单价
111 衣服 1000
222 衣服 800
333 裤子 500
2库存stock 字段matid,locateid,qty 为货品编号,库存门店编号,库存数
111 101 1
111 102 2
222 101 3
3销售主表matout 字段matioid,filldate,totalprice 为销售单号,销售日期,金额
1 2010-03-11 20000
4销售明细表matoutlist 字段matioid,locateid,matid,qty 为销售单号,门店,货品编号,销售数
1 111 101 1
1 222 101 1对matid汇总库存及销售数及计算出销率
对name汇总库存及销售数及动销率
1货品档案mmf 字段matid,name,pice 为货品编号,名称,单价
111 衣服 1000
222 衣服 800
333 裤子 500
2库存stock 字段matid,locateid,qty 为货品编号,库存门店编号,库存数
111 101 1
111 102 2
222 101 3
3销售主表matout 字段matioid,filldate,totalprice 为销售单号,销售日期,金额
1 2010-03-11 20000
4销售明细表matoutlist 字段matioid,locateid,matid,qty 为销售单号,门店,货品编号,销售数
1 111 101 1
1 222 101 1对matid汇总库存及销售数及计算出销率
对name汇总库存及销售数及动销率
left join matout c on b.matid=c.matid
group by b.mtid
-- 未测试,试一试
b.matid,
sum(b.qty) as 汇总库存,
sum(d.销售数) as 销售数,
ltrim(sum(d.销售数)*100/sum(b.qty))+'%' as 出销率
from
a,b,c,d
where
a.matid=b.matid
and
b.matid=d.matid
and
c.matioid=d.matioid
gorup by
b.matid
ltrim(sum(d.销售数)*100/sum(b.qty))+'%' as 出销率
from a,b,c,d
where a.matid=b.matid and b.matid=d.matid and c.matioid=d.matioid
gorup by b.matid
IF OBJECT_ID(N'MMF') IS NOT NULL
DROP TABLE MMF
GOCREATE TABLE MMF
(
MATID INT,
NAME NVARCHAR(20),
PRICE MONEY
)
GOINSERT INTO MMF
SELECT 111,'衣服',1000
UNION ALL
SELECT 222,'衣服',800
UNION ALL
SELECT 333,'裤子',500
GOIF OBJECT_ID(N'STOCK') IS NOT NULL
DROP TABLE STOCK
GOCREATE TABLE STOCK
(
MATID INT,
LOCATEID INT,
QTY INT
)
GOINSERT INTO STOCK
SELECT 111,101,1
UNION ALL
SELECT 111,102,2
UNION ALL
SELECT 222,101,3
GOIF OBJECT_ID(N'MATOUT') IS NOT NULL
DROP TABLE MATOUT
GOCREATE TABLE MATOUT
(
MATIOID INT,
FILLDATE DATETIME,
TOTALPRICE MONEY
)
GOINSERT INTO MATOUT
SELECT 1,'2010-03-11',20000
GOIF OBJECT_ID(N'MATOUTLIST') IS NOT NULL
DROP TABLE MATOUTLIST
GOCREATE TABLE MATOUTLIST
(
MATIOID INT,
MATID INT,
LOCATEID INT,
QTY INT
)
GOINSERT INTO MATOUTLIST
SELECT 1,111,101,1
UNION ALL
SELECT 1,222,101,1
GO---------------------------------------------------------
;WITH CTE1 AS
(
SELECT MATID,SUM(QTY) AS STOCKQTY FROM STOCK GROUP BY MATID
),CTE2 AS
(
SELECT MATID,SUM(QTY) AS OUTQTY FROM MATOUTLIST GROUP BY MATID
)
SELECT T1.MATID,T1.STOCKQTY,T2.OUTQTY,
CASE T1.STOCKQTY WHEN 0 THEN 0
ELSE CAST(ROUND(CAST(T2.OUTQTY AS DECIMAL(10,2))/CAST(T1.STOCKQTY AS DECIMAL(10,2)),2) AS DECIMAL(10,2)) END AS XIAOLV
FROM CTE1 T1
INNER JOIN CTE2 T2
ON T1.MATID = T2.MATIDSELECT T1.NAME,SUM(ISNULL(T3.QTY,0)) AS STOCKQTY,SUM(ISNULL(T2.QTY,0)) AS OUTQTY,
CASE SUM(ISNULL(T3.QTY,0)) WHEN 0 THEN 0
ELSE CONVERT(NUMERIC(11,2),CAST(SUM(ISNULL(T2.QTY,0)) AS FLOAT)/CAST(SUM(ISNULL(T3.QTY,0)) AS FLOAT)) END AS XIAOLV
FROM MMF T1
LEFT JOIN MATOUTLIST T2
ON T1.MATID = T2.MATID
LEFT JOIN STOCK T3
ON T1.MATID = T3.MATID
GROUP BY T1.NAME---------------------------------------------------------------------
MATID STOCKQTY OUTQTY XIAOLV
----------- ----------- ----------- ---------------------------------------
111 3 1 0.33
222 3 1 0.33(2 行受影响)NAME STOCKQTY OUTQTY XIAOLV
-------------------- ----------- ----------- ---------------------------------------
裤子 0 0 0.00
衣服 6 3 0.50(2 行受影响)
create table #mmf
(matid nvarchar(10),
[name] nvarchar(10),
pice int) --为货品编号,名称,单价
insert #mmf
select 111, '衣服', 1000 union all
select 222 , '衣服', 800 union all
select 333 , '裤子', 500create table #stock
(matid nvarchar(10),
locateid nvarchar(10) ,
qty int)--为货品编号,库存门店编号,库存数
insert #stock
select '111', '101', 1 union all
select '111', '102', 2 union all
select '222', '101', 3create table #matout
(matioid nvarchar(10),
filldate datetime,
totalprice int) --为销售单号,销售日期,金额
insert #matout select '1', '2010-03-11', 20000create table #matoutlist
(matioid nvarchar(10),
locateid nvarchar(10),
matid nvarchar(10),
qty int)-- 为销售单号,门店,货品编号,销售数
insert #matoutlist
select '1', '111', '111', 1 union all
select '1', '222', '222', 1begin
with T as (select m.matid as id,(select sum(qty) from #stock as s where s.matid=m.matid) as 总库存,
(select sum(qty) from #matoutlist as l where l.matid=m.matid)as 销售数 from #mmf as m)
select T.*,cast(销售数 as float)/cast(总库存 as float) as 出销率 from T
endbegin
with T as (select m.[name] as [name],(select sum(qty) from #stock as s where s.matid=m.matid) as 总库存,
(select sum(qty) from #matoutlist as l where l.matid=m.matid)as 销售数 from #mmf as m)
select T.[name],sum(T.总库存)as 总库存,sum(T.销售数) as 销售数,
cast(sum(T.总库存) as float)/cast(sum(T.销售数)as float)as 出销率
from T group by [name]
end
如果有多表,表之间如何关联?
发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
SELECT T1.MATID,SUM(T1.QTY) AS STOCKQTY,SUM(T2.QTY) AS OUTQTY,
CASE SUM(T1.QTY) WHEN 0 THEN 0
ELSE CAST(ROUND(CAST(SUM(T2.QTY) AS DECIMAL(10,2))/CAST(SUM(T1.QTY) AS DECIMAL(10,2)),2) AS DECIMAL(10,2)) END AS XIAOLV
FROM (SELECT MATID,SUM(QTY) AS QTY FROM STOCK GROUP BY MATID)T1
LEFT JOIN MATOUTLIST T2
ON T1.MATID = T2.MATID
GROUP BY T1.MATIDSELECT T1.NAME,SUM(ISNULL(T3.QTY,0)) AS STOCKQTY,SUM(ISNULL(T2.QTY,0)) AS OUTQTY,
CASE SUM(ISNULL(T3.QTY,0)) WHEN 0 THEN 0
ELSE CONVERT(NUMERIC(11,2),CAST(SUM(ISNULL(T2.QTY,0)) AS FLOAT)/CAST(SUM(ISNULL(T3.QTY,0)) AS FLOAT)) END AS XIAOLV
FROM MMF T1
LEFT JOIN MATOUTLIST T2
ON T1.MATID = T2.MATID
LEFT JOIN STOCK T3
ON T1.MATID = T3.MATID
GROUP BY T1.NAME
-------------------- ----------- ----------- ---------------------------------------
裤子 0 0 0.00
衣服 6 3 0.50按name汇总时 衣服的outqty应该是2件才对,重复统计了
SELECT T3.NAME,T4.STOCKQTY,T3.OUTQTY,
CASE ISNULL(T4.STOCKQTY,0) WHEN 0 THEN 0
ELSE CONVERT(NUMERIC(11,2),CAST(ISNULL(T3.OUTQTY,0) AS FLOAT)/CAST(ISNULL(T4.STOCKQTY,0) AS FLOAT)) END AS XIAOLV
FROM
(
SELECT T1.NAME,SUM(ISNULL(T2.QTY,0)) AS OUTQTY
FROM MMF T1
LEFT JOIN MATOUTLIST T2
ON T1.MATID = T2.MATID
GROUP BY T1.NAME
)T3
INNER JOIN
(
SELECT T1.NAME,SUM(ISNULL(T2.QTY,0)) AS STOCKQTY
FROM MMF T1
LEFT JOIN STOCK T2
ON T1.MATID = T2.MATID
GROUP BY T1.NAME
)
T4
ON T3.NAME = T4.NAME
(select sum(m.qty) from matoutlist as m where m.matid=s.matid and s.locateid =m.locateid) as 销售,
s.qty as 库存,
s.locateidfrom matoutlist as M,stock as s,mmfwhere s.matid=mmf.matid and s.locateid =m.locateid and s.locateid='102'group by s.locateid,s.qty,s.matid
order by s.locateid针对门店102的库存和销售是对的,
但是把一开始的s.matid(库存表中有多行数据有相同的matid,不同的门店对应)换成mmf.matid(货品档案中的matid是唯一的)其他的s.matid也换成mmf.matid结果就不对,不解
CASE ISNULL((T4.STOCKQTY+T3.outqty),0) WHEN 0 THEN null
ELSE CONVERT(NUMERIC(11,2),CAST(ISNULL(T3.OUTQTY,0) AS FLOAT)/CAST(ISNULL((T4.STOCKQTY+T3.outqty),0) AS FLOAT)) END AS XIAOLV
FROM
(
SELECT T1.NAME,SUM(ISNULL(T2.QTY,0)) AS OUTQTY
FROM MMF T1
LEFT JOIN MATOUTLIST T2
ON T1.MATID = T2.MATID and t2.matioid like 'a%'
GROUP BY T1.NAME
)T3
INNER JOIN
(
SELECT T1.NAME,SUM(ISNULL(T2.QTY,0)) AS STOCKQTY
FROM MMF T1
LEFT JOIN STOCK T2
ON T1.MATID = T2.MATID
GROUP BY T1.NAME
)
T4
ON T3.NAME = T4.NAME 多谢