SQL语句集合

记录下平时写的稍微复杂点的sql

#说明 A B是一对多关系 一个A有多个B 查询A中已有B的且状态不是11020403的A数据select * from A where id  not in(select id  from B where CP_STATUS <> '11020403' group by id) and  id in (select id from B group by id) --exists和1 in效果相同SELECT * FROM  AWHERE EXISTS(SELECT 1 FROM B WHERE id=A.id)AND NOT EXISTS (SELECT 1 FROM B WHERE id=A.id AND CP_STATUS<>'11020403')

sql执行时间

declare @v_a datetime;declare @v_b datetime;set @v_a= getdate();select * from A;set  @v_b = getdate();select datediff(ms,@v_a,@v_b) as '用时/毫秒'发现exists比in要快那么一点

#存储过程里的赋值语句，当结果有多条时，赋值的是最后一条的值SELECT @V_NEW_NAME = PARAM_NAME FROM QLC_TINTPARAM WHERE PARAM_VALUE = '3'

当用group by后想查结果的条数，发现count(*)是计算每组的条数
用select count(distinct TRADE_NO) from B 能得到组数

#sqlserver中可用case when来实现结果转换(CASE WHEN A.VERIFY_STATE = 0 THEN N'未核对' WHEN A.VERIFY_STATE = 1 THEN  N'已核对' ELSE N'核对有误' END)  AS VERIFY_STATE_NAM

--判断是否有字段 来加字段IF NOT EXISTS(SELECT 1 FROM syscolumns WHERE id = OBJECT_ID('ABCD') AND name = 'FLOW_ID')        ALTER TABLE ABCD ADD FLOW_ID int nullGO--新增视图IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'V_QLC')    DROP VIEW V_QLCGOCREATE VIEW  V_QLCAS

#插入数据并返回主键strSQL = "INSERT INTO tablename (name) VALUES (@name);SELECT @@Identity"