SELECT COUNT(*) FROM (SELECT t.* from ( SELECT p.*,p.id descId from package_channel_price_plan p as a where a.sys_default=0 UNION SELECT c.*,c.id descId from package_commodity_main_info as b LEFT JOIN package_channel_price_plan as c on b.plan_code=c.plan_code where 1=1 and b.commodity_code = ? ) as t where 1=1 and t.plan_status = ? order by descId=? desc, t.updated_time DESC) TOTAL 这个ssql 错在哪

这个 SQL 语句的错误在于使用了别名 `as a` 和 `as b` 来引用表，但是在子查询中并没有定义这些别名，导致语法错误。正确的写法应该是将别名直接放在子查询的括号后面，而不是在子查询内部的每个表名后面。以下是修正后的 SQL 语句：

SELECT COUNT(*) FROM (
    SELECT t.* 
    FROM (
        SELECT p.*, p.id AS descId 
        FROM package_channel_price_plan p 
        WHERE p.sys_default = 0 
        
        UNION 
        
        SELECT c.*, c.id AS descId 
        FROM package_commodity_main_info b 
        LEFT JOIN package_channel_price_plan c ON b.plan_code = c.plan_code 
        WHERE 1=1 
        AND b.commodity_code = ?
    ) AS t 
    WHERE 1=1 
    AND t.plan_status = ? 
    ORDER BY descId DESC, t.updated_time DESC
) AS TOTAL

相关问题

SELECT XMDK_T.* FROM XMDK_T游标初始化

游标初始化是一种在 SQL 中用于检索数据的技术。在 Oracle 数据库中，您可以使用以下语法来初始化游标：

DECLARE
  cursor_name SYS_REFCURSOR;
BEGIN
  OPEN cursor_name FOR
    SELECT  XMDK_T.*
    FROM    XMDK_T;
END;

其中，`XMDK_T` 是一个表名，`SYS_REFCURSOR` 是一种游标类型，`cursor_name` 是游标变量的名称。这个语句将返回 `XMDK_T` 表中的所有数据，您可以在接下来的代码中使用游标变量来处理这些数据。

SELECT COUNT( * ) FROM ( SELECT t.* FROM ( SELECT p.* FROM package_channel_price_plan p a WHERE a.sys_default = 0 UNION SELECT c.* FROM package_commodity_main_info b LEFT JOIN package_channel_price_plan c ON b.plan_code = c.plan_code WHERE 1 = 1 AND b.commodity_code = '3323070713301825957052' ) AS t WHERE 1 = 1 AND t.plan_status = '1' ORDER BY t.updated_time DESC ) TOTAL 这个sql错在哪里

这个 SQL 语句中的错误在于使用了不正确的表别名和列名。以下是修正后的 SQL 语句：

SELECT COUNT(*) FROM (
SELECT t.* FROM (
SELECT p.* FROM package_channel_price_plan p
WHERE p.sys_default = 0
UNION
SELECT c.* FROM package_commodity_main_info b
LEFT JOIN package_channel_price_plan c ON b.plan_code = c.plan_code
WHERE 1 = 1 AND b.commodity_code = '3323070713301825957052'
) AS t
WHERE 1 = 1 AND t.plan_status = '1'
ORDER BY t.updated_time DESC
) AS TOTAL