记录下平时写的稍微复杂点的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 参与的字符串集合