{"rsdb":{"rid":"242525","subhead":"","postdate":"0","aid":"166986","fid":"57","uid":"1","topic":"1","content":"
\n

\u63a5\u5230\u94f6\u884c\u4e1a\u52a1\u9700\u6c42\uff0c\u8981\u505a\u4e00\u4e2a\u5982\u56fe\u7684\u4e2a\u4eba\u5e74\u9f84\u533a\u95f4\u5b58\u6b3e\u7edf\u8ba1\u62a5\u8868\uff1a<\/p> \n

\"\\\"<\/p> \n

\u601d\u8def\uff1a<\/p> \n

\u53ef\u4ee5\u770b\u5230\uff0c\u8868\u5934\u662f\u5206\u7eb5\u5411\u548c\u6a2a\u5411\u7684\uff0c\u56e0\u6b64\u9996\u5148\u60f3\u5230\u7684\u662f\u9700\u8981\u8fdb\u884c\u4e24\u6b21group by\uff0c\u800c\u67e5\u8be2\u65f6\u60f3\u8981\u5c06\u5b58\u6b3e\u533a\u95f4\u5217\u4f5c\u4e3a\u7eb5\u5411\u7684\u8868\u5934\uff0c\u5e74\u9f84\u533a\u95f4\u4f5c\u4e3a\u6a2a\u5411\u8868\u5934\u8fd9\u79cd\u6837\u5f0f\uff0c\u5c31\u8981\u628a\u5b58\u6b3e\u7c7b\u522b\u548c\u5b58\u6b3e\u533a\u95f4\u5f53\u6210\u5217\u5185\u7684\u6570\u636e\u6765\u770b\uff0c\u5e76\u5c06\u8fd9\u4e24\u5217\u7684\u5b57\u6bb5\u653e\u5728\u67e5\u8be2\u7684\u524d\u4e24\u4f4d\uff08\u5982SELECT TYPE\uff0cBAL_ORANGE....\uff09\u3002<\/p> \n

\u7531\u6b64\u53ef\u4ee5\u5148\u5c06\u539f\u59cb\u6570\u636e\u6309\u7167\u5b58\u6b3e\u7684\u533a\u95f4\u548c\u7c7b\u522b\u8fdb\u884c\u4e00\u6b21\u5206\u7ec4\uff1a<\/p> \n 

\r\nSELECT TYPE, CASE WHEN BAL < 50000 THEN '5\u4e07\u5143\u4ee5\u4e0b'\r\nWHEN BAL BETWEEN 50001 AND 100000 THEN '5-10\u4e07' \r\nWHEN BAL BETWEEN 100001 AND 200000 THEN '10-20\u4e07' \r\nWHEN BAL BETWEEN 200001 AND 300000 THEN '20-30\u4e07' \r\nWHEN BAL BETWEEN 300001 AND 400000 THEN '30-40\u4e07' \r\nWHEN BAL BETWEEN 400001 AND 500000 THEN '40-50\u4e07' \r\nWHEN BAL > 500000 THEN '50\u4e07\u4ee5\u4e0a'\r\nEND AS BAL_RANGE,\r\nBAL, AGE, COUNT(*) COUNT_B\r\n\tFROM C_CUST_AGE\r\n  WHERE DEPT_ID = \uff1f\r\n  AND TYPE = \uff1f\r\n\tGROUP BY  TYPE, AGE, BAL<\/pre> \n 
(\u5176\u4e2d\u7684DEPT_ID\u53ef\u4ee5\u9488\u5bf9\u4e0d\u540c\u90e8\u95e8\u83b7\u5f97\u5bf9\u5e94\u7684\u5206\u7ec4\u8bb0\u5f55\uff0cTYPE\u4e3a\u5b58\u6b3e\u7c7b\u522b\uff0c\u4ec5\u4f5c\u4e3a\u67e5\u8be2\u6761\u4ef6\u3002)<\/p> \n 
\u67e5\u8be2\u7ed3\u679c\u5982\u4e0b\uff1a<\/p> \n 
\"\\\"<\/p> \n 
\u6b64\u65f6\u8981\u6ce8\u610f\u7684\u662fCOUNT_B\u8fd9\u4e2a\u5b57\u6bb5\uff0c\u56e0\u4e3aCASE WHEN\u8bed\u53e5\u53ea\u5bf9BAL(\u4f59\u989d)\u5b57\u6bb5\u8fdb\u884c\u4e86\u5206\u7c7b\uff0c\u5e76\u4f5c\u4e3aBAL_RANGE(\u4f59\u989d\u533a\u95f4)\u9644\u52a0\u5728\u4e86\u539f\u6765\u7684\u8868\u4e0a\uff0c\u5b9e\u9645\u4e0a\u53ea\u5bf9AGE\u5b57\u6bb5\u8fdb\u884c\u4e86\u5206\u7ec4\uff0c\u6240\u4ee5\u6b64\u65f6\u7684count_b\u5176\u5b9e\u53ea\u662f\u5f53\u524d\u5e74\u9f84\u5f53\u524d\u4f59\u989d\u7684\u5ba2\u6237\u6570\u91cf\uff0c\u6b64\u65f6\u6211\u4eec\u5df2\u7ecf\u6709\u4e86TYPE(\u5b58\u6b3e\u7c7b\u578b),BAL_RANGE(\u4f59\u989d\u533a\u95f4)\u4e24\u4e2a\u7eb5\u5411\u8868\u5934\uff0c\u53ef\u4ee5\u52a0\u5165\u6a2a\u5411\u7684\u7684\u5e74\u9f84\u533a\u95f4\u8868\u5934\u4e86\uff1a<\/p> \n 
\r\nselect type,\r\nbal_range,\r\nCASE WHEN AGE BETWEEN 0 AND 30 then sum(count_b) ELSE 0 end age_30c,\r\nCASE WHEN AGE BETWEEN 0 AND 30 then sum(count_b*bal) ELSE 0 end age_30b,\r\nCASE WHEN AGE BETWEEN 31 AND 40 then sum(count_b) ELSE 0 end age31_40c,\r\nCASE WHEN AGE BETWEEN 31 AND 40 then sum(count_b*bal) ELSE 0 end age31_40b,\r\nCASE WHEN AGE BETWEEN 41 AND 50 then sum(count_b) ELSE 0 end age41_50c,\r\nCASE WHEN AGE BETWEEN 41 AND 50 then sum(count_b*bal) ELSE 0 end age41_50b,\r\nCASE WHEN AGE BETWEEN 51 AND 60 then sum(count_b) ELSE 0 end age51_60c,\r\nCASE WHEN AGE BETWEEN 51 AND 60 then sum(count_b*bal) ELSE 0 end age51_60b,\r\nCASE WHEN AGE > 60 then sum(count_b) ELSE 0 end age60_c,\r\nCASE WHEN AGE > 60 then sum(count_b*bal) ELSE 0 end age60_b,\r\ncount(count_b) sum_count,\r\nsum(count_b*bal) sum_bal\r\n from\r\n(SELECT TYPE, CASE WHEN BAL < 50000 THEN '5\u4e07\u5143\u4ee5\u4e0b'\r\nWHEN BAL BETWEEN 50001 AND 100000 THEN '5-10\u4e07' \r\nWHEN BAL BETWEEN 100001 AND 200000 THEN '10-20\u4e07' \r\nWHEN BAL BETWEEN 200001 AND 300000 THEN '20-30\u4e07' \r\nWHEN BAL BETWEEN 300001 AND 400000 THEN '30-40\u4e07' \r\nWHEN BAL BETWEEN 400001 AND 500000 THEN '40-50\u4e07' \r\nWHEN BAL > 500000 THEN '50\u4e07\u4ee5\u4e0a'\r\nEND AS BAL_RANGE,\r\nBAL, AGE, COUNT(*) COUNT_B\r\n\tFROM C_CUST_AGE\r\n  WHERE DEPT_ID = \uff1f\r\n  AND TYPE = \uff1f\r\n\tGROUP BY  TYPE, AGE, BAL)\r\n  GROUP BY TYPE,bal_range,AGE<\/pre> \n 
\u67e5\u8be2\u7ed3\u679c\u5982\u4e0b\uff1a<\/p> \n 
\"\\\"<\/p> \u89c2\u5bdf\u6700\u524d\u9762\u90a3\u5f20\u62a5\u8868\u793a\u4f8b\u56fe\u53ef\u77e5\uff0c\u5b9e\u9645\u7684\u5b57\u6bb5\u6570\u636e\u53ef\u4ee5\u5206\u4e3a30\u5c81\u4ee5\u4e0b\u7684\u6237\u6570\u300130\u5c81\u4ee5\u4e0b\u7684\u4f59\u989d\u300130-40\u5c81\u7684\u6237\u6570\u300130-40\u5c81\u7684\u4f59\u989d······ \n 
\u7531\u4e8eCOUNT_B\u53ea\u662f\u9488\u5bf9\u540c\u4e00\u5e74\u9f84\u7684\u7528\u6237\u6570\uff0c\u6240\u4ee5<\/p> \n 
CASE WHEN AGE BETWEEN 0 AND 30 then sum(count_b) ELSE 0 end age_30c<\/p> \n 
\u8868\u793a\u5e74\u9f84\u5c0f\u4e8e30\u5c81\u7684\u6240\u6709\u7528\u6237\u6570\uff0c\u5373age_30c,\u4f46\u5982\u679cAGE\u4e0d\u5728\u5c0f\u4e8e30\u8fd9\u5217\uff0c\u5c31\u75280\u6765\u8868\u793a<\/p> \n 
\r\nCASE WHEN AGE BETWEEN 0 AND 30 then sum(count_b*bal) ELSE 0 end age_30b<\/pre> \n 
\u540c\u7406\u8868\u793a\u5e74\u9f84\u5c0f\u4e8e30\u5c81\u7684\u6240\u6709\u7528\u6237\u603b\u4f59\u989d<\/p> \n 
\u6b64\u65f6\u67e5\u8be2\u7ed3\u679c\u4f1a\u6709\u5927\u91cf\u76840\u5b58\u5728\uff0c\u662f\u56e0\u4e3a\u8fd8\u6ca1\u6709\u6309\u7167BAL_RANGE\u5b57\u6bb5\u8fdb\u884c\u5206\u7ec4\u5408\u5e76\uff0c\u6240\u4ee5\u6700\u540e\u518d\u8fdb\u884c\u4e00\u6b21\u5206\u7ec4\u5373\u53ef\uff0c\u6700\u7ec8SQL\u4ee3\u7801\u5982\u4e0b\uff1a<\/p> \n 
\r\nSELECT type,\r\nbal_range, SUM(age_30c) age_30c, sum(age_30b) age_30b, sum(age31_40c) age31_40c, sum(age31_40b) age31_40b, sum(age41_50c) age41_50c, sum(age41_50b) age41_50b,\r\nsum(age51_60c) age51_60c, sum(age51_60b) age51_60b, sum(age60_c) age60_c, sum(age60_b) age60_b,\r\nsum(sum_count) sum_count, sum(sum_bal) sum_bal\r\nFROM(select type,\r\nbal_range,\r\nCASE WHEN AGE BETWEEN 0 AND 30 then sum(count_b) ELSE 0 end age_30c,\r\nCASE WHEN AGE BETWEEN 0 AND 30 then sum(count_b*bal) ELSE 0 end age_30b,\r\nCASE WHEN AGE BETWEEN 31 AND 40 then sum(count_b) ELSE 0 end age31_40c,\r\nCASE WHEN AGE BETWEEN 31 AND 40 then sum(count_b*bal) ELSE 0 end age31_40b,\r\nCASE WHEN AGE BETWEEN 41 AND 50 then sum(count_b) ELSE 0 end age41_50c,\r\nCASE WHEN AGE BETWEEN 4","orderid":"0","title":"\u5982\u4f55\u8fd0\u7528SQL\u8fdb\u884c\u53cc\u5411\u8868\u5934\u62a5\u8868\u67e5\u8be2(\u4e00)","smalltitle":"","mid":"0","fname":"\u6570\u636e\u5e93\u7f16\u7a0b","special_id":"0","bak_id":"0","info":"0","hits":"285","pages":"2","comments":"0","posttime":"2017-12-14 14:32:26","list":"1513233146","username":"admin","author":"","copyfrom":"","copyfromurl":"","titlecolor":"","fonttype":"0","titleicon":"0","picurl":"http:\/\/img.2cto.com\/Collfiles\/20171213\/2017121310102490.png","ispic":"1","yz":"1","yzer":"","yztime":"0","levels":"0","levelstime":"0","keywords":"\u5982\u4f55<\/A> \u8fd0\u7528<\/A> SQL<\/A> \u8fdb\u884c<\/A> \u53cc\u5411<\/A> \u8868\u5934<\/A> \u62a5\u8868<\/A> \u67e5\u8be2<\/A>","jumpurl":"","iframeurl":"","style":"","template":"a:3:{s:4:\"head\";s:0:\"\";s:4:\"foot\";s:0:\"\";s:8:\"bencandy\";s:0:\"\";}","target":"0","ip":"113.110.149.170","lastfid":"0","money":"0","buyuser":"","passwd":"","allowdown":"","allowview":"","editer":"","edittime":"0","begintime":"0","endtime":"0","description":"\u5982\u4f55\u8fd0\u7528SQL\u8fdb\u884c\u53cc\u5411\u8868\u5934\u62a5\u8868\u67e5\u8be2","lastview":"1712994717","digg_num":"0","digg_time":"0","forbidcomment":"0","ifvote":"0","heart":"","htmlname":"","city_id":"0"},"page":"1"}