sql行转列，列转行
- 格式：pdf
- 大小：85.01 KB
- 文档页数：3
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 by zyh having count(zyh)>1)
--其中pk是主键或是 unique的字段。
复制代码
把多⾏SQL数据变成⼀条多列数据，即新增列
复制代码
Select
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)
复制代码
表复制
insert into PhoneChange_Num ([IMSI],Num)
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种语法直接执⾏是会马上刷新到数据库表
利⽤带关联⼦查询Update语句更新数据
复制代码
--⽅法1：
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
复制代码
连接远程服务器
--⽅法1：
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')
--判断某些字段是否为空
--case
select case when '字段名' is null then '\N' else convert(varchar(20),'字段名') end as 'NewName'
select case when null is null then '\N' else convert(varchar(20),null) end as 'NewName'
--SQL Server 2005：coalesce
select coalesce('字符串类型字段','\N') as 'NewName'
select coalesce(convert(varchar(20),'⾮字符串类型字段'),'\N') as 'NewName'
select coalesce(convert(varchar(20),null),'\N') as 'NewName'
--coalesce,返回其参数中的第⼀个⾮空表达式
select Coalesce(null,null,1,2,null)union
select Coalesce(null,11,12,13,null)union
select Coalesce(111,112,113,114,null)
newid()的妙⽤
⽣成测试数据
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[test](
[name] [varchar](20) NULL,
[quarter] [int] NULL,
[profile] [int] NULL,
[dates] [smallint] NULL,
[ID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
