行列互转
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
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
select ID from 表 group by ID having sum ( 1 ) > 1 ))
( select ID1 + ID2 + ID3 from 表 group by ID1,ID2,ID3 having sum ( 1 ) > 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数据变成一条多列数据,即新增列
DeptName = 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 0 End ),
' 7G3 ' = Sum ( Case When PersonalGrade = 7 AND JobGrade = 3 Then 1 Else 0 End ),
' 6G ' = Sum ( Case When PersonalGrade = 6 Then 1 Else 0 End ),
' 5G3 ' = Sum ( Case When PersonalGrade = 5 AND JobGrade = 3 Then 1 Else 0 End ),
' 5G2 ' = Sum ( Case When PersonalGrade = 5 AND JobGrade = 2 Then 1 Else 0 End ),
' 4G ' = Sum ( Case When PersonalGrade = 4 Then 1 Else 0 End ),
' 3G2 ' = Sum ( Case When PersonalGrade = 3 AND JobGrade = 2 Then 1 Else 0 End ),
' 3G1 ' = Sum ( Case When PersonalGrade = 3 AND JobGrade = 1 Then 1 Else 0 End ),
' 2G ' = Sum ( Case When PersonalGrade = 2 Then 1 Else 0 End ),
' 1G ' = Sum ( Case When PersonalGrade = 1 Then 1 Else 0 End ),
-- ' 未定级'=Sum(Case When PersonalGrade=NULL Then 1 Else 0 End)
表复制
SELECT [ IMSI ]
, count ( [ IMEI ] ) as num
FROM [ Test ] . [ dbo ] . [ PhoneChange ] group by [ IMSI ] order by num desc
语法1:Insert INTO table(field1,field2,...) values(value1,value2,...)
语法2:Insert into Table2(field1,field2,...) select value1,value2,... from Table1(要求目标表Table2必须存在,由于目标表Table2已经存在,所以我们除了插入源表Table1的字段外,还可以插入常量。)
语法3:SELECT vale1, value2 into Table2 from Table1(要求目标表Table2不存在,因为在插入时会自动创建表Table2,并将Table1中指定字段数据复制到Table2中。)
语法4:使用导入导出功能进行全表复制。如果是使用【编写查询以指定要传输的数据】,那么在大数据表的复制就会有问题?因为复制到一定程度就不再动了,内存爆了?它也没有写入到表中。而使用上面3种语法直接执行是会马上刷新到数据库表中的,你刷新一下mdf文件就知道了。
利用带关联子查询Update语句更新数据
Update Table1 set c = ( select c from Table2 where a = Table1.a) where c is null
-- 方法2:
update A
set newqiantity = B.qiantity
from A,B
where A.bnum = B.bnum
-- 方法3:
update
( select A.bnum ,A.newqiantity,B.qiantity from A left join B on A.bnum = B.bnum) AS C
set C.newqiantity = C.qiantity
where C.bnum = XX
连接远程服务器
select * from openrowset ( ' SQLOLEDB ' , ' server=192.168.0.67;uid=sa;pwd=password ' , ' SELECT * FROM BCM2.dbo.tbAppl ' )
-- 方法2:
select * from openrowset ( ' SQLOLEDB ' , ' 192.168.0.67 ' ; ' sa ' ; ' password ' , ' SELECT * FROM BCM2.dbo.tbAppl ' )
TRUNCATE TABLE [Table Name]
下面是对Truncate语句在MSSQLServer2000中用法和原理的说明:
Truncate是SQL中的一个删除数据表内容的语句,用法是:
Truncate table 表名 速度快,而且效率高,因为:
TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同:二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少。
DELETE 语句每次删除一行,并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放。
TRUNCATE TABLE 删除表中的所有行,但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值,请改用 DELETE。如果要删除表定义及其数据,请使用 DROP TABLE 语句。
对于由 FOREIGN KEY 约束引用的表,不能使用 TRUNCATE TABLE,而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中,所以它不能激活触发器。
TRUNCATE TABLE 不能用于参与了索引视图的表。