有三个表:
入库表:rktable
rkid rkmcid rksl rkdw
1 1 20 个
2 2 50 个
3 3 30 双
4 1 10 个出库表:cktable
ckid ckmcid cksl ckdw
1 1 10 个
2 2 30 个商品表:sptable
spid spmc
1 螺丝
2 铁钉
3 手套现在求一段sql语句来查询剩余库存数量,要求库存数量包括未出库的商品(网上有一些方法不能查询出未出库的商品数量),查询出来的库存:
ckid ckmc rksl cksl kcsl dw
1 螺丝 30 10 20 个
2 铁钉 50 30 20 个
3 手套 30 0 30 双 这段只能实现有商品出库时的库存查询select c.rkmcid as ckid,d.spmc as ckmc,c,rk as rksl,c.ck as cksl,c.kc as kcsl,c.rkdw as dw
from (select a.rkmcid,a.rkdw,a.rk,b.ck,a.rk-b.ck as ck from (select rkmcid,rkdw,sum(rksl)
as rk from rktable group by rkmcid,rkdw) as a inner join (select ckmcid,ckdw,sum(cksl)
as ck from cktable group by ckmcid,ckdw) as b on a.rkmcid=b.ckmcid) as c inner join sptable
as d on c.rkmcid=d.spid order by ckid
入库表:rktable
rkid rkmcid rksl rkdw
1 1 20 个
2 2 50 个
3 3 30 双
4 1 10 个出库表:cktable
ckid ckmcid cksl ckdw
1 1 10 个
2 2 30 个商品表:sptable
spid spmc
1 螺丝
2 铁钉
3 手套现在求一段sql语句来查询剩余库存数量,要求库存数量包括未出库的商品(网上有一些方法不能查询出未出库的商品数量),查询出来的库存:
ckid ckmc rksl cksl kcsl dw
1 螺丝 30 10 20 个
2 铁钉 50 30 20 个
3 手套 30 0 30 双 这段只能实现有商品出库时的库存查询select c.rkmcid as ckid,d.spmc as ckmc,c,rk as rksl,c.ck as cksl,c.kc as kcsl,c.rkdw as dw
from (select a.rkmcid,a.rkdw,a.rk,b.ck,a.rk-b.ck as ck from (select rkmcid,rkdw,sum(rksl)
as rk from rktable group by rkmcid,rkdw) as a inner join (select ckmcid,ckdw,sum(cksl)
as ck from cktable group by ckmcid,ckdw) as b on a.rkmcid=b.ckmcid) as c inner join sptable
as d on c.rkmcid=d.spid order by ckid
解决方案 »
- Sybase 问题
- 帮忙看看怎么这个存储过程有错啊!
- 游标如何传递参数?
- 如何取得表最后10条数据,以及存储过程中使用TOP的问题.
- to 邹健:函数中不允许调用Update ?函数中不允许调用存储过程?那以下功能怎么实现呢?
- 关于分组记录的一个问题,求方法。
- 视图中增加自增长ID?
- select substring(tName,0,charindex('1',tName,1))+cast(right(tName,1)+1 as va
- 通过某一行得知在分页中所在的页码
- *******SQL数据处理,可能用到搜索技巧,头痛,特高分求救: (今次出手300分)*****
- sql左连接 改成其他方式也能好用如何改?
- 在线求解分组统计占比复杂sql语句问题
FROM sptable a LEFT JOIN (SELECT rkmcid,rkdw,sum(rksl) rksl FROM rktable GROUP BY rkmcid,rkdw)b
ON a.spid=b.rkmcid LEFT JOIN (SELECT ckmcid,ckdw,sum(cksl) cksl FROM cktable GROUP BY ckmcid,ckdw) c
ON a.spid=c.ckmcid
,isnull(c.cksl,0) as cksl
,isnull(b.rksl,0)-isnull(c.cksl,0) as kcsl
,isnull(rkdw,ckdw) as dw
from sptable a
left join(select rkmcid,rkdw,sum(rksl) as rksl from rktable group by rkmcid,rkdw) b on a.spid=b.rkmcid
left join(select ckmcid,ckdw,sum(cksl) as cksl from cktable group by rkmcid,rkdw) c on a.spid=c.ckmcid
drop table rktable
Go
Create table rktable([rkid] int,[rkmcid] int,[rksl] int,[rkdw] nvarchar(1))
Insert rktable
select 1,1,20,N'个' union all
select 2,2,50,N'个' union all
select 3,3,30,N'双' union all
select 4,1,10,N'个'
Go
if not object_id('cktable') is null
drop table cktable
Go
Create table cktable([ckid] int,[ckmcid] int,[cksl] int,[ckdw] nvarchar(1))
Insert cktable
select 1,1,10,N'个' union all
select 2,2,30,N'个'
if not object_id('sptable') is null
drop table sptable
Go
Create table sptable([spid] int,[spmc] nvarchar(2))
Insert sptable
select 1,N'螺丝' union all
select 2,N'铁钉' union all
select 3,N'手套'
Go
select c.*,
rksl=isnull((select sum([rksl])from rktable where [rkmcid]=c.[spid]),0),
cksl=isnull((select sum([cksl])from cktable where [ckmcid]=c.[spid]),0),
kcsl=isnull((select sum([rksl])from rktable where [rkmcid]=c.[spid]),0)-
isnull((select sum([cksl])from cktable where [ckmcid]=c.[spid]),0),
dw=(select top 1 [rkdw] from rktable where [rkmcid]=c.[spid])
from sptable c
/*
spid spmc rksl cksl kcsl dw
----------- ---- ----------- ----------- ----------- ----
1 螺丝 30 10 20 个
2 铁钉 50 30 20 个
3 手套 30 0 30 双*/
-- Author : htl258(Tony)
-- Date : 2010-04-01 11:52:19
-- Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
-- Mar 29 2009 10:27:29
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:rktableIF NOT OBJECT_ID('[rktable]') IS NULL
DROP TABLE [rktable]
GO
CREATE TABLE [rktable]([rkid] INT,[rkmcid] INT,[rksl] INT,[rkdw] NVARCHAR(10))
INSERT [rktable]
SELECT 1,1,20,N'个' UNION ALL
SELECT 2,2,50,N'个' UNION ALL
SELECT 3,3,30,N'双' UNION ALL
SELECT 4,1,10,N'个'
GO
--SELECT * FROM [rktable]--> 生成测试数据表:cktableIF NOT OBJECT_ID('[cktable]') IS NULL
DROP TABLE [cktable]
GO
CREATE TABLE [cktable]([ckid] INT,[ckmcid] INT,[cksl] INT,[ckdw] NVARCHAR(10))
INSERT [cktable]
SELECT 1,1,10,N'个' UNION ALL
SELECT 2,2,30,N'个'
GO
--SELECT * FROM [cktable]--> 生成测试数据表:sptableIF NOT OBJECT_ID('[sptable]') IS NULL
DROP TABLE [sptable]
GO
CREATE TABLE [sptable]([spid] INT,[spmc] NVARCHAR(10))
INSERT [sptable]
SELECT 1,N'螺丝' UNION ALL
SELECT 2,N'铁钉' UNION ALL
SELECT 3,N'手套'
GO
--SELECT * FROM [sptable]
/*
ckid ckmc rksl cksl kcsl dw
1 螺丝 30 10 20 个
2 铁钉 50 30 20 个
3 手套 30 0 30 双
*/
-->SQL查询如下:SELECT A.[SPID],A.[SPMC],SUM(B.RKSL) RKSL,B.[RKDW],SUM(ISNULL(C.CKSL,0)) CKSL,C.[CKDW],
ISNULL(SUM(B.RKSL-ISNULL(C.CKSL,0)),0) KCSL
FROM [SPTABLE] A
JOIN [RKTABLE] B ON A.SPID=B.[RKMCID]
LEFT JOIN [CKTABLE] C ON A.SPID=C.[CKMCID]
GROUP BY A.[SPID],A.[SPMC],B.[RKDW],C.[CKDW]
/*
SPID SPMC RKSL RKDW CKSL CKDW KCSL
----------- ---------- ----------- ---------- ----------- ---------- -----------
1 螺丝 30 个 20 个 10
2 铁钉 50 个 30 个 20
3 手套 30 双 0 NULL 30(3 行受影响)
*/
-- Author : htl258(Tony)
-- Date : 2010-04-01 11:52:19
-- Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
-- Mar 29 2009 10:27:29
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:rktableIF NOT OBJECT_ID('[rktable]') IS NULL
DROP TABLE [rktable]
GO
CREATE TABLE [rktable]([rkid] INT,[rkmcid] INT,[rksl] INT,[rkdw] NVARCHAR(10))
INSERT [rktable]
SELECT 1,1,20,N'个' UNION ALL
SELECT 2,2,50,N'个' UNION ALL
SELECT 3,3,30,N'双' UNION ALL
SELECT 4,1,10,N'个'
GO
--SELECT * FROM [rktable]--> 生成测试数据表:cktableIF NOT OBJECT_ID('[cktable]') IS NULL
DROP TABLE [cktable]
GO
CREATE TABLE [cktable]([ckid] INT,[ckmcid] INT,[cksl] INT,[ckdw] NVARCHAR(10))
INSERT [cktable]
SELECT 1,1,10,N'个' UNION ALL
SELECT 2,2,30,N'个'
GO
--SELECT * FROM [cktable]--> 生成测试数据表:sptableIF NOT OBJECT_ID('[sptable]') IS NULL
DROP TABLE [sptable]
GO
CREATE TABLE [sptable]([spid] INT,[spmc] NVARCHAR(10))
INSERT [sptable]
SELECT 1,N'螺丝' UNION ALL
SELECT 2,N'铁钉' UNION ALL
SELECT 3,N'手套'
GO
--SELECT * FROM [sptable]
/*
ckid ckmc rksl cksl kcsl dw
1 螺丝 30 10 20 个
2 铁钉 50 30 20 个
3 手套 30 0 30 双
*/
-->SQL查询如下:SELECT A.[SPID],A.[SPMC],SUM(B.RKSL) RKSL,SUM(ISNULL(C.CKSL,0)) CKSL,
ISNULL(SUM(B.RKSL-ISNULL(C.CKSL,0)),0) KCSL,B.[RKDW]
FROM [SPTABLE] A
JOIN [RKTABLE] B ON A.SPID=B.[RKMCID]
LEFT JOIN [CKTABLE] C ON A.SPID=C.[CKMCID]
GROUP BY A.[SPID],A.[SPMC],B.[RKDW],C.[CKDW]
/*
SPID SPMC RKSL CKSL KCSL RKDW
----------- ---------- ----------- ----------- ----------- ----------
1 螺丝 30 20 10 个
2 铁钉 50 30 20 个
3 手套 30 0 30 双(3 行受影响)
*/