34,593
社区成员
发帖
与我相关
我的任务
分享
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'
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
--> 测试数据:#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
--借用#6的表 ^.^#
--查询
DELETE FROM #b
WHERE #b.时间 IN (SELECT 时间 FROM #a)
看不懂问题的意思,帮顶,学习,蹭分.
--> 测试数据:#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))
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 行受影响)
*/
;WITH CTE AS
(
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.数量)