PROC集计算
来源：互联网 发布：python isupper函数 编辑：程序博客网 时间：2024/06/05 09:42
--proc_xnh_mzbzjdbb '2014','*'CREATE PROC proc_xnh_mzbzjdbb ( @nf VARCHAR(4) , @yf VARCHAR(2) )AS IF NULLIF('*', @yf) IS NOT NULL BEGIN-- 1 直接可用字段 次均门诊费用（jmzfy），次均门诊可报费用（jmzkbfy），--门诊补助金额占门诊费用比例（bzzjebl），次均补助金额（jbzje），--处理字段 补助金额 （补助金额占旗县区门诊总补助金额比例）-- group by B.XZQBH 行政区,A.JGJB 机构级别 均门诊费用 均门诊可报费用 补助金额占总金额比例 均补助金额 补助金额 SELECT ISNULL(A.JGJB, '0') AS JGJB , B.XZQBH , SUM(MZZJE) / COUNT(XZQBH) AS JMZFY , SUM(KBCJE) / COUNT(XZQBH) AS JMZKBFY , SUM(SJBCJE) / SUM(MZZJE) BZZJEBL , SUM(SJBCJE) / COUNT(XZQBH) AS JBZJE , SUM(SJBCJE) AS BZJE FROM ( SELECT A.ZYJGBH , A.YLGMC , A.XZQBH FROM SKJBXX_XZQ B LEFT JOIN NHXX_YLJG A ON ( SUBSTRING(a.XZQBH, 1, 4) = b.GJBH ) WHERE B.GJBH LIKE '1508__' OR B.GJBH = '1508' ) AS B LEFT JOIN NHXX_MZFYXX A ON A.MZJG = B.ZYJGBH WHERE DATEPART(year, MZRQ) = @nf AND DATEPART(MONTH, MZRQ) = @yf GROUP BY B.XZQBH , A.JGJB ORDER BY B.XZQBH ASC-- 2 行政区划分 总的补助金额（与上结果集1处理） SELECT B.XZQBH , SUM(SJBCJE) AS ZSJBZJE FROM ( SELECT A.ZYJGBH , A.YLGMC , A.XZQBH FROM SKJBXX_XZQ B LEFT JOIN NHXX_YLJG A ON ( SUBSTRING(a.XZQBH, 1, 4) = b.GJBH ) WHERE B.GJBH LIKE '1508__' OR B.GJBH = '1508' ) AS B LEFT JOIN NHXX_MZFYXX A ON A.MZJG = B.ZYJGBH WHERE DATEPART(year, MZRQ) = @nf AND DATEPART(MONTH, MZRQ) = @yf AND A.SJBCJE IS NOT NULL AND a.SJBCJE > 0 GROUP BY B.XZQBH ORDER BY B.XZQBH ASC-- 3 行政区机构级别划分 补助人数 SELECT a.JGJB , B.XZQBH , COUNT(DISTINCT RYBH) AS RS FROM ( SELECT A.ZYJGBH , A.YLGMC , A.XZQBH FROM SKJBXX_XZQ B LEFT JOIN NHXX_YLJG A ON ( SUBSTRING(a.XZQBH, 1, 4) = b.GJBH ) WHERE B.GJBH LIKE '1508__' OR B.GJBH = '1508' ) AS B LEFT JOIN NHXX_MZFYXX A ON A.MZJG = B.ZYJGBH WHERE DATEPART(year, MZRQ) = @nf AND DATEPART(MONTH, MZRQ) = @yf AND A.SJBCJE IS NOT NULL AND a.SJBCJE > 0 GROUP BY B.XZQBH , a.JGJB ORDER BY B.XZQBH ASC-- 4 行政区划分 补助人数（结果集3 和 4进行运算 得出 补助人数占旗县区总补助人数） SELECT B.XZQBH , COUNT(DISTINCT RYBH) AS RS FROM ( SELECT A.ZYJGBH , A.YLGMC , A.XZQBH FROM SKJBXX_XZQ B LEFT JOIN NHXX_YLJG A ON ( SUBSTRING(a.XZQBH, 1, 4) = b.GJBH ) WHERE B.GJBH LIKE '1508__' OR B.GJBH = '1508' ) AS B LEFT JOIN NHXX_MZFYXX A ON A.MZJG = B.ZYJGBH WHERE DATEPART(year, MZRQ) = @nf AND DATEPART(MONTH, MZRQ) = @yf AND A.SJBCJE IS NOT NULL AND a.SJBCJE > 0 GROUP BY B.XZQBH ORDER BY B.XZQBH ASC-- 5 行政区 --合计数据--直接可用字段 次均门诊费用（jmzfy），次均门诊可报费用（jmzkbfy），--门诊补助金额占门诊费用比例（bzzjebl），次均补助金额（jbzje）， SELECT B.XZQBH , SUM(MZZJE) / COUNT(XZQBH) AS JMZFY , SUM(KBCJE) / COUNT(XZQBH) AS JMZKBFY , SUM(SJBCJE) / SUM(MZZJE) BZZJEBL , SUM(SJBCJE) / COUNT(XZQBH) AS JBZJE FROM ( SELECT A.ZYJGBH , A.YLGMC , A.XZQBH FROM SKJBXX_XZQ B LEFT JOIN NHXX_YLJG A ON ( SUBSTRING(a.XZQBH, 1, 4) = b.GJBH ) WHERE B.GJBH LIKE '1508__' OR B.GJBH = '1508' ) AS B LEFT JOIN NHXX_MZFYXX A ON A.MZJG = B.ZYJGBH WHERE DATEPART(year, MZRQ) = @nf AND DATEPART(MONTH, MZRQ) = @yf GROUP BY B.XZQBH ORDER BY B.XZQBH ASC-- 6 根据gjbh(国家编号)来group by 得出不同行政区的参合总人数--集4/集6 旗县区补助人数占参合人数比例 SELECT XZQBH , COUNT(XZQBH) CHRS FROM dbo.NHXX_CHCYXX a LEFT JOIN dbo.NHXX_CHJTXX b ON a.JTBH = b.JTBH GROUP BY XZQBH--7 国家编号 与 行政区名称对照字典 SELECT GJBH , XZQMC FROM SKJBXX_XZQ WHERE GJBH LIKE '1508__' OR GJBH = '1508' ORDER BY GJBH ASC END--月份为‘*’查询累计数据 ELSE BEGIN -- 1 直接可用字段 次均门诊费用（jmzfy），次均门诊可报费用（jmzkbfy），--门诊补助金额占门诊费用比例（bzzjebl），次均补助金额（jbzje），--处理字段 补助金额 （补助金额占旗县区门诊总补助金额比例）-- group by B.XZQBH 行政区,A.JGJB 机构级别 均门诊费用 均门诊可报费用 补助金额占总金额比例 均补助金额 补助金额 SELECT ISNULL(A.JGJB, '0') AS JGJB , B.XZQBH , SUM(MZZJE) / COUNT(XZQBH) AS JMZFY , SUM(KBCJE) / COUNT(XZQBH) AS JMZKBFY , SUM(SJBCJE) / SUM(MZZJE) BZZJEBL , SUM(SJBCJE) / COUNT(XZQBH) AS JBZJE , SUM(SJBCJE) AS BZJE FROM ( SELECT A.ZYJGBH , A.YLGMC , A.XZQBH FROM SKJBXX_XZQ B LEFT JOIN NHXX_YLJG A ON ( SUBSTRING(a.XZQBH, 1, 4) = b.GJBH ) WHERE B.GJBH LIKE '1508__' OR B.GJBH = '1508' ) AS B LEFT JOIN NHXX_MZFYXX A ON A.MZJG = B.ZYJGBH WHERE DATEPART(year, MZRQ) = @nf GROUP BY B.XZQBH , A.JGJB ORDER BY B.XZQBH ASC-- 2 行政区划分 总的补助金额（与上结果集1处理） SELECT B.XZQBH , SUM(SJBCJE) AS ZSJBZJE FROM ( SELECT A.ZYJGBH , A.YLGMC , A.XZQBH FROM SKJBXX_XZQ B LEFT JOIN NHXX_YLJG A ON ( SUBSTRING(a.XZQBH, 1, 4) = b.GJBH ) WHERE B.GJBH LIKE '1508__' OR B.GJBH = '1508' ) AS B LEFT JOIN NHXX_MZFYXX A ON A.MZJG = B.ZYJGBH WHERE DATEPART(year, MZRQ) = @nf AND A.SJBCJE IS NOT NULL AND a.SJBCJE > 0 GROUP BY B.XZQBH ORDER BY B.XZQBH ASC-- 3 行政区机构级别划分 补助人数 SELECT a.JGJB , B.XZQBH , COUNT(DISTINCT RYBH) AS RS FROM ( SELECT A.ZYJGBH , A.YLGMC , A.XZQBH FROM SKJBXX_XZQ B LEFT JOIN NHXX_YLJG A ON ( SUBSTRING(a.XZQBH, 1, 4) = b.GJBH ) WHERE B.GJBH LIKE '1508__' OR B.GJBH = '1508' ) AS B LEFT JOIN NHXX_MZFYXX A ON A.MZJG = B.ZYJGBH WHERE DATEPART(year, MZRQ) = @nf AND A.SJBCJE IS NOT NULL AND a.SJBCJE > 0 GROUP BY B.XZQBH , a.JGJB ORDER BY B.XZQBH ASC-- 4 行政区划分 补助人数（结果集3 和 4进行运算 得出 补助人数占旗县区总补助人数） SELECT B.XZQBH , COUNT(DISTINCT RYBH) AS RS FROM ( SELECT A.ZYJGBH , A.YLGMC , A.XZQBH FROM SKJBXX_XZQ B LEFT JOIN NHXX_YLJG A ON ( SUBSTRING(a.XZQBH, 1, 4) = b.GJBH ) WHERE B.GJBH LIKE '1508__' OR B.GJBH = '1508' ) AS B LEFT JOIN NHXX_MZFYXX A ON A.MZJG = B.ZYJGBH WHERE DATEPART(year, MZRQ) = @nf AND A.SJBCJE IS NOT NULL AND a.SJBCJE > 0 GROUP BY B.XZQBH ORDER BY B.XZQBH ASC-- 5 行政区 --合计数据--直接可用字段 次均门诊费用（jmzfy），次均门诊可报费用（jmzkbfy），--门诊补助金额占门诊费用比例（bzzjebl），次均补助金额（jbzje）， SELECT B.XZQBH , SUM(MZZJE) / COUNT(XZQBH) AS JMZFY , SUM(KBCJE) / COUNT(XZQBH) AS JMZKBFY , SUM(SJBCJE) / SUM(MZZJE) BZZJEBL , SUM(SJBCJE) / COUNT(XZQBH) AS JBZJE FROM ( SELECT A.ZYJGBH , A.YLGMC , A.XZQBH FROM SKJBXX_XZQ B LEFT JOIN NHXX_YLJG A ON ( SUBSTRING(a.XZQBH, 1, 4) = b.GJBH ) WHERE B.GJBH LIKE '1508__' OR B.GJBH = '1508' ) AS B LEFT JOIN NHXX_MZFYXX A ON A.MZJG = B.ZYJGBH WHERE DATEPART(year, MZRQ) = @nf GROUP BY B.XZQBH ORDER BY B.XZQBH ASC-- 6 根据gjbh(国家编号)来group by 得出不同行政区的参合总人数--集4/集6 旗县区补助人数占参合人数比例 SELECT XZQBH , COUNT(XZQBH) CHRS FROM dbo.NHXX_CHCYXX a LEFT JOIN dbo.NHXX_CHJTXX b ON a.JTBH = b.JTBH GROUP BY XZQBH--7 国家编号 与 行政区名称对照字典 SELECT GJBH , XZQMC FROM SKJBXX_XZQ WHERE GJBH LIKE '1508__' OR GJBH = '1508' ORDER BY GJBH ASC END 0 0
- PROC集计算
- linux /proc/stat 计算CPU利用率
- linux/proc/stat计算cpu利用率
- linux下/proc/stat 计算CPU利用率
- linux下/proc/stat 计算CPU利用率
- linux下/proc/stat 计算CPU利用率
- linux /proc/stat 计算CPU利用率
- linux下/proc/stat 计算CPU利用率
- 用/proc/stat计算cpu的占用率
- linux下/proc/stat 计算CPU利用率
- 用/proc/stat计算cpu的占用率
- linux下/proc/stat 计算CPU利用率
- linux下/proc/stat 计算CPU利用率
- 用/proc/stat计算cpu的占用率
- 用/proc/stat计算cpu的占用率
- proc
- proc
- /proc
- google-play-services_lib添加不成功的解决方案
- iOS collectionview小结
- java获取ip地址
- java字符编码转换研究（转）
- 代码的运行顺序总结
- PROC集计算
- WebForms UnobtrusiveValidationMode 需要“jquery”ScriptResourceMapping。请添加一个名为 jquery (区分大小写)的 ScriptRes
- 目标黑马，我的自学之路：第七章 字符串和字符串缓冲区
- 说说Python程序的执行过程
- hdu 5064 Find Sequence（单调性优化DP）
- 从TS流到PAT和PMT
- 数据科学家面试常见的77个问题
- 编程之美2.15 二维数组最大子数组的和(数组下标从(1,1)开始)
- C文件读写函数介绍
