cipay(提问者)drop TEMPORARY table if exists MONTH_TABLE;
CREATE TEMPORARY TABLE MONTH_TABLE(MONTH_NUM VARCHAR(10)) SELECT DISTINCT DATE_FORMAT(ld.date,\'%Y-%m\') AS MONTH_NUM
FROM lab_dips AS ld;
drop TEMPORARY table if exists tmp_table;
CREATE TEMPORARY TABLE tmp_table SELECT DISTINCT ld.sales_organization_id AS organizationid,
so.name AS name FROM lab_dips AS ld LEFT JOIN sales_organization AS so ON ld.sales_organization_id=so.id
WHERE ld.sales_organization_id is not NULL GROUP BY organizationid;
SELECT mm.mon,mm.name,IFNULL(t.counts,\'0\') AS counts
FROM
(SELECT mt.MONTH_NUM AS mon,
tmp.name AS name
-- IF(t.months=mt.MONTH_NUM AND tmp.name=t.organization,t.counts,\'0\')
-- CASE
-- WHEN t.months=mt.MONTH_NUM AND tmp.name=t.organization THEN t.counts ELSE \'0\' END AS counts
-- ,t.counts AS counts
FROM MONTH_TABLE mt,tmp_table tmp) AS mm
LEFT JOIN (
SELECT
DATE_FORMAT(ld.date,\'%Y-%m\') AS months,
so.name AS organization,
COUNT(ldd.id) AS counts
FROM lab_dips AS ld
LEFT JOIN lab_dip_details AS ldd ON ldd.lab_dip_id = ld.id
LEFT JOIN sales_organization AS so ON ld.sales_organization_id=so.id
-- LEFT JOIN t_account AS ta ON ta.FID = ld.creator_id
WHERE ld.deleted_at IS NULL
-- ${if(len(startmonth)==0,\"\", \" AND date_format(ld.date,\'%Y-%m\')>=\'\"+startmonth+\"\'\")}
-- ${if(len(endmonth)==0,\"\", \" AND date_format(ld.date,\'%Y-%m\')<=\'\"+endmonth+\"\'\")}
-- ${if(len(oranizationid)==0,\"\",\" and ld.sales_organization_id=\'\"+oranizationid+\"\'\")}
AND ld.code not LIKE \'%-%\'
AND ldd.deleted_at IS NULL
AND ld.sales_organization_id is not NULL
GROUP BY months,organization
) AS t ON mm.mon=t.months AND t.organization=mm.name
ORDER BY mon ASC
这是完整地sql，可以不写存储过程实现的吧，感觉
cipay(提问者) 回复 axingSELECT mm.mon,mm.name,IFNULL(t.counts,\'0\') AS counts
FROM
(SELECT mt.MONTH_NUM AS mon,
tmp.name AS name
FROM (SELECT DISTINCT DATE_FORMAT(ld.date,\'%Y-%m\') AS MONTH_NUM FROM lab_dips AS ld) mt,(SELECT DISTINCT ld.sales_organization_id AS organizationid,
so.name AS name FROM lab_dips AS ld LEFT JOIN sales_organization AS so ON ld.sales_organization_id=so.id
WHERE ld.sales_organization_id is not NULL GROUP BY organizationid) tmp) AS mm
LEFT JOIN (
SELECT
DATE_FORMAT(ld.date,\'%Y-%m\') AS months,
so.name AS organization,
COUNT(ldd.id) AS counts
FROM lab_dips AS ld
LEFT JOIN lab_dip_details AS ldd ON ldd.lab_dip_id = ld.id
LEFT JOIN sales_organization AS so ON ld.sales_organization_id=so.id
WHERE ld.deleted_at IS NULL
AND ld.code not LIKE \'%-%\'
AND ldd.deleted_at IS NULL
AND ld.sales_organization_id is not NULL
GROUP BY months,organization
) AS t ON mm.mon=t.months AND t.organization=mm.name
ORDER BY mon ASC
改成这样一个查询，可以了，但是参数有点问题
1min目标场景问卷
立即参与
