sybase 自动根据sql生成excel文件
1.下载sybase官方软件:MySQL Workbench 6.3 CE
2.新建bat文件脚本
脚本如下:红色就是需要修改的服务器地址,账户密码
c:
cd C:\Program Files\MySQL\MySQL Workbench 6.3 CE
DEL E:\app\cis2000\ap\AP_Work_Folder\Internal_Auditor_Rpt\Weekly_Active_Flag_Review.xls
mysql -h fca-server.org -P3308 -uuser-ppassword -e "source E:/app/cis2000/Review.sql" > E:\app\cis2000\Review.xls
type E:\app\cis2000\Review.sql >> E:\app\cis2000\Review.xls
3.Review.sql如下:
-- [/Formatter] Formatted with Sybase T-SQL Formatter(version: 1.5.1.14696) at 04/22/2016 13:43:25 -07:00:00[Formatter/]
use test;
SET SQL_SAFE_UPDATES = 0;
DROP TEMPORARY TABLE
IF EXISTS tt_q_gen;
CREATE TEMPORARY TABLE tt_q_gen
SELECT a.crud_id
,max(a.resource_id) AS resource_id
,max(a.crud_type) AS qgen_crud_type
,max(CONCAT_WS('.', a.server_name, a.db_name, a.object_name)) AS qgen_object_name
,GROUP_CONCAT(distinct CASE
WHEN create_display = 'Y'
OR change_edit = 'Y'
THEN b.column_name
ELSE NULL
END) AS qgen_editable_column_name
,(
SELECT max(z.resource_id)
FROM CIS.spy_resource1 z
WHERE z.parentresource = a.resource_id
AND z.resource_id like '%BPM'
) AS qgen_bpm_resource_id
FROM CIS.crud_header a
LEFT JOIN CIS.crud_detail b
ON a.crud_id = b.crud_id
GROUP BY a.crud_id;
UPDATE tt_q_gen
SET qgen_editable_column_name = 'ReadOnly'
WHERE qgen_crud_type = 'CRpt';
/*
SELECT *
FROM tt_q_gen;
*/
-- -------------------------------------------------------------------------------------------
-- -------------------------------------------------------------------------------------------
-- -------------------------------------------------------------------------------------------
use test;
SET SQL_SAFE_UPDATES = 0;
DROP TEMPORARY TABLE
IF EXISTS tt_q_gen2;
CREATE TEMPORARY TABLE tt_q_gen2
SELECT 'ACTIVE_FLAG_REVIEW' AS review_type
,sr.resource_id
,min(z.entry_datetime) AS change_date
,max(sr.parentresource) AS parentresource
,(
SELECT max(z.resourcecomment)
FROM CIS.spy_resource1 z
WHERE sr.parentresource = z.resource_id
) AS parentresourcecomment
,a.qgen_crud_type
,a.qgen_object_name
,a.qgen_editable_column_name
,a.qgen_bpm_resource_id
FROM CIS.spy_resource1 sr
INNER JOIN LOG.change_log_spy z
ON sr.resource_id = z.resource_id
LEFT JOIN CIS.crud_header ch
ON sr.resource_id = ch.resource_id
LEFT JOIN tt_q_gen a
ON sr.resource_id = a.resource_id
WHERE z.column_name = 'activeFlag'
AND z.role_id = - 1
AND z.new_value = 'Y'
AND z.entry_datetime >= cast(date_add(date_add(last_day(now()), interval 1 day), interval - 2 month) AS date)
AND z.entry_datetime < cast(date_add(date_add(last_day(now()), interval 1 day), interval - 1 month) AS date)
GROUP BY sr.resource_id;
-- -------------------------------------------------------------------------------------------
-- -------------------------------------------------------------------------------------------
-- -------------------------------------------------------------------------------------------
SET SESSION group_concat_max_len = 10000;
SELECT GROUP_CONCAT(distinct concat("max(CASE WHEN ct.comment_display = '", ifnull(ct.comment_display,''), "' THEN replace(rr.review_Comment,char(10),' ')
ELSE ''
END)", " AS '", ifnull(ct.comment_display,''), "'") ORDER BY ct.type_id)
INTO @v_express
FROM CIS.spy_resource1 sr
INNER JOIN tt_q_gen2 qg2
ON sr.resource_id = qg2.resource_id
LEFT JOIN CIS.global_department gd
ON sr.bizDeptId = gd.bizDeptId
LEFT JOIN CIS.global_employee ge
ON sr.entry_id = ge.globalEmployeeId
LEFT JOIN CIS.resource_review rr
ON rr.resource_id = sr.resource_id
LEFT JOIN CIS.global_comment_type ct
ON 1=1;
SET @SQL = concat(
"SELECT now()
,sr.resource_id
,max(sr.resourceComment) as Description
,max(gd.bizDeptName) as Spy_Owner
,max(sr.title) as title
,max(sr.resourceType) as resourceType
,max(sr.platform) as platform
,max(sr.activeflag) as activeflag
,max(sr.keyflag) as keyflag
,max(sr.soxflag) as soxflag
,max(sr.entry_datetime) as entry_datetime
,max(sr.update_datetime) as update_datetime
,max(qg2.change_date) as change_date
,max(qg2.parentresource) as parentresource
,max(qg2.parentresourcecomment) as parentresourcecomment
,max(qg2.qgen_crud_type) as qgen_crud_type
,max(qg2.qgen_object_name) as qgen_object_name
,max(qg2.qgen_editable_column_name) as qgen_editable_column_name
,max(qg2.qgen_bpm_resource_id) as qgen_bpm_resource_id
,max(ge.globalEmployeeId) as created_by
,max(ge.firstname) as firstname
,max(ge.nickName) as nickName
,max(ge.lastname) as lastname
,max(rr.entry_id) as Reviewer,"
, @v_express, "
FROM CIS.spy_resource1 sr
INNER JOIN tt_q_gen2 qg2
ON sr.resource_id = qg2.resource_id
LEFT JOIN CIS.global_department gd
ON sr.bizDeptId = gd.bizDeptId
LEFT JOIN CIS.global_employee ge
ON sr.entry_id = ge.globalEmployeeId
LEFT JOIN CIS.resource_review rr
ON rr.resource_id = sr.resource_id
LEFT JOIN CIS.global_comment_type ct
ON ct.comment_type = rr.comment_type
GROUP BY sr.resource_id
ORDER BY max(qg2.change_date)
");
PREPARE stmt1
FROM @SQL;
EXECUTE stmt1;
DROP TEMPORARY TABLE
IF EXISTS tt1;
CREATE TEMPORARY TABLE tt1
SELECT sr.resource_id
FROM CIS.spy_resource1 sr
INNER JOIN tt_q_gen2 qg2
ON sr.resource_id = qg2.resource_id
LEFT JOIN CIS.global_department gd
ON sr.bizDeptId = gd.bizDeptId
LEFT JOIN CIS.global_employee ge
ON sr.entry_id = ge.globalEmployeeId
LEFT JOIN CIS.resource_review rr
ON rr.resource_id = sr.resource_id
LEFT JOIN CIS.global_comment_type ct
ON ct.comment_type = rr.comment_type
GROUP BY sr.resource_id;
SELECT "";
SELECT now() AS report_time
,count(1) AS report_row_count
FROM tt1;
SELECT "";
-- select * from CIS.resource_review where resource_id = 'JE_UPLOAD_SUPER_APPROVE'
-- select * from CIS.global_comment_type