-------------------------------行转列--------------------------------- SELECT c.PRODUCT_LINE, c.SERVICE_ITEM, c.SERVICE_FINER, c.COLUMN_DATA2 AS BILLING_SUBJECT, SUM(c.SHANGHAI) SHANGHAI, SUM(c.SHENZHEN) SHENZHEN, SUM(c.CHENGDU) CHENGDU, SUM(c.NEIJIANG) NEIJIANG, SUM(c.HEFEI) HEFEI, SUM(c.DEPARTMENT) DEPARTMENT , SUM(c.SHANGHAI+c.SHENZHEN+c.CHENGDU+c.NEIJIANG+c.HEFEI+c.DEPARTMENT) as TOTAL FROM (SELECT DISTINCT(SELECT a.data_name FROM standing_link_table a where b.PRODUCT_LINE=a.DATA_ID) AS PRODUCT_LINE, (SELECT a.data_name FROM STANDING_LINK_TABLE a where b.SERVICE_ITEM=a.DATA_ID) AS SERVICE_ITEM, (SELECT a.data_name FROM STANDING_LINK_TABLE a where b.SERVICE_FINER=a.DATA_ID) AS SERVICE_FINER, b.COLUMN_DATA2, (case b.COLUMN_DATA4 WHEN '上海' THEN b.COLUMN_DATA3 ELSE 0 end) AS SHANGHAI, (case b.COLUMN_DATA4 WHEN '深圳' THEN b.COLUMN_DATA3 ELSE 0 end) AS SHENZHEN, (case b.COLUMN_DATA4 WHEN '成都' THEN b.COLUMN_DATA3 ELSE 0 end) AS CHENGDU, (case b.COLUMN_DATA4 WHEN '内江' THEN b.COLUMN_DATA3 ELSE 0 end) AS NEIJIANG, (case b.COLUMN_DATA4 WHEN '合肥' THEN b.COLUMN_DATA3 ELSE 0 end) AS HEFEI, (case b.COLUMN_DATA4 WHEN '部门' THEN b.COLUMN_DATA3 ELSE 0 end) AS DEPARTMENT FROM (SELECT PRODUCT_LINE, SERVICE_ITEM, SERVICE_FINER, COLUMN_DATA2, COLUMN_DATA4, SUM(COLUMN_DATA3) COLUMN_DATA3 FROM STADING_LEDER_DATA_FORMAL WHERE PRODUCT_LINE='1' AND SERVICE_ITEM ='11' AND SERVICE_FINER ='111' AND BILLING_SUBJECT = '寿险' GROUP BY SERVICE_FINER, COLUMN_DATA2, COLUMN_DATA4) b ,STANDING_LINK_TABLE a)c GROUP BY c.PRODUCT_LINE,c.SERVICE_ITEM,c.SERVICE_FINER,c.COLUMN_DATA2
SELECT c.COLUMN_DATA2 AS BILLING_SUBJECT, c.PRODUCT_LINE, c.SERVICE_ITEM, c.SERVICE_FINER, SUM(c.SHANGHAI) SHANGHAI, SUM(c.SHENZHEN) SHENZHEN, SUM(c.CHENGDU) CHENGDU, SUM(c.NEIJIANG) NEIJIANG, SUM(c.HEFEI) HEFEI, SUM(c.DEPARTMENT) DEPARTMENT , SUM(c.SHANGHAI+c.SHENZHEN+c.CHENGDU+c.NEIJIANG+c.HEFEI+c.DEPARTMENT) as TOTAL FROM (SELECT DISTINCT(SELECT a.data_name FROM STANDING_LINK_TABLE a where b.PRODUCT_LINE=a.DATA_ID) AS PRODUCT_LINE, (SELECT a.data_name FROM STANDING_LINK_TABLE a where b.SERVICE_ITEM=a.DATA_ID) AS SERVICE_ITEM, (SELECT a.data_name FROM STANDING_LINK_TABLE a where b.SERVICE_FINER=a.DATA_ID) AS SERVICE_FINER, b.COLUMN_DATA2, (case b.COLUMN_DATA4 WHEN '上海' THEN b.COLUMN_DATA3 ELSE 0 end) AS SHANGHAI, (case b.COLUMN_DATA4 WHEN '深圳' THEN b.COLUMN_DATA3 ELSE 0 end) AS SHENZHEN, (case b.COLUMN_DATA4 WHEN '成都' THEN b.COLUMN_DATA3 ELSE 0 end) AS CHENGDU, (case b.COLUMN_DATA4 WHEN '内江' THEN b.COLUMN_DATA3 ELSE 0 end) AS NEIJIANG, (case b.COLUMN_DATA4 WHEN '合肥' THEN b.COLUMN_DATA3 ELSE 0 end) AS HEFEI, (case b.COLUMN_DATA4 WHEN '部门' THEN b.COLUMN_DATA3 ELSE 0 end) AS DEPARTMENT FROM (SELECT PRODUCT_LINE, SERVICE_ITEM, SERVICE_FINER, COLUMN_DATA2, COLUMN_DATA4, SUM(COLUMN_DATA3) COLUMN_DATA3 FROM STADING_LEDER_DATA_FORMAL WHERE DATA_STATE = '2' AND IS_DELETE = 'N' AND PRODUCT_LINE = '1' AND SERVICE_ITEM = '11' AND SERVICE_FINER = '111' #AND BILLING_SUBJECT = AND FORM_DATE_BEGIN >= '2018-09-29' AND FORM_DATE_BEGIN < '2018-09-29' GROUP BY SERVICE_FINER, COLUMN_DATA2, COLUMN_DATA4) b ,STANDING_LINK_TABLE a)c GROUP BY c.COLUMN_DATA2,c.PRODUCT_LINE,c.SERVICE_ITEM,c.SERVICE_FINER
ps -ef |grep tomca 后台乱码 tomcat->servers->server.xml
mybaits: select * from user where name like concat("%",#{name},"%");
#某个用户拥有哪些权限菜单 SELECT ID_RESOURCE, RESOURCE_NAME, RESOURCE_URL, PARENT_RESOURCE_ID FROM chk_resource WHERE ID_RESOURCE IN ( SELECT ID_RESOURCE FROM chk_resource_role_rel WHERE ID_ROLE IN ( SELECT ID_ROLE FROM chk_user_role_rel WHERE ID_USER = ( SELECT ID_USER FROM chk_user WHERE UM_ID = 'test' AND IS_DELETE = 'N' ) AND IS_DELETE = 'N' ) AND IS_DELETE = 'N' ) AND IS_DELETE = 'N'
SELECT a.DATA_ID, a.PRODUCT_LINE, a.SERVICE_ITEM, a.SERVICE_FINER, (SELECT PRODUCT_NAME FROM STANDING_PRODUCT sp WHERE sp.PRODUCT_LINE = a.PRODUCT_LINE ) PRODUCT_NAME, (SELECT SERVICE_NAME FROM STANDING_SERVICE ss WHERE ss.SERVICE_ITEM = a.SERVICE_ITEM ) SERVICE_NAME, (SELECT FINER_NAME FROM STANDING_FINER sf WHERE sf.SERVICE_FINER = a.SERVICE_FINER ) FINER_NAME, a.DATA_STATE, a.INPUT_PERSON, a.INPUT_DATA, a.APPROVER, a.APPROVER_DATE, a.SPLIT_LINK, a.BILLING_MONTH, a.BILLING_SUBJECT, a.QUANTITY_WORK, a.OPERATING_AREA, a.CHARGING_MODE, a.JOB_ATTRIBUTES, max(b.COL10) AS COL10, max(b.COL11) AS COL11, max(b.COL17) AS COL17 FROM STANDING_DATA a LEFT JOIN ( SELECT c.DATA_ID, c.PRODUCT_LINE, c.SERVICE_ITEM, c.SERVICE_FINER, max(c.COL10) AS COL10, max(c.COL11) AS COL11, max(c.COL17) AS COL17 FROM ( SELECT DATA_ID, PRODUCT_LINE, SERVICE_ITEM, SERVICE_FINER, CASE WHEN HEADER_FIELD_ID ='10' THEN TEXT_VALUE END AS COL10, CASE WHEN HEADER_FIELD_ID ='11' THEN TEXT_VALUE END AS COL11, CASE WHEN HEADER_FIELD_ID ='17' THEN TEXT_VALUE END AS COL17 FROM STANDING_DATA_ADDENDUM WHERE IS_DELETE = 'N' AND SERVICE_FINER ='1' ) c GROUP BY c.DATA_ID ) b ON a.DATA_ID = b.DATA_ID AND a.SERVICE_FINER = b.SERVICE_FINER WHERE a.IS_DELETE = 'N' AND a.SERVICE_FINER = '1' AND a.INPUT_PERSON LIKE CONCAT('%' ,'fangzong', '%') AND DATA_STATE = '9' GROUP BY a.DATA_ID
