关键字:SQL Server 数据库
引言
平时做开发不可避免的与数据库打交道,总会遇到这样或那样的问题,遇到难题就goole一下,下次遇到有时还要Google,干脆平时积累一下遇到同样问题解决也就方便多了,下面就是我平时积累和搜集的一些常见问题的解决技巧的积累,希望对大家也能有帮助。
阅读对象
- 对SQL Server有开发经验
- 其它对单元测试有兴趣的人员
1) 跨服务器数据库操作
这个对于DBA可能要经常用到。假设:有两个数据库DB1,DB2,DB1下有T1表,DB2下有T2表,目前要查询T1的字段F1和T2下字段F2相同的记录。目前查询分析器链接的DB1。方案一:使用OPENDATASOURCE直接进行查询。
SQL语句范例:
select T1.*, T2.* from T1, OPENDATASOURCE('SQLOLEDB','Data Source=192.168.0.5;User ID=sa;Password=sa;').DB2.dbo.T2 as T2 where t1.F1 = t2.F2方案二:为DB1所在数据库使用 sp_addlinkedserver 新增一个链接服务器 进行查询。
操作范例:
EXEC sp_addlinkedserver @server='LocDB2',--被访问的服务器别名 @srvproduct='', @provider='SQLOLEDB', @datasrc='192.168.0.5' --要访问的服务器 EXEC sp_addlinkedsrvlogin 'LocDB2', --被访问的服务器别名 'false', NULL, 'sa', --帐号 'sa' --密码 select T1.*, T2.* from T1, LocDB2.DB2.dbo.T2 as T2 where T1.F1= T2.F2当然:如果DB2和DB1在同一台技巧上,并且当前登录DB1的用户有权访问DB2,那么就没有必要这么复制了,直接使用
select T1.*, T2.* from T1, DB2.dbo.T2 as T2 where T1.F1= T2.F2
2) 不同排序规则(COLLATE)的查询
这种现象我经常碰到,因为我在台资公司工作,当时我还是喜欢简体,因此我的数据库的排序规则基本上都是简体,而好多其它的大部分用的繁体,因此不同的字段比较时就出现了此问题。 比如数据库DB1,表T1,F1字段的排序规则为 Chinese_PRC_CI_AS, F2字段为 Chinese_Taiwan_Stroke_CI_AS,查询 此表中F1和F2字段相等的记录。select * from T1 where F1= F2 COLLATE Chinese_PRC_CI_ASCOLLATE Chinese_PRC_CI_AS 的含义强制都转换为 Chinese_PRC_CI_AS 格式进行比较。
3) 动态列查询
这是需求做系统报表时常见的一个查询统计需求。需求描述:比如有一个成绩表,包含姓名(realname),课程(Class), 成绩等级(grade),原始数据如下图
但是我想查询出如右下图
查询语句实现
declare @s nvarchar(4000) set @s='' select @s=@s+','+quotename(class)+ '=isnull(rtrim(max(case Class when ' +quotename(Class,'''')+' then Score end)),'''')' from TSC group by class -- print @S --这里输出@S 你可以看到动态组织的SQL 语句的原貌 exec('select RealName as ''项目\科目''' + @s + ' from TSC group by RealName ')当然这里面有一个缺陷就是,这里的class不能太多,否则 @S 超过系统所承受的最大长度您就无法得到查询结果了
4) 查询表中以某字段为分类,另外一个字段满足制定条件的记录信息
例如T1表有如下数据(左图)而查询结果(右图)。要求为:查询F2字段内容相同,F3数值最大的所有记录。原始数据 目的结果
解决方案一:首先建立一个以F2数据为结果集合的视图数据集合。然后创建指定指定为输出结果的查询函数。
1.1)创建视图
CREATE VIEW dbo.V_T_Distinct AS SELECT DISTINCT F2 FROM dbo.T11.2 创建输出F1, F3的函数,这里要查询输出几个字段,就要写几个函数,因此此方法对于要很多字段,并且都要输出的表非常不方便。
-- 创建输出F1的函数 CREATE FUNCTION dbo.fun_Get_F1 (@F2 varchar(16)) RETURNS int AS BEGIN declare @Ret int declare @MaxF3 varchar(16) select @MaxF3 = max(F3) from T1 where F2 = @F2 select @Ret = F1 from T1 where F2 = @F2 and @MaxF3 = F3 return @Ret END GO -- 创建输出F3的函数 CREATE FUNCTION dbo.fun_Get_F3 (@F2 varchar(16)) RETURNS varchar(16) AS BEGIN declare @MaxF3 varchar(16) select @MaxF3 = max(F3) from T1 where F2 = @F2 return @MaxF3 END1.3 执行最后的查询语句,输出查询结果
select dbo.fun_Get_F1(F2) as F1,F2, dbo.fun_Get_F1(F2) as F3 from V_T_Distinct
解决方案二: 首先建立一个能够定位所需记录的关键字的视图,然后用表关联查询结果
2.1 创建定位记录关键字视图
CREATE VIEW dbo.V_T1 AS SELECT F2, MAX(F3) AS Max_F3 FROM dbo.T1 GROUP BY F2
下面是此视图结果集
2.2 组织查询语句输出查询结果
select T1.* from T1,V_T1 where T1.F2=V_T1.F2 and F3=Max_F3
这种方法比较简单,推荐使用。