订单号 时间 数量 产品码 部门代号 星期 批次号
1 2008-11-19 00:00:00.000 160 BC0KR003V 0104 星期三 1_BC0KR003V_2008-11-19
1 2008-11-19 00:00:00.000 65 BC0KS003V 0104 星期三 1_BC0KS003V_2008-11-19
1 2008-11-19 00:00:00.000 230 BC0KS003V 0104 星期三 1_BC0KS003V_2008-11-19
1 2008-11-19 00:00:00.000 26 BC0MQ003V 0104 星期三 1_BC0MQ003V_2008-11-19
1 2008-11-19 00:00:00.000 50 BC0MQ003V 0104 星期三 1_BC0MQ003V_2008-11-19
1 2008-11-19 00:00:00.000 113 BC0MQ003V 0104 星期三 1_BC0MQ003V_2008-11-19
1 2008-11-19 00:00:00.000 264 BC0MR003V 0104 星期三 1_BC0MR003V_2008-11-19
1 2008-11-19 00:00:00.000 5 BK001203V 0104 星期三 1_BK001203V_2008-11-19
1 2008-11-19 00:00:00.000 9 BK001203V 0104 星期三 1_BK001203V_2008-11-19
1 2008-11-19 00:00:00.000 12 BK001203V 0104 星期三 1_BK001203V_2008-11-19
1 2008-11-19 00:00:00.000 57 BK001203V 0104 星期三 1_BK001203V_2008-11-19
1 2008-11-19 00:00:00.000 10 BK002001T 0104 星期三 1_BK002001T_2008-11-19
1 2008-11-19 00:00:00.000 40 BK002203V 0104 星期三 1_BK002203V_2008-11-19
1 2008-11-19 00:00:00.000 62 BK002203V 0104 星期三 1_BK002203V_2008-11-19
1 2008-11-19 00:00:00.000 63 BK002203V 0104 星期三 1_BK002203V_2008-11-19
1 2008-11-19 00:00:00.000 3 BK009401T 0104 星期三 1_BK009401T_2008-11-19如上如表所示,表中有如此的数据,由于数量不同,导致表中同一个批次号重复出现。我现在想做的是把批次号重复的数量的】加起来,然后只保留一个不重复的批次号,请教各位大侠该怎么写?
1 2008-11-19 00:00:00.000 160 BC0KR003V 0104 星期三 1_BC0KR003V_2008-11-19
1 2008-11-19 00:00:00.000 65 BC0KS003V 0104 星期三 1_BC0KS003V_2008-11-19
1 2008-11-19 00:00:00.000 230 BC0KS003V 0104 星期三 1_BC0KS003V_2008-11-19
1 2008-11-19 00:00:00.000 26 BC0MQ003V 0104 星期三 1_BC0MQ003V_2008-11-19
1 2008-11-19 00:00:00.000 50 BC0MQ003V 0104 星期三 1_BC0MQ003V_2008-11-19
1 2008-11-19 00:00:00.000 113 BC0MQ003V 0104 星期三 1_BC0MQ003V_2008-11-19
1 2008-11-19 00:00:00.000 264 BC0MR003V 0104 星期三 1_BC0MR003V_2008-11-19
1 2008-11-19 00:00:00.000 5 BK001203V 0104 星期三 1_BK001203V_2008-11-19
1 2008-11-19 00:00:00.000 9 BK001203V 0104 星期三 1_BK001203V_2008-11-19
1 2008-11-19 00:00:00.000 12 BK001203V 0104 星期三 1_BK001203V_2008-11-19
1 2008-11-19 00:00:00.000 57 BK001203V 0104 星期三 1_BK001203V_2008-11-19
1 2008-11-19 00:00:00.000 10 BK002001T 0104 星期三 1_BK002001T_2008-11-19
1 2008-11-19 00:00:00.000 40 BK002203V 0104 星期三 1_BK002203V_2008-11-19
1 2008-11-19 00:00:00.000 62 BK002203V 0104 星期三 1_BK002203V_2008-11-19
1 2008-11-19 00:00:00.000 63 BK002203V 0104 星期三 1_BK002203V_2008-11-19
1 2008-11-19 00:00:00.000 3 BK009401T 0104 星期三 1_BK009401T_2008-11-19如上如表所示,表中有如此的数据,由于数量不同,导致表中同一个批次号重复出现。我现在想做的是把批次号重复的数量的】加起来,然后只保留一个不重复的批次号,请教各位大侠该怎么写?
1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) 2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录
delete from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1) 3、查找表中多余的重复记录(多个字段)
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) 4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1) 5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1) 比方说在A表中存在一个字段“name”,而且不同记录之间的“name”值有可能会相同,
现在就是需要查询出在该表中的各记录之间,“name”值存在重复的项;
Select Name,Count(*) From A Group By Name Having Count(*) > 1 如果还查性别也相同大则如下:
Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1
select 订单号,时间,sum(数量),产品码,部门代号,星期,批次号 into # from tb group by 订单号,时间,产品码,部门代号,星期,批次号
go
truncate table tb
insert tb select * from #
group by 产品码
可以得到你想要的结果
inner join
(select 批次号 ,sum(数量) sm from tb group by 批次号) b
on a.批次号 = b.批次号delete from tb a where exists(select 1 from tb where a.批次号=批次号 and 对比列>a.对比列)
--对比列,指的批次重复时的保留条件。比如批次同的保留日期最大的,那么日期列就是保留条件
insert into #a select 1 , '2008-11-19 00:00:00.000', 160, 'BC0KR003V', '0104', '星期三', '1_BC0KR003V_2008-11-19'
insert into #a select 1 , '2008-11-19 00:00:00.000' ,65 , 'BC0KS003V', '0104' , '星期三' , '1_BC0KS003V_2008-11-19'
insert into #a select 1 , '2008-11-19 00:00:00.000' , 230 , 'BC0KS003V' , '0104' , '星期三' , '1_BC0KS003V_2008-11-19'
insert into #a select 1 , '2008-11-19 00:00:00.000' , 26 , 'BC0MQ003V', '0104' , '星期三' , '1_BC0MQ003V_2008-11-19'
insert into #a select 1 , '2008-11-19 00:00:00.000' , 50 ,'C0MQ003V' ,'0104' , '星期三' , '1_BC0MQ003V_2008-11-19'
insert into #a select 1 , '2008-11-19 00:00:00.000' , 113, 'BC0MQ003V' , '0104' , '星期三', '1_BC0MQ003V_2008-11-19'
insert into #a select 1 , '2008-11-19 00:00:00.000' , 264, 'BC0MR003V' , '0104', '星期三', '1_BC0MR003V_2008-11-19'
insert into #a select 1 , '2008-11-19 00:00:00.000' , 5 , 'BK001203V' , '0104' , '星期三' , '1_BK001203V_2008-11-19'
insert into #a select 1 , '2008-11-19 00:00:00.000' , 9 , 'BK001203V' , '0104' , '星期三', '1_BK001203V_2008-11-19'
insert into #a select 1 , '2008-11-19 00:00:00.000' , 12, 'BK001203V' , '0104' , '星期三' ,'1_BK001203V_2008-11-19'
insert into #a select 1 , '2008-11-19 00:00:00.000' , 57 , 'BK001203V' ,'0104' , '星期三' , '1_BK001203V_2008-11-19'
insert into #a select 1 , '2008-11-19 00:00:00.000' , 10, 'BK002001T' , '0104' , '星期三', '1_BK002001T_2008-11-19'
insert into #a select 1 , '2008-11-19 00:00:00.000' , 40 , 'BK002203V' , '0104' , '星期三' , '1_BK002203V_2008-11-19'
insert into #a select 1 , '2008-11-19 00:00:00.000' , 62 , 'BK002203V' , '0104' , '星期三' , '1_BK002203V_2008-11-19'
insert into #a select 1 , '2008-11-19 00:00:00.000' , 63, 'BK002203V' , '0104' , '星期三' , '1_BK002203V_2008-11-19'
insert into #a select 1 , ' 2008-11-19 00:00:00.000', 3 , 'BK009401T' , '0104', '星期三', '1_BK009401T_2008-11-19'
select * into #b from
(select id,s_date,sum(qty)qty,s_no,dept_code,s_week,p_no
from #a
group by id,s_date,s_no,dept_code,s_week,p_no)a
delete from #a
insert into #a select * from #bselect * from #a
drop table #a,#b
/**
1 2008-11-19 00:00:00.000 160 BC0KR003V 0104 星期三 1_BC0KR003V_2008-11-19
1 2008-11-19 00:00:00.000 295 BC0KS003V 0104 星期三 1_BC0KS003V_2008-11-19
1 2008-11-19 00:00:00.000 139 BC0MQ003V 0104 星期三 1_BC0MQ003V_2008-11-19
1 2008-11-19 00:00:00.000 264 BC0MR003V 0104 星期三 1_BC0MR003V_2008-11-19
1 2008-11-19 00:00:00.000 83 BK001203V 0104 星期三 1_BK001203V_2008-11-19
1 2008-11-19 00:00:00.000 10 BK002001T 0104 星期三 1_BK002001T_2008-11-19
1 2008-11-19 00:00:00.000 165 BK002203V 0104 星期三 1_BK002203V_2008-11-19
1 2008-11-19 00:00:00.000 3 BK009401T 0104 星期三 1_BK009401T_2008-11-19
1 2008-11-19 00:00:00.000 50 C0MQ003V 0104 星期三 1_BC0MQ003V_2008-11-19