index stockcode sn batchid qty status
1 A111 1001 2011001 10 0
2 A111 1001 2011001 20 0
3 A112 1001 2011002 20 0
4 A113 1001 2011003 10 0
5 A111 1002 2011001 20 0
6 A999 1002 2011111 100 0-->
stockcode sn bathid qty
A111 1001 2011001 30
A112 1001 2011002 20
A113 1001 2011003 10
A999 1002 2011111 100相同料号,相同批次 数量合并,且不同sn的只出现(合并)一个sn。写了一个,(left join&inner join)都没法实现,求助~!
select c.stockcode,c.sn,c.batchid,c.qty
from (select stoccode,sn,batchid,sum(qty) from tb group by stockcode,sn,batchid) c
left join (select stoccode,sn,batchid,sum(qty) from tb group by stockcode,sn,batchid) d
on c.stockcode=d.stockcode and c.batchid=d.batchid and c.dt_sn>d.dt_sn
1 A111 1001 2011001 10 0
2 A111 1001 2011001 20 0
3 A112 1001 2011002 20 0
4 A113 1001 2011003 10 0
5 A111 1002 2011001 20 0
6 A999 1002 2011111 100 0-->
stockcode sn bathid qty
A111 1001 2011001 30
A112 1001 2011002 20
A113 1001 2011003 10
A999 1002 2011111 100相同料号,相同批次 数量合并,且不同sn的只出现(合并)一个sn。写了一个,(left join&inner join)都没法实现,求助~!
select c.stockcode,c.sn,c.batchid,c.qty
from (select stoccode,sn,batchid,sum(qty) from tb group by stockcode,sn,batchid) c
left join (select stoccode,sn,batchid,sum(qty) from tb group by stockcode,sn,batchid) d
on c.stockcode=d.stockcode and c.batchid=d.batchid and c.dt_sn>d.dt_sn
from tb
group by stockcode,batchid
insert into tb select 1,'A111','1001','2011001',10,0
insert into tb select 2,'A111','1001','2011001',20,0
insert into tb select 3,'A112','1001','2011002',20,0
insert into tb select 4,'A113','1001','2011003',10,0
insert into tb select 5,'A111','1002','2011001',20,0
insert into tb select 6,'A999','1002','2011111',100,0
go
select stockcode,min(sn)sn,batchid,sum(qty)qty
from tb
group by stockcode,batchid
/*
stockcode sn batchid qty
---------- ---------- ---------- -----------
A111 1001 2011001 50
A112 1001 2011002 20
A113 1001 2011003 10
A999 1002 2011111 100(4 行受影响)*/
go
drop table tb
@fredrickhu不对哦
sum(qty)把 sn 1002的也计算进来了
关 sn 啥事!
create table tb([index] int,stockcode varchar(10),sn varchar(10),batchid varchar(10),qty int,status int)
insert into tb select 1,'A111','1001','2011001',10,0
insert into tb select 2,'A111','1001','2011001',20,0
insert into tb select 3,'A112','1001','2011002',20,0
insert into tb select 4,'A113','1001','2011003',10,0
insert into tb select 5,'A111','1002','2011001',20,0
insert into tb select 6,'A999','1002','2011111',100,0
go
select stockcode,min(sn)sn,batchid,sum(qty)qty
from(
select * from tb a where not exists(select 1 from tb where stockcode=a.stockcode and batchid=a.batchid and sn<a.sn)
)t
group by stockcode,batchid
/*
stockcode sn batchid qty
---------- ---------- ---------- -----------
A111 1001 2011001 30
A112 1001 2011002 20
A113 1001 2011003 10
A999 1002 2011111 100(4 行受影响)*/
go
drop table tb
(
select * from tb t where [index]=(select max([index]) from tb where stockcode=t.stockcode and batchid=t.batchid
)select stockcode,min(sn) as sn,batchid,sum(qty) as qty from f group by stockcode
sn(领料单号码)是一个标记,写进erp里,要标记出来。
两个sn的,所以要标记两次..但是erp系统扣帐的时候 同一个料号,批次,只能出现一次。所以
相同料号,相同批次,相同sn,数量合并
且相同料号,相同批次,不同sn的信息排除掉,等第二次查询
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-12-15 13:18:11
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([index] int,[stockcode] varchar(4),[sn] int,[batchid] int,[qty] int,[status] int)
insert [tb]
select 1,'A111',1001,2011001,10,0 union all
select 2,'A111',1001,2011001,20,0 union all
select 3,'A112',1001,2011002,20,0 union all
select 4,'A113',1001,2011003,10,0 union all
select 5,'A111',1002,2011001,20,0 union all
select 6,'A999',1002,2011111,100,0
--------------开始查询--------------------------
;with f as
(
select * from tb t where [sn]=(select min([sn]) from tb where stockcode=t.stockcode and batchid=t.batchid)
)
select stockcode,min(sn) as sn,batchid,sum(qty) as qty from f group by stockcode,batchid
----------------结果----------------------------
/* stockcode sn batchid qty
--------- ----------- ----------- -----------
A111 1001 2011001 30
A112 1001 2011002 20
A113 1001 2011003 10
A999 1002 2011111 100(4 行受影响)*/
if object_id('tb') is not null
drop table tb
go
create table tb
(
[index] int identity(1,1),
stockcode varchar(10),
sn varchar(10),
batchid varchar(10),
qty int,
status int
)
go
insert into tb(stockcode,sn,batchid,qty,status)
select 'A111','1001','2011001',10,0 union all
select 'A111','1001','2011001',20,0 union all
select 'A112','1001','2011002',20,0 union all
select 'A113','1001','2011003',10,0 union all
select 'A111','1002','2011001',20,0 union all
select 'A999','1002','2011001',100,0
go
select stockcode,sn,batchid,qty=sum(qty) from tb group by stockcode,sn,batchid
go
/*
stockcode sn batchid qty
---------- ---------- ---------- -----------
A111 1001 2011001 30
A111 1002 2011001 20
A112 1001 2011002 20
A113 1001 2011003 10
A999 1002 2011001 100(5 行受影响)
*/
fredrickhu(小F)
pengxuan(约定蓝天)
谢谢~~OK了。