简单但有用的SQL脚本

行列转换
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 "5"
from
test
pivot
(
sum(profile)
for quarter in
([1],[2],[3],[4],[5])
)
as pvt
create 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,profile
from
test2
unpivot
(
profile
for 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 b