简单但有用的SL脚本.docVIP

行列互转create?table?test(id?int,name?varchar(20),quarter?int,profile?int)?insert?into?test?values(1,a,1,1000)insert?into?test?values(1,a,2,2000)insert?into?test?values(1,a,3,4000)insert?into?test?values(1,a,4,5000)insert?into?test?values(2,b,1,3000)insert?into?test?values(2,b,2,3500)insert?into?test?values(2,b,3,4200)insert?into?test?values(2,b,4,5500)select?*?from?test--行转列select?id,name,[1]?as?一季度,[2]?as?二季度,[3]?as?三季度,[4]?as?四季度,[5]?as?5fromtestpivot(sum(profile)for?quarter?in([1],[2],[3],[4],[5]))as?pvtcreate?table?test2(id?int,name?varchar(20),?Q1?int,?Q2?int,?Q3?int,?Q4?int)insert?into?test2?values(1,a,1000,2000,4000,5000)insert?into?test2?values(2,b,3000,3500,4200,5500)select?*?from?test2--列转行select?id,name,quarter,profilefromtest2unpivot(profilefor?quarter?in([Q1],[Q2],[Q3],[Q4]))?as?unpvt sql替换字符串 substring replace --例子1：update?tbPersonalInfo?set?TrueName?=?replace(TrueName,substring(TrueName,2,4),**)?where?ID?=?1--例子2：update?tbPersonalInfo?set?Mobile?=?replace(Mobile,substring(Mobile,4,11),********)?where?ID?=?1--例子3：update?tbPersonalInfo?set?Email?=?replace(Email,chinamobile,******)?where?ID?=?1? SQL查询一个表内相同纪录 having 如果一个ID可以区分的话，可以这么写 select?*?from?表?where?ID?in?(select?ID?from?表?group?by?ID?having?sum(1)1) 如果几个ID才能区分的话，可以这么写 select?*?from?表?where?ID1+ID2+ID3?in(select?ID1+ID2+ID3?from?表?group?by?ID1,ID2,ID3?having?sum(1)1) 其他回答：数据表是zy_bho,想找出ZYH字段名相同的记录 --方法1：????SELECT??*FROM??zy_bho??a??WHERE??EXISTS???(SELECT??1??FROM??zy_bho??WHERE??[PK]????a.[PK]??AND??ZYH??=??a.ZYH)????--方法2：select??a.*??from??zy_bho??a??join??zy_bho??b???????on??(a.[pk]b.[pk]??and??a.zyh=b.zyh)????--方法3：select??*??from??zy_bbo??where??zyh??in?(select??zyh??from??zy_bbo??group??by??zyh??having??count(zyh)1)?--其中pk是主键或是?unique的字段。? 把多行SQL数据变成一条多列数据，即新增列SelectDeptName=O.OUName,9G=Sum(Case?When?PersonalGrade=9?Then?1?Else?0?End),8G=Sum(Case?When?PersonalGrade=8?Then?1?Else?0?End),7G4=Sum(Case?When?PersonalGrade=7?AND?Jo