1、跨服务器执行SQL语句(以前从来未用过)
select *form
OPENDATASOURCE ('SQLOLEDB','Data Source=服务器IP;User ID=sa;Password=').DB_DZP.dbo.LOGLAA
select *from
OPENDATASOURCE ('SQLOLEDB','Data Source=服务器IP;User ID=sa;Password=').DB_DZP.dbo.LOGLAB
两个表连接:SELECT *FROM OPENDATASOURCE ('SQLOLEDB','Data Source=10.0.0.186;User ID=sa;Password=').DB_DZP.dbo.LOGLAA a,
OPENDATASOURCE ('SQLOLEDB','Data Source=10.0.0.186;User ID=sa;Password=').DB_DZP.dbo.LOGLAB b
where a.LAA001=LAB001
2、动态执行语句的学习:
create table Dctxt(电池 varchar(10),电压 varchar(10),时间 varchar(10))
insert Dctxt
select '1号' , '2V', '2006-09-13'union all
select '1号' , '6V', '2006-09-14' union all
select '1号' , '8V' , '2006-09-15'union all
select '2号' , '6V', '2006-09-10'union all
select '2号' , '5V' , '2006-09-11'union all
select '2号' , '10V' , '2006-09-12'
如何这成这样:
电池 2006-09-10 2006-09-11 2006-09-12
1号 2V 6V 8V
2号 6V 5V 10V
这种将字段内容作为一个字段名的语句我还是第一次见哦!!!这种语句一般还是定义变量!!!再动态执行!
就OK!!!
declare @str varchar(8000)
set @str = ''
select @str=@str + ',[' + convert(varchar(10),时间,120) + ']=max(case when datediff(dd,时间,''' + convert(varchar(10),时间,120) + ''') = 0 then 电压 else '''' end)'
from Dctxt group by 时间
print @str
EXEC('select 电池' + @str + ' from Dctxt group by 电池')
3、将子查询当作一个字段名来显示,在网络上见过的经典的一个是:
create table A(A_id int,title varchar(10),num int)
insert A
select 1 ,'吃饭',80 union all
select 2 ,'睡觉',75 union all
select 3 ,'看书',84 union all
select 4 ,'上网',92 union all
select 5 ,'游戏',70
将其以num字段来排名次:
4 上网 92 1
3 看书 84 2
1 吃饭 80 3
把子查询当作一个字段名:
select A_id,title,num,(select count(*)+1 from A a2 where num>a1.num or num=a1.num) as 排名
from A a1
order by 4
4、
create table A(num,int)
insert into A
select 10 union all
select 20 union all
select 33 union all
select 42 union all
select 50
写成的结果如下:
10 20
20 33
33 42
42 50
select num,(select min(num) from A a where a.num>b.num) from A b