删除语句问题，在线等

[zhanglong_longlong]

CREATE TABLE [dbo].[Tests](
	[uID] [int] NULL,
	[CreateTime] [datetime] NULL,
	[Type] [int] NULL,
	[TTT] [nvarchar](50) NULL
) ON [PRIMARY]


insert into [test].[dbo].[Tests] values(1,'2014-10-22 9:31:27',1,'a')
insert into [test].[dbo].[Tests] values(1,'2014-10-22 9:41:27',1,'b')
insert into [test].[dbo].[Tests] values(1,'2014-10-22 9:51:27',1,'c')
insert into [test].[dbo].[Tests] values(1,'2014-10-22 17:31:27',2,'d')
insert into [test].[dbo].[Tests] values(1,'2014-10-22 17:41:27',2,'e')
insert into [test].[dbo].[Tests] values(1,'2014-10-23 9:31:27',1,'f')
insert into [test].[dbo].[Tests] values(2,'2014-10-22 9:41:27',1,'g')
insert into [test].[dbo].[Tests] values(2,'2014-10-22 9:51:27',1,'h')
insert into [test].[dbo].[Tests] values(2,'2014-10-22 17:31:27',2,'i')
insert into [test].[dbo].[Tests] values(2,'2014-10-22 17:41:27',2,'j')

结果

之前sql语句是实现了，代码是

delete from tests 
from tests t,
(select uid, min(createtime) createtime from Tests where TYPE = 1 group by uid, LEFT(Createtime,10)) a,
(select uid, max(createtime) createtime from Tests where TYPE = 2 group by uid, LEFT(Createtime,10)) b
where t.uid = a.uid 
  and t.uid = b.uid
  and LEFT(t.Createtime,10) = LEFT(a.Createtime,10) 
  and LEFT(t.Createtime,10) = LEFT(b.Createtime,10) 
  and t.createtime <> a.createtime
  and t.createtime <> b.createtime
;

现在想用Access实现这种结果删除，但是显示语法有问题。有知道Access怎么实现这个呢？

[zhanglong_longlong]

引用 8 楼 lovelj2012 的回复:

估计access不支持两张表逗号连接，改成inner join试试

delete from  tests where exists(select 1 from  tests t
inner join (
select uid, min(createtime) createtime from Tests where TYPE = 1 group by uid, LEFT(Createtime,10)) a on t.uid = a.uid 
inner join (
select uid, max(createtime) createtime from Tests where TYPE = 2 group by uid, LEFT(Createtime,10)) b on t.uid = b.uid
where  LEFT(t.Createtime,10) = LEFT(a.Createtime,10) 
  and LEFT(t.Createtime,10) = LEFT(b.Createtime,10) 
  and t.createtime <> a.createtime
  and t.createtime <> b.createtime)

报那个地方有点问题 select uid, min(createtime) createtime from Tests where TYPE = 1 group by uid, LEFT(Createtime,10)) a on t.uid = a.uid inner join ( select uid, max(createtime) createtime from Tests where TYPE = 2 group by uid, LEFT(Createtime,10)) b on t.uid = b.uid

[power_user]

很详细，收益非浅。谢谢分享。

[zhanglong_longlong]

DELETE AAA FROM
(select * ,row_number() over (partition by  [uID],type,CONVERT(varchar(100), [CreateTime], 102) order by  [CreateTime]) as numrow
from [test].[dbo].[Tests])  AAA where numrow>1 and AAA.Type=1

DELETE AAA FROM
(select * ,row_number() over (partition by  [uID],type,CONVERT(varchar(100), [CreateTime], 102) order by  [CreateTime] desc) as numrow
from [test].[dbo].[Tests])  AAA where numrow>1 and AAA.Type=2

这种sql也可以达到效果，但是Access不知道如果修改，Access大牛麻烦看看

[woaini6157730]

我也不懂啊这个很多的

[zhanglong_longlong]

要求是每个用户同一天只能出现一个type 1和一个Type 2，类似用户考勤一样，type 1代表早上打卡，type 2代表晚上打卡，现在要保留最早时间type 1的当天日期和Type 2最晚的一次打卡数据，删除中间的重复打卡，以上我的sql中执行的效果和结果，Access中应该如果修改？

[csover8]

语法错误呢？？发出来看看。

[江南小鱼]

木有装access，没办法试一下。 执行下看报神马错，再百度找access的语法呗

[zhanglong_longlong]

