表A:
时间 产品名 数量 价格 贵宾编号
2010-07-14 00:00:00 CKJ 1.0 308.69999999999999 031010001280
2010-07-14 00:00:00 CKJ 1.0 245.69999999999999 031010001280
2010-06-26 00:00:00 CKJ 1.0 351.0 031010001280
2010-06-26 00:00:00 CKJ -1.0 -351.0 031010001280
2010-06-26 00:00:00 CKJ 1.0 351.0 031010001280
2010-06-26 00:00:00 CKJ 1.0 283.5 031010001280
2010-05-21 00:00:00 CKJ 1.0 1071.0 031010001280
2010-05-21 00:00:00 CKJ 1.0 531.0 031010001280
表B:
时间 产品名 数量 价格 贵宾编号
09-23-2010 00:00:00 CKJ 2.0 6732.0 031010001280
07-14-2010 00:00:00 CKJ 2.0 554.39999999999998 031010001280
06-26-2010 00:00:00 CKJ 2.0 634.5 031010001280
05-21-2010 00:00:00 CKJ 2.0 1602.0 031010001280
问题
比如2010-07-14 00:00:00这个时间买了2次产品,但是表B的把这2次加在一起了,我如何删除表B里面2010-07-14 00:00:00这一天的记录,还有2010-06-26 00:00:00也是,就是只要表B里面是把表A里面同一天的数据加起来的话就把表B里面的数据删除,高手指导下看有什么办法,谢谢~~~~
时间 产品名 数量 价格 贵宾编号
2010-07-14 00:00:00 CKJ 1.0 308.69999999999999 031010001280
2010-07-14 00:00:00 CKJ 1.0 245.69999999999999 031010001280
2010-06-26 00:00:00 CKJ 1.0 351.0 031010001280
2010-06-26 00:00:00 CKJ -1.0 -351.0 031010001280
2010-06-26 00:00:00 CKJ 1.0 351.0 031010001280
2010-06-26 00:00:00 CKJ 1.0 283.5 031010001280
2010-05-21 00:00:00 CKJ 1.0 1071.0 031010001280
2010-05-21 00:00:00 CKJ 1.0 531.0 031010001280
表B:
时间 产品名 数量 价格 贵宾编号
09-23-2010 00:00:00 CKJ 2.0 6732.0 031010001280
07-14-2010 00:00:00 CKJ 2.0 554.39999999999998 031010001280
06-26-2010 00:00:00 CKJ 2.0 634.5 031010001280
05-21-2010 00:00:00 CKJ 2.0 1602.0 031010001280
问题
比如2010-07-14 00:00:00这个时间买了2次产品,但是表B的把这2次加在一起了,我如何删除表B里面2010-07-14 00:00:00这一天的记录,还有2010-06-26 00:00:00也是,就是只要表B里面是把表A里面同一天的数据加起来的话就把表B里面的数据删除,高手指导下看有什么办法,谢谢~~~~
(
SELECT 时间,产品名,贵宾编号,SUM(价格) AS 价格,SUM(数量) AS 数量 FROM 表A GROUP BY 时间,产品名, 贵宾编号
)
DELETE B
FROM 表B B
WHERE NOT EXISTS(SELECT 1 FROM CTE C WHERE B.时间=C.时间 AND B.产品名=C.产品名 AND B.贵宾编号=C.贵宾编号 AND B.价格=C.价格 AND B.数量=C.数量)
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a(时间 datetime, 产品名 varchar(8), 数量 numeric(2,1), 价格 numeric(18,14), 贵宾编号 varchar(12))
insert into #a
select '2010-07-14 00:00:00', 'CKJ', 1.0, 308.69999999999999, '031010001280' union all
select '2010-07-14 00:00:00', 'CKJ', 1.0, 245.69999999999999, '031010001280' union all
select '2010-06-26 00:00:00', 'CKJ', 1.0, 351.0, '031010001280' union all
select '2010-06-26 00:00:00', 'CKJ', -1.0, -351.0, '031010001280' union all
select '2010-06-26 00:00:00', 'CKJ', 1.0, 351.0, '031010001280' union all
select '2010-06-26 00:00:00', 'CKJ', 1.0, 283.5, '031010001280' union all
select '2010-05-21 00:00:00', 'CKJ', 1.0, 1071.0, '031010001280' union all
select '2010-05-21 00:00:00', 'CKJ', 1.0, 531.0, '031010001280'
--> 测试数据:#b
if object_id('tempdb.dbo.#b') is not null drop table #b
create table #b(时间 datetime, 产品名 varchar(8), 数量 numeric(2,1), 价格 numeric(18,14), 贵宾编号 varchar(12))
insert into #b
select '09-23-2010 00:00:00', 'CKJ', 2.0, 6732.0, '031010001280' union all
select '07-14-2010 00:00:00', 'CKJ', 2.0, 554.39999999999998, '031010001280' union all
select '06-26-2010 00:00:00', 'CKJ', 2.0, 634.5, '031010001280' union all
select '05-21-2010 00:00:00', 'CKJ', 2.0, 1602.0, '031010001280'delete b from #b b where (select count(1) from #a where 贵宾编号=b.贵宾编号 and 时间=b.时间) >= 2
select * from #b/*
时间 产品名 数量 价格 贵宾编号
----------------------- -------- --------------------------------------- --------------------------------------- ------------
2010-09-23 00:00:00.000 CKJ 2.0 6732.00000000000000 031010001280(1 行受影响)*/
我加一条create table #b(时间 datetime, 产品名 varchar(8), 数量 numeric(2,1), 价格 numeric(18,14), 贵宾编号 varchar(12))
insert into #b
select '09-23-2010 00:00:00', 'CKJ', 2.0, 6732.0, '031010001280' union all
select '07-14-2010 00:00:00', 'CKJ', 2.0, 554.39999999999998, '031010001280' union all
select '06-26-2010 00:00:00', 'CKJ', 2.0, 634.5, '031010001280' union all
select '05-21-2010 00:00:00', 'CKJ', 2.0, 1602.0, '031010001280' union all
select '05-21-2010 00:00:00', 'CKJ', 2.0, 1602.0, '031010001281' 然后在变成
select * from #b b where (select count(1) from #a where 贵宾编号=b.贵宾编号 and 时间=b.时间) >= 2
结果编号为'031010001281' 就查询不出来啊
Select b.'产品名' from b right join (
Select '时间', '产品名',count('数量')as cun from a group by '时间', '产品名' from a) a
on a.'产品名'=b.'产品名' where a.cun=b.'数量' and a.'时间=b.时间')
Select b.'产品名' from b right join (
Select '时间', '产品名',count('数量')as cun from a group by '时间', '产品名' from a) a
on a.'产品名'=b.'产品名' where a.cun=b.'数量' and a.'时间=b.时间')
--借用#6的表 ^.^#
--查询
DELETE FROM #b
WHERE #b.时间 IN (SELECT 时间 FROM #a)
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a(时间 datetime, 产品名 varchar(8), 数量 numeric(2,1), 价格 numeric(18,14), 贵宾编号 varchar(12))
insert into #a
select '2010-07-14 00:00:00', 'CKJ', 1.0, 308.69999999999999, '031010001280' union all
select '2010-07-14 00:00:00', 'CKJ', 1.0, 245.69999999999999, '031010001280' union all
select '2010-06-26 00:00:00', 'CKJ', 1.0, 351.0, '031010001280' union all
select '2010-06-26 00:00:00', 'CKJ', -1.0, -351.0, '031010001280' union all
select '2010-06-26 00:00:00', 'CKJ', 1.0, 351.0, '031010001280' union all
select '2010-06-26 00:00:00', 'CKJ', 1.0, 283.5, '031010001280' union all
select '2010-05-21 00:00:00', 'CKJ', 1.0, 1071.0, '031010001280' union all
select '2010-05-21 00:00:00', 'CKJ', 1.0, 531.0, '031010001280'
--> 测试数据:#b
if object_id('tempdb.dbo.#b') is not null drop table #b
create table #b(时间 datetime, 产品名 varchar(8), 数量 numeric(2,1), 价格 numeric(18,14), 贵宾编号 varchar(12))
truncate table #b
insert into #b
select '09-23-2010 00:00:00', 'CKJ', 2.0, 6732.0, '031010001280' union all
select '07-14-2010 00:00:00', 'CKJ', 2.0, 554.39999999999998, '031010001280' union all
select '06-26-2010 00:00:00', 'CKJ', 2.0, 634.5, '031010001280' union all
select '05-21-2010 00:00:00', 'CKJ', 2.0, 1602.0, '031010001280'delete #b from #b left join
(select 时间 ,产品名,贵宾编号,sum(数量)总数量 from #a group by 时间 ,产品名,贵宾编号) t
on datediff(day,#b.时间,t.时间)=0 and #b.产品名=t.产品名 and #b.贵宾编号=t.贵宾编号 and #b.数量=t.总数量
where t.时间 is not null
DELETE #b
FROM #b a
INNER JOIN (SELECT 时间,产品名,SUM(数量)数量,SUM(价格)价格,贵宾编号 FROM #a
GROUP BY 时间,产品名,贵宾编号) b
ON a.时间=B.时间 AND a.产品名=b.产品名 AND a.数量=b.数量
AND a.贵宾编号=b.贵宾编号
SELECT * FROM #b
(
times datetime,
names nvarchar(50),
num int,
moneys money,
idno nvarchar(50)
)
insert into #t1
select '2010-07-14 00:00:00','CKJ',1,308.69999999999999,'031010001280' union all
select '2010-07-14 00:00:00','CKJ',1,245.69999999999999,'031010001280' union all
select '2010-06-26 00:00:00','CKJ',1,351.0,'031010001280' union all
select '2010-06-26 00:00:00','CKJ',-1,-351.0,'031010001280' union all
select '2010-06-26 00:00:00','CKJ',1,351.0,'031010001280' union all
select '2010-06-26 00:00:00','CKJ',1,283.5,'031010001280' union all
select '2010-05-21 00:00:00','CKJ',1,1071.0,'031010001280' union all
select '2010-05-21 00:00:00','CKJ',1,531.0 ,'031010001280'create table #t2
(
times datetime,
names nvarchar(50),
num int,
moneys money,
idno nvarchar(50)
)insert into #t2
select '09-23-2010 00:00:00','CKJ',2,6732.0,'031010001280' union all
select '07-14-2010 00:00:00','CKJ',2,554.39999999999998,'031010001280' union all
select '06-26-2010 00:00:00','CKJ',2,634.5,'031010001280' union all
select '05-21-2010 00:00:00','CKJ',2,1602.0,'031010001280'delete a from #t2 a
inner join (select times,names,count(times) as counts from #t1 group by times,names) b
on a.times=b.times and a.names=b.names and b.counts>1
要表B里面是把表A里面同一天的数据加起来的话就把表B里面的数据删除!
把A表中的的数量相加 等于B表中的数量
因为你的数据恰好是 1.0+1.0=2.0,刚好sum 与count一样,
如果A表出现三个怎么办?
所以应该是sum