表 A 用户
Id Int 主键
Name varchar(20)
regDate datetime
表 B 上传信息
Id int 主键
UId int 外键与a关联
FileName varchar(20) ..
sql 实现:
1。最近三天注册的用户中上传文件数量最多的用户及上传数量
2。删除用户表A中用户名重复的最后一条(注册时间晚的)--------
:没有‘上传数量’等其他任何字段谢谢.
Id Int 主键
Name varchar(20)
regDate datetime
表 B 上传信息
Id int 主键
UId int 外键与a关联
FileName varchar(20) ..
sql 实现:
1。最近三天注册的用户中上传文件数量最多的用户及上传数量
2。删除用户表A中用户名重复的最后一条(注册时间晚的)--------
:没有‘上传数量’等其他任何字段谢谢.
解决方案 »
- 请教一个SQL语句!
- sql2000还原数据库后网站程序登录不了了。出现:错误 '80004005'
- [在线]存储过程如何返回多个32位的整形值?
- 机子上原有SQLSERVER2000,现在装了个SQLSERVER2005SP2,每次开机时!
- 在线等,谢谢
- 怎么把2个表合并到一个表?
- 两个查询结果的连接问题
- 在数据表中一字段ID是设为自增的,如何在添加了一行后,得到ID的值啊!
- SQLSERVER一直有语句在运行
- win2000专业版下如何安装sql sever 7.0? 不能装服务器?只能装客户端?真的是这样吗?请高手给予解释!
- SQL 2008上建的数据库如何最方便的还原至SQL2000上???
- 在sql中如何实现时间的搜索?
1: select top 1 s.name,count(t.FileName ) from B t ,
(select id,name from A where datediff(dd,getdtae(),regDate)<=3) s
where t.uid=s.id
group by s.name
order by count(1) desc
1
select top 1 s.name,count(t.FileName ) from B t ,
(select id,name from A where datediff(dd,getdtae(),regDate)<=3) s
where t.uid=s.id
group by s.name
order by count(t.FileName ) desc2 delete s
from a s,(select name,max(regDate) as regDate from a group by name) t
where a.name=t.name and
a.regDate=t.regDate
from a s,(select name,max(regDate) as regDate from a group by name) t
where s.name=t.name and
s.regDate=t.regDate
1:select top 1 name, count(filename)
from a join b on a.id=b.uid
where datediff(day,regDate ,getdate())<3
group by a.id order by count(filename) desc
2:delete A1 from a A1 where not exists(select 1 from a where name=A1.name and regDate<A1.regDate )
select top 1 name,num=count(*) from A ,B
where a.id=b.uid and datediff(day,regDate,getdate())<=3
group by name
order by count(*)
--2、
delete from a
where not exists (select * from a b where b.name=a.name and b.regDate>regDate)
--建表,只建表A(我懒...)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[A]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[A]
GOCREATE TABLE [dbo].[A] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[RegDate] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO--插入数据,用户dd不存在重复的记录,删除时不应将其删除
INSERT INTO [A]
SELECT 'aa',getdate()
UNION
SELECT 'aa',dateadd(day,-1,getdate())
UNION
SELECT 'aa',dateadd(day,-4,getdate())
UNION
SELECT 'aa',dateadd(day,-7,getdate())
UNION
SELECT 'bb',getdate()
UNION
SELECT 'bb',dateadd(day,-1,getdate())
UNION
SELECT 'bb',dateadd(day,-4,getdate())
UNION
SELECT 'cc',getdate()
UNION
SELECT 'cc',dateadd(day,-1,getdate())
UNION
SELECT 'dd',getdate()
GO--1
SELECT TOP 1 [Name],COUNT(*) FROM A
JOIN B ON A.Id = B.Uid
WHERE datediff(day,regDate,getdate())<=3
GROUP BY Name
ORDER BY COUNT(*)--2
DELETE FROM t1
FROM A t1
WHERE t1.regDate = (
SELECT MAX(regDate) FROM A t2
WHERE t1.Name = t2.Name
GROUP BY [Name]
HAVING COUNT(*) > 1
)
删除的时候注意外键约束,或者可以将外键设置为ON DELETE CASCADE
返现 ks_reny 的第一个 我这边差不多成了
du209 的第二个 差不多成了。
应该是datediff(dd,regDate,getdtae()) <= 3DATEDIFF ( datepart , startdate , enddate ) 是 enddate - startdate
注册时间(regDate)肯定是小于当前时间的,如果是datediff(dd,getdtae(),regDate)的话,返回的结果是非正数,是肯定小于3的正确地筛选最近三天注册的用户应该是用 datediff(dd,regDate,getdtae()) <= 3
使用SELECT *, DATEDIFF(dd, GETDATE(), RegDate) AS 天数差 FROM A 测试一下就知道了