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  A
WHERE 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 null
GO

--新增视图
IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'V_QLC')
    DROP VIEW V_QLC
GO
CREATE VIEW  V_QLC
AS

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

SELECT D.*, (SELECT CAST(LIST_ID AS NVARCHAR(30))+','
                        FROM QLC_TINAPPLYLIST A, QLC_TCONTRACT_LOAN B, QLC_TCONTRACT C
                            WHERE A.LOAN_ID = B.LOAN_ID AND A.CONTRACT_ID = C.CONTRACT_ID AND A.DEAL_FLAG = 1 AND A.PROBLEM_ID ='ue3df3a1063411e8a3be2c337a1a91d6'
                                AND B.PRODUCT_ID = D.PRODUCT_ID AND A.CONTRACT_ID = D.CONTRACT_ID AND A.TRADE_NO = D.TRADE_NO AND A.ACC_DATE = D.ACC_DATE
                            FOR XML PATH('')) LIST_ID_STR FROM 
            (SELECT B.PRODUCT_ID, A.CONTRACT_ID, A.TRADE_NO, A.ACC_DATE, ISNULL(C.TOLL_FLAG,0) as TOLL_FLAG ,SUM(ISNULL(A.ACC_MONEY,0)) ACC_MONEY,SUM(ISNULL(A.TAX_BASE,0)) TAX_BASE,SUM(ISNULL(A.TAX,0)) TAX
                    FROM QLC_TINAPPLYLIST A, QLC_TCONTRACT_LOAN B, QLC_TCONTRACT C
                        WHERE A.LOAN_ID = B.LOAN_ID AND A.CONTRACT_ID = C.CONTRACT_ID  AND A.PROBLEM_ID = 'ue3df3a1063411e8a3be2c337a1a91d6'
                        GROUP BY B.PRODUCT_ID, A.CONTRACT_ID, A.TRADE_NO, A.ACC_DATE,C.TOLL_FLAG) D

– FOR XML PATH(”)) LIST_ID_STR ,能得到不groupby 参与的字符串集合