把我在开发时候遇到的一点小知识持续更新在这里~
1.where条件时常变
where UserID='1' 这里的UserID呢,它的值是经常在变化的,有时候要查2,有时候要查3的,有时候要查全部人!这里定义一个函数传参数的时候,查全部人这里需要搞一个if分支判断一下,但是呢,不需要~~好的办法来了
select * from 表 Where UserID="+UserID+"
大概就是这样子,UserID是一个参数,你有需要的时候直接传给我1或者2或者其他数字都OK,但是你不需要这个条件的时候,只需要传给我UserID这个就行了,UserID=UserID会查出所有的数据~
2.联表查询都知道,现在来试试三表查询
Sql = "select * from VehicleRegistration a inner join TB_DictData b on a.VehicleID=b.Name inner join T_ACL_User c on a.UserID=c.ID where b.Value='" + Value + "'";
多个inner join on 就ok了
3.出现这样的错误:
消息 8120,级别 16,状态 1,第 6 行
选择列表中的列 'a.Value' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。
原因以及解决办法:
凡是在group by后面出现的字段,必须同时在select后面出现;凡是在select后面出现的、同时未在聚合函数中出现的字段,必须同时出现在group by后面”,检查sql是否符合上述法则。
其实这个group by还有一些需要注意的地方,在select后面的如果是 as 某一列存在的话是不需要出现在group by后面的,还有,如果select后面出现聚合函数,例如SUM,count等,则一定要有group by存在
4.书写sql语句的方法(一个资深的同事建议,写在using语句里面可以自动释放数据库链接)
public static DataTable SqlConnectionInformation() { string connstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; DataTable dt = new DataTable(); using (SqlConnection conn = new SqlConnection(connstr)) { if (conn.State == ConnectionState.Closed) { conn.Open(); } string sql = "select * from table"; SqlCommand com = new SqlCommand(sql, conn); SqlDataAdapter adapter = new SqlDataAdapter(com); adapter.Fill(dt); } return dt; }
5.要学会使用in啊,我脑残的写了一大串子...
select * from WEB_ItemInfo i where id in(26933,26934,26935,26936,26937,26938,26939,26940,26941)
我傻逼的写了9次,然后用union连接了起来
6.学会使用CTE公用表表达式
with t as ( select * from ...... ) select * from t where .....
这是简单的CTE,可以写复杂一点,比如想对表的查询结果继续进行操作,那么就多写几个CTE
with t as( select CONVERT(varchar(8), a.ItemDate, 112) as datekey ,left(i.Name,5)as Name,a.ItemValue from Web_ItemInfo i inner join AnalysisData a on i.ID in(26933,26934,26935,26936,26937,26938,26939,26940,26941) and i.ID=a.ItemID ), t1 as( select convert(varchar(20),'蜀云泉真帅啊') as plantname ,convert(varchar(20),'音乐家') as divisionname,t.datekey,t.Name,SUM(ItemValue) as data from t where datekey>= dbo.GetUpdateDateKey() group by Name,datekey ), t2 as( select convert(varchar(20),'蜀云泉真帅啊') as plantname,convert(varchar(20),'音乐家') as divisionname, convert(varchar(12),replace(date_day,'-','')) as datekey,b.pumpname, (isnull(h0,0)+isnull(h1,0)+isnull(h2,0)+isnull(h3,0)+isnull(h4,0)+isnull(h5,0) +isnull(h6,0)+isnull(h7,0)+isnull(h8,0)+isnull(h9,0) +isnull(h10,0)+isnull(h11,0)+isnull(h12,0)+isnull(h13,0)+isnull(h14,0) +isnull(h15,0)+isnull(h16,0)+isnull(h17,0)+isnull(h18,0)+isnull(h19,0) +isnull(h20,0)+isnull(h21,0)+isnull(h22,0)+isnull(h23,0))/60 as data from web_daydata a inner join web_pumpconfig b on b.scid=a.id where date_day>=dbo.GetUpdateDate() ), t3 as( select * from t1 union all select * from t2 ) select e.Equipmentid,11 as PlantID,18 asDivisionID,t3.* from t3 inner join SZWG.dbo.Dim_Equipment e on 11=e.PlantID and 18=e.DivisionID and Name=e.EquipmentName
7.连表查询还可以这样来,不需要写那么多的inner join
select * from table1 1,table2 2 .....
8.pivot和unpivot行转列和列转行
with t as( SELECT P.Date_Month,P.Supplier,P.data FROM ( select Date_Month,sum(d1) as d1, sum(d2) as d2,sum(d3) as d3,sum(d4) as d4,sum(d5) as d5,sum(d6) as d6,sum(d7) as d7,sum(d8) as d8,sum(d9) as d9,sum(d10) as d10,sum(d11) as d11,sum(d12) as d12,sum(d13) as d13,sum(d14) as d14,sum(d15) as d15,sum(d16) as d16,sum(d17) as d17,sum(d18) as d18,sum(d19) as d19,sum(d20) as d20, sum(d21) as d21,sum(d22) as d22,sum(d23) as d23,sum(d24) as d24,sum(d25) as d25,sum(d26) as d26,sum(d27) as d27,sum(d28) as d28,sum(d29) as d29,sum(d30) as d30,sum(d31) as d31 from Web_MonthData_FuTianWasteWaterTreatmentPlant where ID in (40204,40218) group by Date_Month )T UNPIVOT ( data FOR Supplier IN (d1, d2,d3,d4,d5,d6,d7,d8,d9,d10,d11,d12,d13,d14,d15,d16,d17,d18,d19,d20, d21,d22,d23,d24,d25,d26,d27,d28,d29,d30,d31 ) ) P ) select replace(Date_Month+RIGHT(replace(supplier,'d','0'),2),'-','') as datekey,'福田水质净化厂' as plantname, '一期' as divisionname,data as ItemValue,'预处理区用电' as 电种类 from t
9.筛选某列只要数字的(如果=1说明是数字类型,如果=0说明不是数字类型)
where dbo.isReallyNumeric(b.daydata)=1
10.SSIS抽数据的时候做增量,这个应该不属于sql的知识范畴,暂且写在这里吧
t1.ItemDate>=isnull((select ConfiguredValue from dbo.[SSIS Configurations] where configurationfilter='updatedate'),'2008-01-01')
11.sql表里的数据生成insert脚本
12.取得一个表的所有列名,但是不包括XX (BENG_TB是表名)
select name from syscolumns where id=object_id('BENG_TB') and name not in ('DATE','TIME')
需要注意的是 ,object_id里面必须写表名,你写数据库.dbo.表名都不行,所以这东西只能在该表存在的数据库中使用了
13.SQL行列转换中的IN不能写select语句怎么办?动态SQL可以实现!
declare @sql nvarchar(4000) select @sql=isnull(@sql+',','')+quotename(Name) from syscolumns where id=object_id('BENG_TB') and name not in ('DATE','TIME') declare @sqlstr varchar(4000) set @sqlstr='select a.*,b.NodeID,b.TagName,301 as FactoryID from ( SELECT rtrim(convert(varchar,replace([Date],''/'',''-'')))+'' ''+ ltrim(convert(varchar,[Time])) [DateTime] ,P.SupplyNum,p.Supplier FROM ( SELECT top 1 * FROM [QY_DATA].[dbo].[BENG_TB] )T UNPIVOT ( SupplyNum FOR Supplier IN ('+@sql+') ) P) a inner join Realtime5.dbo.Web_Node b on a.Supplier=b.TagName ' exec(@sqlstr)
动态SQL,屌屌的~
14.order by多个字段排序(因为他们是字符格式,所以我都转换了一下格式)
order by CONVERT(datetime,DATE,120) desc,CONVERT(datetime,TIME,120) desc
15.多表联表更新
update 表1 set Name=a.TagDescription from 表2 a where ID=a.NodeID
还可以这样
update a set a.h0=b.h0,a.h1=b.h1,a.h2=b.h2,a.h3=b.h3,a.h4=b.h4,a.h5=b.h5,a.h6=b.h6,a.h7=b.h7, a.h8=b.h8,a.h9=b.h9,a.h10=b.h10,a.h11=b.h11,a.h12=b.h12,a.h13=b.h13,a.h14=b.h14, a.h15=b.h15,a.h16=b.h16,a.h17=b.h17,a.h18=b.h18,a.h19=b.h19,a.h20=b.h20,a.h21=b.h21, a.h22=b.h22,a.h23=b.h23 from [IPMLBZ].[PMIS5].[dbo].Web_daydata a inner join [PMIS5].[dbo].Web_daydata b on a.id = b.ID and a.ID= 23459 and a.date_day= b.date_day and a.Date_Day= '2018-08-27'
但是这里需要注意一点,a.ID=这里,貌似不能用in (1,2,3),不能使用in,只能用等于号。这个不清楚为什么。
16.获取当前日期并判断是星期几
select case datepart(dw,getdate()) when 1 then '星期天 ' when 2 then '星期一 ' when 3 then '星期二 ' when 4 then '星期三 ' when 5 then '星期四 ' when 6 then '星期五 ' when 7 then '星期六 ' end
但是上面这个方法很麻烦,这里有一个更简单的方法来判断输入的日期是周几
select datename(weekday,'2018-06-01')
17.数据库有的时候,我新建的表打不出来?说不存在?
ctrl+shift+R 按这个键,就会刷新,就可以打出表名了,还不行的话你多按几下....
18.偷师学艺,数据库53错误
在与 SQL Server 建立连接时出现与网络相关的或特定于实例的错误。未找到或无法访问服务器。请验证实例名称是否正确并且 SQL Server 已配置为允许远程连接。 (provider: 命名管道提供程序, error: 40 - 无法打开到 SQL Server 的连接)
可以按照下面的步骤来做,就好了
打开Sql server 管理配置器或者在命令行输入:SQLServerManager10.msc
然后重启一个sql server的服务,重新打开SQL Server Management Studio,在服务器名称输入:(local)或者127.0.0.1,即可登录数据库了。
19.根据in()里面的数据进行排序,也就是固定排序
我查询的时候in()了一些数字,但是查出来之后自动排序了。我不想排序,可以使用charindex()方法,如下:
select * from AnalysisData where ItemID in( 11002,114101,214011 ) Order By charindex(','+RTRIM(cast(ItemID as varchar(10)))+ ',',',11002,114101,214011,')
这样就可以按照in里面的数字进行排序了
20.接着19讲,Order by这里我又理解了一下
Order by应该只能用在语句的最后,例如:
select '当前' h,charindex(','+RTRIM(cast(winame as varchar(100)))+ ',',',pH值,浑浊度,臭和味,氨氮,游离余氯,总余氯,细菌总数,总大肠菌群,铝') ,* from [dbo].[WaterQuality] (@date1,@date2) where winame in('pH值','浑浊度','臭和味','氨氮','游离余氯','总余氯','细菌总数','总大肠菌群','铝') union select '同比' h,charindex(','+RTRIM(cast(winame as varchar(100)))+ ',',',pH值,浑浊度,臭和味,氨氮,游离余氯,总余氯,细菌总数,总大肠菌群,铝') ,* from [dbo].[WaterQuality] (Convert(varchar(10),(Dateadd(YEAR,-1,@date1)),23),Convert(varchar(10),(Dateadd(YEAR,-1,@date2)),23)) where winame in('pH值','浑浊度','臭和味','氨氮','游离余氯','总余氯','细菌总数','总大肠菌群','铝') union select '环比' h,charindex(','+RTRIM(cast(winame as varchar(100)))+ ',',',pH值,浑浊度,臭和味,氨氮,游离余氯,总余氯,细菌总数,总大肠菌群,铝') ,* from [dbo].[WaterQuality] (Convert(varchar(10),(Dateadd(MONTH,-1,@date1)),23),Convert(varchar(10),(Dateadd(MONTH,-1,@date2)),23)) where winame in('pH值','浑浊度','臭和味','氨氮','游离余氯','总余氯','细菌总数','总大肠菌群','铝') order by h,charindex(','+RTRIM(cast(winame as varchar(100)))+ ',',',pH值,浑浊度,臭和味,氨氮,游离余氯,总余氯,细菌总数,总大肠菌群,铝')
我原来三个语句,当前,同比,环比都写了Order by 但是就不能union了, 所以我觉得Order by是应该写在最后的。然后我排序的固定内容也写在了select后面才得以实行
21.判断除数不能为0
Sql Server好像没有判断是否为0的函数,只有一个NULLIF可以用一下,这个是判断如果是0就设置为NULL,还可以用
1/NULLIF(列名,0)
22. 一个表的数据查询结果插入另外一个表
insert into VaeDB.dbo.A(Name) select Name from B group by Name
23. Sql Server修改查询结果
使用编辑查询结果的方式,适合小数据量的时候,有时候不想写update语句
先在表上点击编辑前200行,然后选择显示SQL窗格,如图
这个时候直接输入sql语句,查询的结果可以在下面直接修改,少量数据还是很方便的
24.去除所有重复的数据,或者得到所有不重复的数据,使用的是group by和having的组合搭配,巧妙的是min函数,这样重复的数据我只拿第一个,这不就不重复了吗
网上全都是介绍distinct的,这个distinct不好用,只能看一列数据,没用而且效率很低
--这个是所有不重复的数据 select ReplaceProductName, min(ProductAlterId) as minProductAlterId from ProductAlter group by ReplaceProductName having COUNT(ReplaceProductName)>=1 order by minProductAlterId --这个是所有重复的数据 select * from ProductAlter a inner join ( select ReplaceProductName, min(ProductAlterId) as minProductAlterId from ProductAlter group by ReplaceProductName having COUNT(ReplaceProductName)>1 ) as aa on a.ReplaceProductName=aa.ReplaceProductName and a.ProductAlterId<>aa.minProductAlterId order by a.ProductAlterId
25. 去除字段中所有的空格
单纯的使用Ltrim或者Rtrim有时候是不行的,因为空格中存在换行符,回车等,所以要先替换掉这些特殊字符,再去除空格
LTRIM(RTRIM(REPLACE(REPLACE( 字段A,char(13),''),char(10),'') ))