Sql基本语句的学习

--查询每个人订饭的次数
select  username as 姓名, count(*) as 次数 from orderitems  group by UserName having count(*)=1
order by 姓名 desc
select distinct username as 未注册姓名 from orderitems
where username not in (select [Name] from Person)
select distinct username as 已注册姓名 from orderitems where username
in (select [Name] from Person)
--索引为31-40的数据
select top 10 * from orderitems where ID not in (select top 30 ID from orderitems)
--复制数据
insert into  distinctselect(UserName,State,OrderTime) select UserName,State,OrderTime from orderitems
--去除所有重复的记录(完全重复)
select distinct UserName ,ID, State,OrderTime into #Table1 from [distinctselect]
delete from [distinctselect]
insert into [distinctselect](UserName,State,OrderTime) select UserName,State,OrderTime from #Table1
drop table #Table1
--删除某列重复的记录
delete   t  
  from   [distinctselect]   t  
  where   exists   (  
  select   1   from   [distinctselect]  where   username=t.username   and   id<t.id) 
select * from [distinctselect] order by username
delete from [distinctselect]