资源描述：
《简单但有用SQL脚本.doc》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
1、1.行列互转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,
2、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 "5"fromtestpivot(sum(profile)for quarter
3、 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,quarte
4、r,profilefromtest2unpivot(profilefor quarter in([Q1],[Q2],[Q3],[Q4])) as unpvt1.sql替换字符串substringreplace--例子1：update tbPersonalInfo set TrueName = replace(TrueName,substring(TrueName,2,4),'**') where ID = 1--例子2：update tbPersonalInfo set Mobile = replac
5、e(Mobile,substring(Mobile,4,11),'********') where ID = 1--例子3：update tbPersonalInfo set Email = replace(Email,'chinamobile','******') where ID = 1 2.SQL查询一个表内相同纪录having如果一个ID可以区分的话，可以这么写select * from 表 where ID in (select ID from 表 group by ID having su
6、m(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.[
7、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是主键或是 u
8、nique的字段。 1.把多行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 JobGrade =4 Then 1 Else
