- A+
所属分类:网站建设
把多行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种语法直接执行是会马上刷新到数据库表中的,你刷新一下mdf文件就知道了。