常用的SQL语句

1.where convert(varchar(10),lurutime,120)='2021-01-26' 去掉秒、分 ，按照时间去查询的SQL语句

CONVERT(varchar(7), end_time, 120 )保留年月

2. cy.sqlSelectExecuteScalar("select MAX(shangxianTime) from shengchanjihua20nei"); 选择最大最小数字

3. DATEADD(day, -3, 字段) AS 字段 字段 时间加减

select jianh,sum(xuqiu)as xuqiu from dianchibaoquejianku where dianchibaoquejianku.jianh not in (select jianhao from safekucun where 1=1 ) group by jianh

4. 字段 like 'PSM24W%'

1.?LIKE'Mi%' 将搜索以字母 Mi开头的所有字符串（如 Michael）。

? ?

2. LIKE'%er' 将搜索以字母 er 结尾的所有字符串（如 Worker、Reader）。

? ?

3. LIKE'%en%' 将搜索在任何位置包含字母 en 的所有字符串（如 When、Green）。

5.select CONVERT(varchar(7) ,ddjftime, 120) as 'Date' from xiaoshoudingdanluru 将年月日截取为年月

6. CONVERT (nvarchar(12),GETDATE(),112) 当前时间

7.shangxianTime<= dateadd(day,2,CONVERT (nvarchar(12),GETDATE(),112))and shangxianTime>=CONVERT (nvarchar(12),GETDATE(),112) 当前时间加2天

8.select distinct jianh,cangkukucun.state,kucunshu,jianhao,shuliang,bomnew.ljname, kucunshu,(shuliang*'" + sl + "') as xuqiu ,lockdata,(kucunshu+zaitucaigou-lockdata)as qj,zaitucaigou from cangkukucun right join bomnew on cangkukucun.jianh=jianhao where bomnew.jhzjianhao ='" + jhz + "' and bomnew.state='正常' and beizhu in('箱体','系统自动合成总成号') and jianhao = jianhao and bomnew.jhstate in('旧件','','替换已生效') order by cangkukucun.state asc";

}

9.delete from chengpinku where jiqinum in(select jiqinum from chengpinku group by jiqinum having COUNT(jiqinum)>1) and id not in (select max(id) from chengpinku group by jiqinum having COUNT(jiqinum)>1) 去重

10. select * from libom where shuliang<>round(shuliang,0) 查询小数点的结果

11.批量更新字段中某一个字符串

UPDATE jiqinumku set jiqinum = replace(jiqinum,'无电芯','G') WHERE jhzjianhao='3001075875'

12.一张表复制到另一张表中

insert into 数据库A.dbo.table1(col1,col2,col3...) select col1,col2,col3... from 数据库B.dbo.table2

13.批量复制update字段

update t set gongxuname=(select gongxuname from libom where jhzjianhao='3000399438' and worknum='5530'and t.jianhao =libom.jianhao) from libom t where jhzjianhao='3000399438' and worknum='5545'

14.获取插入id ;select @@IDENTITY