orcal可以mysql不行

username11

再一次来求助了~请求大佬 就是这一段代码，在orcal可以，现在需要转为mysql请问应该怎么改呢

[SQL] 纯文本查看 复制代码

SELECT
	MAX(COUNT(DISTINCT qodi_Id))
FROM
	(
		SELECT
			qod.qodi_Id,
			qod.qodi_lsro_id
		FROM
			Qk_Order_Distribute qod
		LEFT JOIN LG_STORE_ROOM lsr ON lsr.lsro_id = qod.qodi_lsro_id
		LEFT JOIN qk_Order qo ON qo.qord_id = qod.qord_id
		LEFT JOIN qk_Literature ql ON ql.qlit_id = qo.qlit_id
		WHERE
			qod.acct_id = 22
AND qo.qord_Year = '2021'			
	) qod
LEFT JOIN (
	SELECT
		qui.lsro_Id
	FROM
		Qk_Unit_Info qui
	LEFT JOIN qk_Year_Vol_Period qyvp ON qyvp.qyvp_id = qui.qyvp_id
	LEFT JOIN qk_Order qo ON qo.qord_id = qyvp.qord_id
	LEFT JOIN qk_Literature ql ON ql.qlit_id = qui.qlit_id
	WHERE
		qui.acct_id = 22
	AND qui.quin_Flow_State = 2 
	AND qo.qord_Year = 2021
	
) qui ON qui.lsro_id = qod.qodi_lsro_id
WHERE
	qui.lsro_id IS NULL
GROUP BY
	 qod.qodi_lsro_id

wifengqingyang

设置别名之类的，前面带个as 试试

username11

wifengqingyang 发表于 2021-4-26 16:02
设置别名之类的，前面带个as 试试

已经加了别名了呀~

origamiii

太长了懒得看 = = 但我大致看了下，只是普通的left join这种语法是sql通用的 一般莫得问题。一般oracle可以，mysql不行的话，准是函数有点问题，比如： MAX(COUNT(DISTINCT qodi_Id))  这里。

树荫底下的太阳

[SQL] 纯文本查看 复制代码

select max(c) from (
SELECT COUNT(DISTINCT qodi_Id) c
FROM (
         SELECT qod.qodi_Id,
                qod.qodi_lsro_id
         FROM Qk_Order_Distribute qod
                  LEFT JOIN LG_STORE_ROOM lsr ON lsr.lsro_id = qod.qodi_lsro_id
                  LEFT JOIN qk_Order qo ON qo.qord_id = qod.qord_id
                  LEFT JOIN qk_Literature ql ON ql.qlit_id = qo.qlit_id
         WHERE qod.acct_id = 22
           AND qo.qord_Year = '2021'
     ) qod
         LEFT JOIN (
    SELECT qui.lsro_Id
    FROM Qk_Unit_Info qui
             LEFT JOIN qk_Year_Vol_Period qyvp ON qyvp.qyvp_id = qui.qyvp_id
             LEFT JOIN qk_Order qo ON qo.qord_id = qyvp.qord_id
             LEFT JOIN qk_Literature ql ON ql.qlit_id = qui.qlit_id
    WHERE qui.acct_id = 22
      AND qui.quin_Flow_State = 2
      AND qo.qord_Year = 2021
) qui ON qui.lsro_id = qod.qodi_lsro_id
WHERE qui.lsro_id IS NULL
GROUP BY qod.qodi_lsro_id) a;

树荫底下的太阳

mysql中不支持max嵌套count

username11

树荫底下的太阳 发表于 2021-4-26 16:36
[mw_shl_code=sql,true]select max(c) from (
SELECT COUNT(DISTINCT qodi_Id) c
FROM (

感谢 已经成功了 多加了一层 和您一样