¼òµ¥µ«ÓÐÓõÄ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