1、说明：两张关联表，删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
2、说明：日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
3、选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
4、删除重复记录
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
5、说明：列出数据库里所有的表名
select name from sysobjects where type='U'
6、说明：列出表里的所有的
select name from syscolumns where id=object_id('TableName')
7、随机产生数
select top 10 * from Userinfo order by NEWID ()
--> 测试数据: [users]
if object_id('[users]') is not null drop table [users]
go
create table [users] (userid int,username varchar(4))
insert into [users]
select 1,'小红' union all
select 2,'小明' union all
select 3,'小李'
--> 测试数据: [score]
if object_id('[score]') is not null drop table [score]
go
create table [score] (id int,userid int,subject varchar(4),score int)
insert into [score]
select 1,1,'语文',80 union all
select 2,1,'数学',98 union all
select 3,1,'英语',60 union all
select 4,1,'地理',70 union all
select 5,2,'生物',78 union all
select 6,2,'语文',65 union all
select 7,3,'历史',66
if object_id('f_str') is not null drop function f_str
go
create function f_str(@id int)
returns varchar(100)
as
begin
declare @s varchar(100)
select @s=isnull(@s+' ','')+subject+':'+ltrim(score) from score where
userid=@id
return @s
end
go
select a.userid,a.username,subject_score=dbo.f_str(a.userid)
from [users] a,
[score] b
where a.userid=b.userid
group by a.userid,a.username
<a href="#" οnclick="window.open(' <%# Eval("J_jobId")%>');">职位详细信息 </a>
js:
function open(id) {
loc_x = document.body.scrollLeft + event.clientX - event.offsetX + 100;
loc_y = document.body.scrollTop + event.clientY - event.offsetY + 170;
window.showModalDialog("JobsView.aspx?J_jobId="+id, self, "edge:raised;scroll:0;status:0;help:0;resizable:1;dialogWidth:350px;dialogHeight:350px;dialogTop:" + loc_y + "px;dialogLeft:" + loc_x + "px");
}
--行列互转
/******************************************************************************************************************************************************
以学生成绩为例子，比较形象易懂
整理人：中国风(Roy)
日期:2008.06.06
******************************************************************************************************************************************************/
--1、行互列
--> --> (Roy)生成測試數據
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85
Go
--2000方法:
动态:
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+' from Class group by [Student]')
生成静态:
select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end)
from
Class
group by [Student]
GO
动态:
declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')
生成静态:
select *
from
Class
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b
生成格式:
/*
Student 数学 物理 英语 语文
------- ----------- ----------- ----------- -----------
李四 77 85 65 65
张三 87 90 82 78
(2 行受影响)
*/
------------------------------------------------------------------------------------------
go
--加上总成绩(学科平均分)
--2000方法:
动态:
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+',[总成绩]=sum([Score]) from Class group by [Student]')--加多一列(学科平均分用avg([Score]))
生成动态:
select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end),
[总成绩]=sum([Score]) --加多一列(学科平均分用avg([Score]))
from
Class
group by [Student]
go
--2005方法:
动态:
declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号
exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a
pivot (max([Score]) for [Course] in('+@s+'))b ')
生成静态:
select
[Student],[数学],[物理],[英语],[语文],[总成绩]
from
(select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b
生成格式:
/*
Student 数学 物理 英语 语文 总成绩
------- ----------- ----------- ----------- ----------- -----------
李四 77 85 65 65 292
张三 87 90 82 78 337
(2 行受影响)
*/
go
--2、列转行
--> --> (Roy)生成測試數據
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go
--2000:
动态:
declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序
生成静态:
select *
from (select [Student],[Course]='数学',[Score]=[数学] from Class union all
select [Student],[Course]='物理',[Score]=[物理] from Class union all
select [Student],[Course]='英语',[Score]=[英语] from Class union all
select [Student],[Course]='语文',[Score]=[语文] from Class)t
order by [Student],[Course]
go
--2005:
动态:
declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student')
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')
go
select
Student,[Course],[Score]
from
Class
unpivot
([Score] for [Course] in([数学],[物理],[英语],[语文]))b
生成格式:
/*
Student Course Score
------- ------- -----------
李四 数学 77
李四 物理 85
李四 英语 65
李四 语文 65
张三 数学 87
张三 物理 90
张三 英语 82
张三 语文 78
(8 行受影响)
查找张表的列
select name from syscolumns where id=object_id('Userinfo')
查找数据库里面所有的表
select name from sysobjects where type='u'
查找根据系统时间 星期的总数
select Userinfo.* , isnull(t.cnt,0) counts , isnull(s.cnt,0) countss from Userinfo left join
(select u_code , count(1) cnt from UnitCard where datediff(week,tianxieTime,getdate()) = 0 and isxiugai =0
group by u_code) t
on Userinfo.u_code = t.u_code left join (select u_code , count(1) cnt from UnitCard where datediff(week,tianxieTime,getdate()) = 0 and isxiugai =1
group by u_code) s
on Userinfo.u_code = s.u_code
多个列合并成一列
select daochuID , --导出商店
cc=LTRIM (m.A1)+LTRIM (m.A2)+LTRIM (m.A3)+LTRIM (m.A4)+LTRIM (m.A5)+LTRIM (m.A6)+LTRIM (m.A7)+LTRIM (m.A8)+LTRIM (m.A9)+LTRIM (m.A10)+LTRIM (m.A11)+LTRIM (m.A12)+LTRIM (m.A13),
BB=right('00'+ltrim(cityid),2)+
right('00'+ltrim(ShopID),2)+
right('00'+ltrim(zhuanGuiTypeID),2)+
substring(convert(varchar(8),FangwenTime,112),5,8),
FangwenTime ,questionnaireID ,CityID
FROM WriteQuestionnaire join MeirongcountView as m on m.WenjuanItem=questionnaireID
把数值大于9的换成1
如 张三 1；2；3；4；5；6；7；8；
换成 张三 1；1；1；1；1；1；1；1；
SELECT wenjuanItem ,name --导出活动类型
FROM huodongType AS A
CROSS APPLY(
SELECT name =(
SELECT CASE WHEN CAST(x AS int) > 1 THEN '1' ELSE x END
FROM (SELECT x=SUBSTRING(A.huodongtype,number,CHARINDEX(';',A.huodongtype+';',number)-number)
FROM master.dbo.spt_values
WHERE type = 'P' AND SUBSTRING(','+A.huodongtype,number,1)=';') AS T
FOR XML PATH('')
)
修改最后已"；"结尾的
UPDATE again_changhui_brand SET
sku = LEFT(sku,LEN(sku)-1)
WHERE RIGHT(sku,1)=';'