时间戳

首页 > 解决方案 > 在 SQL Anywhere 中使用 AS 后无法连接两个表

sql - 在 SQL Anywhere 中使用 AS 后无法连接两个表

问题描述

我对 SQL 比较陌生（在 SQL Anywhere 中）。我试图加入两个表，但总是出错

SQL Anywhere 错误 -131：第 17 行“左”附近的语法错误

我的左连接有什么错误吗？谢谢

(
SELECT 
    Text_ID, Asdate, BigUnitCode AS Big_Code, 
    (BigUnitCode +'@'+ BigUnitName) as Main_ID, Method, xt.[Comment], 
    SUM(FIvalue) AS CurrentValue 
FROM
    (SELECT * FROM dmf_dbo.main1
     WHERE Text_ID = 440168) AS xt
GROUP BY 
    Text_ID, Asdate, BigUnitCode, Method, xt.[Comment], BigUnitName
) AS gz
LEFT JOIN
    (SELECT
         (BigUnitCode +'@'+ BigUnitName) AS Main_ID, BigDep_code AS group_id, 
         BigDep_name AS group_name, SmallUnit_code AS sub_id, 
         SmallUnit_name AS sub_name 
     FROM
         dmf_dbo.main2 
     WHERE 
         ASOFDATE = '2020-07-11'
     GROUP BY 
         SmallUnit_code, SmallUnit_name) AS ho ON gz.Main_ID = ho.Main_ID

标签： sqlsqlanywhere

解决方案

在没有看到您的数据或您的预期输出的情况下有点棘手，所以我对您的输出不承担任何责任，但试试这个。请记住，如果您格式化代码并使用（接近的东西）正确的缩进，通常更容易看到发生了什么。

让我们来看看你的第一个声明。

(
SELECT 
    Text_ID
    ,Asdate
    ,BigUnitCode AS Big_Code
    ,(BigUnitCode +'@'+ BigUnitName) as Main_ID 
    ,Method
    ,xt.[Comment]
      FROM
          (
           SELECT * FROM dmf_dbo.main1
           WHERE Text_ID = 440168)
          )
) AS gz

您会注意到删除了一个放错位置的括号（括号）并删除了GROUP BYand SUM，我们将在最后一步将它们放在最外层的查询中。这是因为您没有SUM在第二个查询中使用，因此很难预测生成的数据集在JOIN.

现在让我们看看第二个查询。我不确定你为什么在GROUP BY这里使用，因为似乎没有聚合。除非您尝试删除重复项（如在DISTINCT中），否则无需这样做。由于您没有提供上下文，我假设您不希望这样做。

  (
     SELECT
          (BigUnitCode +'@'+ BigUnitName) AS Main_ID
          ,BigDep_code AS group_id
          ,BigDep_name AS group_name
          ,SmallUnit_code AS sub_id
          ,SmallUnit_name AS sub_name 
         FROM
             dmf_dbo.main2 
         WHERE ASOFDATE = '2020-07-11'
 ) AS ho

现在，让我们把它们放在一起。

    SELECT 
    gz.Text_ID
    ,gz.Asdate
    ,gz.Big_Code
    ,gz.Main_ID
    ,gz.Method
    ,gz.Comment 
    ,ho.Main_ID
    ,ho.group_id
    ,ho.group_name
    ,ho.sub_id
    ,ho.sub_name
    ,SUM(gz.FIvalue) AS CurrentValue 
      FROM 
          ( 
           (SELECT 
           Text_ID
           ,Asdate
           ,BigUnitCode AS Big_Code
           ,(BigUnitCode +'@'+ BigUnitName) as Main_ID 
           ,Method
           ,xt.[Comment]
             FROM
                (
                 SELECT * FROM dmf_dbo.main1
                 WHERE Text_ID = 440168)
                 )
         ) AS gz
          
           LEFT JOIN 
         
         (
           SELECT
           (BigUnitCode +'@'+ BigUnitName) AS Main_ID
           ,BigDep_code AS group_id
           ,BigDep_name AS group_name
           ,SmallUnit_code AS sub_id
           ,SmallUnit_name AS sub_name 
           FROM
           dmf_dbo.main2 
           WHERE ASOFDATE = '2020-07-11'
         ) AS ho  
        
         ON gz.Main_ID = ho.Main_ID
      
         )

GROUP BY 
  gz.Text_ID
    ,gz.Asdate
    ,gz.Big_Code
    ,gz.Main_ID
    ,gz.Method
    ,gz.Comment 
    ,ho.Main_ID
    ,ho.group_id
    ,ho.group_name
    ,ho.sub_id
    ,ho.sub_name

推荐阅读