UC知道ACCESS语句转为MSSQL语句
来源：百度知道 编辑：UC知道 时间：2024/05/11 05:10:14
ACCESS查询语句如下:
select * from (SELECT IIf(Right([DM],4)="0000","000000",IIf(Left([dm],2)="01","010000",IIf(Right([dm],2)="00",Left([dm],2)+"0000",Left([dm],4)+"00"))) AS parentCode, XZQH.DM AS code, XZQH.MC AS name FROM XZQH) order by parentCode,code
参照KarasCanvas的提示转换过来了
当然,也谢谢 3306263wb 的热心回答！
select * from (SELECT IIf(Right([DM],4)="0000","000000",IIf(Left([dm],2)="01","010000",IIf(Right([dm],2)="00",Left([dm],2)+"0000",Left([dm],4)+"00"))) AS parentCode, XZQH.DM AS code, XZQH.MC AS name FROM XZQH) order by parentCode,code
参照KarasCanvas的提示转换过来了
当然,也谢谢 3306263wb 的热心回答！
IIF(条件表达式，表达式1，表达式2)
MSSQL里是
case when 条件表达式 then 表达式1 else 表达式2 end
SELECT * FROM
(SELECT
(CASE WHEN (RIGHT([DM],4)='0000') THEN '0000' ELSE '000000' END) AS [DM]
,(CASE WHEN (LEFT([dm],2)='01') THEN '01' ELSE '010000' END) AS [dm]
,(CASE WHEN (RIGHT([dm],2)='00') THEN (LEFT([dm],2)+'0000') ELSE (LEFT([dm],4)+'00') END) AS [PARENTCODE]
,XZQH.DM AS [CODE]
,XZQH.MC AS [NAME]
FROM XZQH) AS NEWTABLE
ORDER BY [PARENTCODE],[CODE]