22,210
社区成员
发帖
与我相关
我的任务
分享
create VIEW [dbo].[view_T_J_DJXMJFMX]
AS
select a.id,us.id usid,us.user_no,us.name user_name,us.pinyin,us.pinyinFirst,e.XMJFJE,
us.user_cata,d.id YHLXBH,d.ZDYMC,dept.code dept_code,dept.name dept_name,A.XMID,B.XMMC,
cast(CONVERT(CHAR(10),A.FYRQ,120)as varchar(10)) AS FYRQ,
e.id as ZFFSID, e.ZFFSBH,e.ZFFSMC, CONVERT(VARCHAR, e.CZRQ, 120) JFRQ,
CASE SIGN(e.XMJFJE) WHEN - 1 THEN '退费' ELSE '缴费' END AS JFBZ,a.org_id ,org.name as org_name
from T_J_DJFYB a
inner join sys_organization org on org.id = a.org_id
inner join T_J_DJXMB b on a.xmid=b.id
INNER JOIN
(
select FYXMID,XMJFJE, ZFFSID,CZRQ,RZBZ,f.id, f.ZFFSBH,f.ZFFSMC from T_J_DJFYMXB dj
INNER JOIN T_J_DJZFFSB f ON dj.ZFFSID = f.id
union all
select FYXMID,XMHJJE as XMJFJE ,ZFFSID,CZRQ,'1',f.id, f.ZFFSBH,f.ZFFSMC as RZBZ from T_J_DJXMHJMX dm
INNER JOIN T_J_DJZFFSB f ON dm.ZFFSID = f.id
) e ON A.id = e.FYXMID
left join sys_user us on a.YHID = us.id
inner join sys_dept dept on us.dept_id = dept.id
inner join T_B_YHLXFB d on us.user_cata=d.id
inner join T_B_YHLX c on d.YHLXID=c.id
WHERE b.XMLX = '1' and e.RZBZ='1'
GO
(104330 行受影响)
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'T_J_DJXMHJMX'。扫描计数 1,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'T_J_DJFYMXB'。扫描计数 1,逻辑读取 3974 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'T_J_DJZFFSB'。扫描计数 1,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sys_dept'。扫描计数 0,逻辑读取 317952 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'T_B_YHLX'。扫描计数 0,逻辑读取 317952 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'T_B_YHLXFB'。扫描计数 0,逻辑读取 317952 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sys_user'。扫描计数 0,逻辑读取 317952 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'T_J_DJXMB'。扫描计数 0,逻辑读取 525441 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'T_J_DJFYB'。扫描计数 46,逻辑读取 998 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sys_organization'。扫描计数 1,逻辑读取 6 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
SELECT *
INTO #tmpA
FROM (
SELECT a.id,
a.FYRQ,
a.XMID,
a.org_id,
a.YHID,
FYXMID,
XMJFJE,
ZFFSID,
dj.CZRQ,
RZBZ,
f.id AS f_id,
f.ZFFSBH,
f.ZFFSMC
FROM T_J_DJFYB a
INNER JOIN T_J_DJFYMXB dj
ON dj.FYXMID = a.id
INNER JOIN T_J_DJZFFSB f
ON dj.ZFFSID = f.id
WHERE dj.RZBZ = '1'
UNION ALL
SELECT a.id,
a.FYRQ,
a.XMID,
a.org_id,
a.YHID,
FYXMID,
dm.XMHJJE AS XMJFJE,
ZFFSID,
dm.CZRQ,
'1',
f.id AS f_id,
f.ZFFSBH,
f.ZFFSMC AS RZBZ
FROM T_J_DJFYB a
INNER JOIN T_J_DJXMHJMX dm
ON dm.FYXMID = a.id
INNER JOIN T_J_DJZFFSB f
ON dm.ZFFSID = f.id
) AS t;
CREATE CLUSTERED INDEX ix_#tmpA_1 ON #tmpA(org_id,xmid,YHID);
SELECT a.id,
us.id usid,
us.user_no,
us.name USER_NAME,
us.pinyin,
us.pinyinFirst,
a.XMJFJE,
us.user_cata,
d.id YHLXBH,
d.ZDYMC,
dept.code dept_code,
dept.name dept_name,
A.XMID,
B.XMMC,
CAST(CONVERT(CHAR(10), A.FYRQ, 120)AS VARCHAR(10)) AS FYRQ,
a.f_id AS ZFFSID,
a.ZFFSBH,
a.ZFFSMC,
CONVERT(VARCHAR, a.CZRQ, 120) JFRQ,
CASE SIGN(a.XMJFJE)
WHEN - 1 THEN '退费'
ELSE '缴费'
END AS JFBZ,
a.org_id,
org.name AS org_name
FROM #tmpA a
INNER JOIN sys_organization org
ON org.id = a.org_id
INNER JOIN T_J_DJXMB b
ON a.xmid = b.id
AND b.XMLX = '1'
LEFT JOIN sys_user us
ON a.YHID = us.id
INNER JOIN sys_dept dept
ON us.dept_id = dept.id
INNER JOIN T_B_YHLXFB d
ON us.user_cata = d.id
INNER JOIN T_B_YHLX c
ON d.YHLXID = c.id
这样需要多少秒?select a.id,us.id usid,us.user_no,us.name user_name,us.pinyin,us.pinyinFirst,a.XMJFJE,
us.user_cata,d.id YHLXBH,d.ZDYMC,dept.code dept_code,dept.name dept_name,A.XMID,B.XMMC,
cast(CONVERT(CHAR(10),A.FYRQ,120)as varchar(10)) AS FYRQ,
a.f_id as ZFFSID, a.ZFFSBH,a.ZFFSMC, CONVERT(VARCHAR, a.CZRQ, 120) JFRQ,
CASE SIGN(a.XMJFJE) WHEN - 1 THEN '退费' ELSE '缴费' END AS JFBZ,a.org_id ,org.name as org_name
from (
select a.id,a.FYRQ,a.XMID,a.org_id,a.YHID,FYXMID,XMJFJE, ZFFSID,dj.CZRQ,RZBZ,f.id as f_id, f.ZFFSBH,f.ZFFSMC
from T_J_DJFYB a
inner join T_J_DJFYMXB dj on dj.FYXMID = a.id
INNER JOIN T_J_DJZFFSB f ON dj.ZFFSID = f.id
where dj.RZBZ='1'
union all
select a.id,a.FYRQ,a.XMID,a.org_id,a.YHID,FYXMID,dm.XMHJJE as XMJFJE ,ZFFSID,dm.CZRQ,'1',f.id as f_id,
f.ZFFSBH,f.ZFFSMC as RZBZ
from T_J_DJFYB a
inner join T_J_DJXMHJMX dm on dm.FYXMID = a.id
INNER JOIN T_J_DJZFFSB f ON dm.ZFFSID = f.id
)a
inner join sys_organization org on org.id = a.org_id
inner join T_J_DJXMB b on a.xmid=b.id and b.XMLX='1'
left join sys_user us on a.YHID = us.id
inner join sys_dept dept on us.dept_id = dept.id
inner join T_B_YHLXFB d on us.user_cata=d.id
inner join T_B_YHLX c on d.YHLXID=c.id