delete from tests from tests t, (select uid, min(createtime) createtime from Tests where TYPE = 1 group by uid, LEFT(Createtime,10)) a, (select uid, max(createtime) createtime from Tests where TYPE = 2 group by uid, LEFT(Createtime,10)) b where t.uid = a.uid and t.uid = b.uid and LEFT(t.Createtime,10) = LEFT(a.Createtime,10) and LEFT(t.Createtime,10) = LEFT(b.Createtime,10) and t.createtime <> a.createtime and t.createtime <> b.createtime ;报这里有语法错误

[江南小鱼]

delete from  tests where exists(select 1 from  tests t
(select uid, min(createtime) createtime from Tests where TYPE = 1 group by uid, LEFT(Createtime,10)) a,
(select uid, max(createtime) createtime from Tests where TYPE = 2 group by uid, LEFT(Createtime,10)) b
where t.uid = a.uid 
  and t.uid = b.uid
  and LEFT(t.Createtime,10) = LEFT(a.Createtime,10) 
  and LEFT(t.Createtime,10) = LEFT(b.Createtime,10) 
  and t.createtime <> a.createtime
  and t.createtime <> b.createtime)

[江南小鱼]

貌似上面的语句等价于

delete from  tests where exists(select 1 from 
(select uid, min(createtime) createtime from Tests where TYPE = 1 group by uid, LEFT(Createtime,10)) a,
(select uid, max(createtime) createtime from Tests where TYPE = 2 group by uid, LEFT(Createtime,10)) b
where t.uid = a.uid 
  and t.uid = b.uid
  and LEFT(t.Createtime,10) = LEFT(a.Createtime,10) 
  and LEFT(t.Createtime,10) = LEFT(b.Createtime,10) 
  and t.createtime <> a.createtime
  and t.createtime <> b.createtime)

[zhanglong_longlong]

引用 6 楼 lovelj2012 的回复:

delete from  tests where exists(select 1 from  tests t
(select uid, min(createtime) createtime from Tests where TYPE = 1 group by uid, LEFT(Createtime,10)) a,
(select uid, max(createtime) createtime from Tests where TYPE = 2 group by uid, LEFT(Createtime,10)) b
where t.uid = a.uid 
  and t.uid = b.uid
  and LEFT(t.Createtime,10) = LEFT(a.Createtime,10) 
  and LEFT(t.Createtime,10) = LEFT(b.Createtime,10) 
  and t.createtime <> a.createtime
  and t.createtime <> b.createtime)

还是报form子句语法错误

[江南小鱼]

估计access不支持两张表逗号连接，改成inner join试试

delete from  tests where exists(select 1 from  tests t
inner join (
select uid, min(createtime) createtime from Tests where TYPE = 1 group by uid, LEFT(Createtime,10)) a on t.uid = a.uid 
inner join (
select uid, max(createtime) createtime from Tests where TYPE = 2 group by uid, LEFT(Createtime,10)) b on t.uid = b.uid
where  LEFT(t.Createtime,10) = LEFT(a.Createtime,10) 
  and LEFT(t.Createtime,10) = LEFT(b.Createtime,10) 
  and t.createtime <> a.createtime
  and t.createtime <> b.createtime)

[OptimizationMaster]

delete from Tests where TTT not in ( select TTT from Tests t, ( select uid, TYPE, min(createtime) as ActionTime from Tests where TYPE = 1 group by uid, TYPE, LEFT(Createtime,10) union select uid, TYPE, max(createtime) as ActionTime from Tests where TYPE = 2 group by uid, TYPE, LEFT(Createtime,10) ) k where t.uid = k.uid and t.type = k.type and t.CreateTime = k.ActionTime );

[zhanglong_longlong]

引用 8 楼 lovelj2012 的回复:

估计access不支持两张表逗号连接，改成inner join试试

delete from  tests where exists(select 1 from  tests t
inner join (
select uid, min(createtime) createtime from Tests where TYPE = 1 group by uid, LEFT(Createtime,10)) a on t.uid = a.uid 
inner join (
select uid, max(createtime) createtime from Tests where TYPE = 2 group by uid, LEFT(Createtime,10)) b on t.uid = b.uid
where  LEFT(t.Createtime,10) = LEFT(a.Createtime,10) 
  and LEFT(t.Createtime,10) = LEFT(b.Createtime,10) 
  and t.createtime <> a.createtime
  and t.createtime <> b.createtime)

这样不行的，不知道您那边有没有测试过

[qq_32797585]

不知道结合贴牛提督是政府

[M9308621]

看看，就是看不懂～.

[zhanglong_longlong]

有人知道吗？Access大牛

[zhanglong_longlong]

有人知道吗？

[郗晓勇]

http://wibbe.xyz/issue/2799591/delete-records-from-multiple-tables-in-access-2007

[coding84.com]

不明觉厉～…………………………