1.查询表中某个字段值重复出现两次以上
select distinct 字段 from 表 where 字段 in (select 字段 from 表 group by 字段 having count(1)>1)
2.把查询结果当做一个表来查询
select * from (select sum(id) as sunNum from people) as tempTable
3.将查询出的记录插入到另一张表中
插入新表(会在数据库中生成):
select 目标列 into 新表 from 旧表 where 条件
插入到已存在的表:
insert into 插入的表名 select 目标列 from 表名 where 条件
4.随机取n条数据
select top 10 from 表 where 条件 order by NEWID()
说明:NEWID()在扫描每条记录的时候都生成一个值,而生成的值是随机的,没有大小顺序,所以最终结果再按这个排序,排序结果当然就是无序的了
5. 判断数据库中是否存在某张表
select count(1) from syscolumns where [id]=object_id('表名称')
结果等于0就是没有,大于0就是有
6.查询结果的连接Union、相减Except
(select nameA from preduty where (week='星期一' or week='星期三' or week='星期五' or
week ='星期六') and (mix='true'))
union
(
select nameA from preduty where (mix='ffalse') and
(week='星期一' or week='星期三' or week='星期五' or week ='星期六')
)
union
(
select nameB from preduty where (mix='ffalse') and
(week='星期一' or week='星期三' or week='星期五' or week ='星期六'))
)
except
(
select nameA from tableThursday
)
7.添加新字段
alter table tableThursday add xvhao int identity(1,1) primary key
8.给个起始日期,日期在起始日期的基础上每天加1,依次插入到表中某列
ALTER PROCEDURE [dbo].[InsertNowDate]
@startdate date
AS
declare @countDay int
set @countDay =1
while(@countDay<=32)
BEGIN
update nowdutysheet set dutydate=(CONVERT(date,@startdate,102))
where nowid=@countDay
set @startdate=DATEADD(DAY,1,@startdate)
set @countDay=@countDay+1
END
9.在新值班表中填充每个人的电话,从基础表获得信息
ALTER PROCEDURE [dbo].[InsertPhoneAB]
AS
declare @countDay int
declare @stuNameA nchar(8)
declare @stuNameB nchar(8)
set @countDay =1
while(@countDay<=32)
BEGIN
set @stuNameA=(select top 1 nameA from nowdutysheet where phoneA is null order by nowid)
update nowdutysheet set phoneA=(select phone from people where name=@stuNameA) where nameA=@stuNameA
set @stuNameB=(select top 1 nameB from nowdutysheet where phoneB is null order by nowid)
update nowdutysheet set phoneB=(select phone from people where name=@stuNameB) where nameB=@stuNameB
set @countDay=@countDay+1
END
10.重置操作,删除数据库中所有没有用的生成表
ALTER PROCEDURE [dbo].[ReDo]
AS
BEGIN
if (select count(1) from syscolumns where [id]=object_id( 'tableThursday' ))>0
drop table dbo.tableThursday
if (select count(1) from syscolumns where [id]=object_id( 'tableTuesSun' ))>0
drop table dbo.tableTuesSun
if (select count(1) from syscolumns where [id]=object_id( 'tablecommon' ))>0
drop table dbo.tablecommon
if (select count(1) from syscolumns where [id]=object_id( 'temptableA' ))>0
drop table dbo.temptableA
truncate table nowdutysheet
update nowdutysheet set dutydate =null
if (select count(1) from syscolumns where [id]=object_id( 'tableMale' ))>0
drop table tableMale
if (select count(1) from syscolumns where [id]=object_id( 'ThursMale' ))>0
drop table ThursMale
END
11.从表中随机获取一名同学到新值班表
--从tableTuesSun中随机获取一名女生
ALTER procedure [dbo].[SelectOneCommon]
as
begin
declare @stuName nvarchar(4)
declare @weekIndex int
set @weekIndex=(select min(nowid) from nowdutysheet where (week='星期一' or
week='星期三' or week ='星期五' or week='星期六' ) and ( nameA is null))
--随机选取一名周一/三/五/六值班的
set @stuName=(select top 1 nameA from tablecommon where herstate='1'
order by NEWID())
--状态置0,下次不再取
update tablecommon set herstate='0',choosetime=GETDATE() where nameA=@stuName
update nowdutysheet set nameA =@stuName where nowid=@weekIndex
end
12.在曹教授的帮助下,写的给个起始星期X,向下自动填充星期
存储过程:
ALTER PROCEDURE [dbo].[GetWeek]
@StartWeek int
as
declare @mycount int
declare @thisWeek int
declare @countDay int
set @countDay=1
set @mycount=@StartWeek
while(@countDay<=32)
begin
set @thisWeek=(@mycount+7)%7
insert into nowdutysheet(week) values(dbo.GetWeekFunction(@thisWeek))
set @mycount=@mycount+1
set @countDay=@countDay +1
end
标量值函数:
ALTER function [dbo].[GetWeekFunction](@weekindex int)
returns nvarchar(4)
as
begin
declare @myWeekName nvarchar(4)
if(@weekindex=1)
begin
set @myWeekName='星期一'
return @myWeekName
end
if(@weekindex=2)
begin
set @myWeekName='星期二';
return @myWeekName;
end
if(@weekindex=3)
begin
set @myWeekName='星期三';
return @myWeekName;
end
if(@weekindex=4)
begin
set @myWeekName='星期四';
return @myWeekName;
end
if(@weekindex=5)
begin
set @myWeekName='星期五';
return @myWeekName;
end
if(@weekindex=6)
begin
set @myWeekName='星期六'
return @myWeekName
end
if(@weekindex=0)
begin
set @myWeekName='星期日'
return @myWeekName
end
return @myWeekName
end