行列转换
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
这种用法相信在网站中经常使用，如要在表中随机取出10条记录，如果使用编程语言进行运算的话会很麻烦而且效率低下。在Sql Server中自带了random()函数用于生成随机数，其实它还自带了另外一个随机函数newid();newid()在扫描每条记录时都会生成一个随机的值：
执行select newid()；运行结果
可以看到这并不是一个随机的数� ......
