有一存储过程需要从某一视图中筛选出大量数据并做分组聚合,过滤条件写在存储过程里好还是用过滤条件创建一新的视图给存储过程用好?比如
SELECT RTRIM(JC_DEPT_PROPERTY.NAME) AS 科室, RTRIM(VI_ZY_FEE_SPECI.ITEM_NAME) AS 项目, SUM(VI_ZY_FEE_SPECI.NUM) AS 数量,
RTRIM(VI_ZY_FEE_SPECI.UNIT) AS 单位, JC_HSITEM.xishu AS 系数, ROUND(SUM(VI_ZY_FEE_SPECI.NUM)*JC_HSITEM.xishu,2) AS 小计
FROM JC_DEPT_PROPERTY INNER JOIN
VI_ZY_FEE_SPECI ON JC_DEPT_PROPERTY.DEPT_ID = VI_ZY_FEE_SPECI.EXECDEPT_ID INNER JOIN
JC_HSITEM ON VI_ZY_FEE_SPECI.XMID = JC_HSITEM.ITEM_ID
WHERE (VI_ZY_FEE_SPECI.XMLY = 2) AND (VI_ZY_FEE_SPECI.CHARGE_DATE BETWEEN @RQ1 AND @RQ2) AND
(VI_ZY_FEE_SPECI.EXECDEPT_ID = @deptid) AND (VI_ZY_FEE_SPECI.XMID IN (1680, 449, 1351, 1709, 454, 456, 457, 1355, 914, 447, 3111, 527,
1677, 1678, 1342, 1365, 982, 1341, 538, 529, 536, 1339, 1338, 532, 774, 712, 105, 1143, 1066, 3362, 1335, 1334, 980, 981, 450, 832, 453, 831, 560,
562, 1063, 1061, 1062, 1074, 898, 899, 900, 1332, 3226, 452, 1193, 644, 524, 1065, 647, 403, 130, 3111, 3112, 528, 985, 983))
GROUP BY VI_ZY_FEE_SPECI.XMID, VI_ZY_FEE_SPECI.ITEM_NAME, JC_DEPT_PROPERTY.NAME, VI_ZY_FEE_SPECI.UNIT, JC_HSITEM.xishu里面的WHERE (VI_ZY_FEE_SPECI.XMLY = 2) AND (VI_ZY_FEE_SPECI.XMID IN (1680, 449, 1351, 1709, 454, 456, 457, 1355, 914, 447, 3111, 527, 1677, 1678, 1342, 1365, 982, 1341, 538, 529, 536, 1339, 1338, 532, 774, 712, 105, 1143, 1066, 3362, 1335, 1334, 980, 981, 450, 832, 453, 831, 560, 562, 1063, 1061, 1062, 1074, 898, 899, 900, 1332, 3226, 452, 1193, 644, 524, 1065, 647, 403, 130, 3111, 3112, 528, 985, 983))是新建视图处理还是就放在存储过程里?
SELECT RTRIM(JC_DEPT_PROPERTY.NAME) AS 科室, RTRIM(VI_ZY_FEE_SPECI.ITEM_NAME) AS 项目, SUM(VI_ZY_FEE_SPECI.NUM) AS 数量,
RTRIM(VI_ZY_FEE_SPECI.UNIT) AS 单位, JC_HSITEM.xishu AS 系数, ROUND(SUM(VI_ZY_FEE_SPECI.NUM)*JC_HSITEM.xishu,2) AS 小计
FROM JC_DEPT_PROPERTY INNER JOIN
VI_ZY_FEE_SPECI ON JC_DEPT_PROPERTY.DEPT_ID = VI_ZY_FEE_SPECI.EXECDEPT_ID INNER JOIN
JC_HSITEM ON VI_ZY_FEE_SPECI.XMID = JC_HSITEM.ITEM_ID
WHERE (VI_ZY_FEE_SPECI.XMLY = 2) AND (VI_ZY_FEE_SPECI.CHARGE_DATE BETWEEN @RQ1 AND @RQ2) AND
(VI_ZY_FEE_SPECI.EXECDEPT_ID = @deptid) AND (VI_ZY_FEE_SPECI.XMID IN (1680, 449, 1351, 1709, 454, 456, 457, 1355, 914, 447, 3111, 527,
1677, 1678, 1342, 1365, 982, 1341, 538, 529, 536, 1339, 1338, 532, 774, 712, 105, 1143, 1066, 3362, 1335, 1334, 980, 981, 450, 832, 453, 831, 560,
562, 1063, 1061, 1062, 1074, 898, 899, 900, 1332, 3226, 452, 1193, 644, 524, 1065, 647, 403, 130, 3111, 3112, 528, 985, 983))
GROUP BY VI_ZY_FEE_SPECI.XMID, VI_ZY_FEE_SPECI.ITEM_NAME, JC_DEPT_PROPERTY.NAME, VI_ZY_FEE_SPECI.UNIT, JC_HSITEM.xishu里面的WHERE (VI_ZY_FEE_SPECI.XMLY = 2) AND (VI_ZY_FEE_SPECI.XMID IN (1680, 449, 1351, 1709, 454, 456, 457, 1355, 914, 447, 3111, 527, 1677, 1678, 1342, 1365, 982, 1341, 538, 529, 536, 1339, 1338, 532, 774, 712, 105, 1143, 1066, 3362, 1335, 1334, 980, 981, 450, 832, 453, 831, 560, 562, 1063, 1061, 1062, 1074, 898, 899, 900, 1332, 3226, 452, 1193, 644, 524, 1065, 647, 403, 130, 3111, 3112, 528, 985, 983))是新建视图处理还是就放在存储过程里?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货