-> 存储过程

-> 存储过程
2019年7月2日
12:03
利用存储过程做数据汇总
示例:
CREATE DEFINER=root@% PROCEDURE statisticalTable1()
BEGIN
/** 1. 播种情况的记录集合 **/
DROP TABLE
IF
EXISTS temp;
CREATE TEMPORARY TABLE temp (
SELECT
t.,
vt.VarietyTypeID,
vt.
VALUE
,
tp.VarietyTypeName,
timeTv.
VALUE
AS DateValue,
timeTv.BZQKTimeTypeID,
timeT.NAME AS DateNameStr
FROM
Enter_SeedlingBZQKDCTable t
LEFT JOIN Enter_BZQKDCVarietyTypeValue vt ON t.ID = vt.BZQKDCBaseID
LEFT JOIN VTB_VarietyType tp ON vt.VarietyTypeID = tp.ID
LEFT JOIN Enter_BZQKDCTimeValue timeTv ON t.ID = timeTv.BZQKDCBaseID
AND timeTv.VarietyTypeID = tp.ID
LEFT JOIN Enter_BZQKTimeType timeT ON timeTv.BZQKTimeTypeID = timeT.ID
);
/** 2. 行转列获取具体的记录 **/
DROP TABLE
IF
EXISTS tab;
CREATE TEMPORARY TABLE tab (
SELECT
temp.BaseID,
temp.AreaID,
temp.AreaTypeID,
temp.lifeyear,
temp.CreateTime,
temp.SeedlingArea,
MAX( CASE VarietyTypeID WHEN 4 THEN VALUE ELSE 0 END ) AS BanDongXingVaue,
MAX( CASE VarietyTypeID WHEN 9 THEN VALUE ELSE 0 END ) AS ChunXingVaue,
MAX( CASE WHEN ( BZQKTimeTypeID = 6 AND VarietyTypeID = 4 ) THEN DateValue ELSE 0 END ) AS Data1010BanDong,
MAX( CASE WHEN ( BZQKTimeTypeID = 6 AND VarietyTypeID = 9 ) THEN DateValue ELSE 0 END ) AS Data1010ChunXing,
MAX( CASE WHEN ( BZQKTimeTypeID = 7 AND VarietyTypeID = 4 ) THEN DateValue ELSE 0 END ) AS Data1015BanDong,
MAX( CASE WHEN ( BZQKTimeTypeID = 7 AND VarietyTypeID = 9 ) THEN DateValue ELSE 0 END ) AS Data1015ChunXing,
MAX( CASE WHEN ( BZQKTimeTypeID = 8 AND VarietyTypeID = 4 ) THEN DateValue ELSE 0 END ) AS Data1020BanDong,
MAX( CASE WHEN ( BZQKTimeTypeID = 8 AND VarietyTypeID = 9 ) THEN DateValue ELSE 0 END ) AS Data1020ChunXing,
MAX( CASE WHEN ( BZQKTimeTypeID = 9 AND VarietyTypeID = 4 ) THEN DateValue ELSE 0 END ) AS Data1031BanDong,
MAX( CASE WHEN ( BZQKTimeTypeID = 9 AND VarietyTypeID = 9 ) THEN DateValue ELSE 0 END ) AS Data1031ChunXing,
MAX( CASE WHEN ( BZQKTimeTypeID = 10 AND VarietyTypeID = 4 ) THEN DateValue ELSE 0 END ) AS Data1101BanDong,
MAX( CASE WHEN ( BZQKTimeTypeID = 10 AND VarietyTypeID = 9 ) THEN DateValue ELSE 0 END ) AS Data1101ChunXing
FROM
temp
GROUP BY
temp.BaseID,
temp.AreaID,
temp.AreaTypeID,
temp.lifeyear,
temp.CreateTime,
temp.SeedlingArea
);
/** 2. 获取具体年份的统计记录 **/
SELECT
lifeyear,
SUM( SeedlingArea ) AS SeedlingArea,
SUM( BanDongXingVaue ) AS BanDongXingVaue,
SUM( ChunXingVaue ) AS ChunXingVaue,
SUM( Data1010BanDong ) AS Data1010BanDong,
SUM( Data1010ChunXing ) AS Data1010ChunXing,
SUM( Data1015BanDong ) AS Data1015BanDong,
SUM( Data1015ChunXing ) AS Data1015ChunXing,
SUM( Data1020BanDong ) AS Data1020BanDong,
SUM( Data1020ChunXing ) AS Data1020ChunXing,
SUM( Data1031BanDong ) AS Data1031BanDong,
SUM( Data1031ChunXing ) AS Data1031ChunXing,
SUM( Data1101BanDong ) AS Data1101BanDong,
SUM( Data1101ChunXing ) AS Data1101ChunXing
FROM
tab
*GROUP BY
lifeyear;

已使用 Microsoft OneNote 2016 创建。