文章/答案/技术大牛

发布

问查询的Coldfusion查询
EN

Stack Overflow用户

提问于 2014-11-07 12:31:57

回答 2查看 3.5K关注 0票数 3

Sql查询与数据源工作良好。(B)编码；

<cfquery name="GET_ACC_REMAINDER" datasource="#dsn2#">
    SELECT
        SUM(BAKIYE) AS BAKIYE, 
        SUM(BORC) AS BORC,
        SUM(ALACAK) AS ALACAK, 
        ACCOUNT_CODE,
        ACCOUNT_NAME,
        ACCOUNT_ID
    FROM
    (
    SELECT
        ROUND(SUM(ACCOUNT_ACCOUNT_REMAINDER.BORC - ACCOUNT_ACCOUNT_REMAINDER.ALACAK),2) AS BAKIYE, 
        ROUND(SUM(ACCOUNT_ACCOUNT_REMAINDER.BORC),2) AS BORC,
        ROUND(SUM(ACCOUNT_ACCOUNT_REMAINDER.ALACAK),2) AS ALACAK, 
        ACCOUNT_PLAN.ACCOUNT_CODE,
        ACCOUNT_PLAN.ACCOUNT_NAME,
    ACCOUNT_PLAN.ACCOUNT_ID,
    ACCOUNT_PLAN.SUB_ACCOUNT
    FROM
        (
        SELECT
            0 AS ALACAK,
             SUM(ROUND(ACCOUNT_CARD_ROWS.AMOUNT,2)) AS BORC,
            ACCOUNT_CARD_ROWS.ACCOUNT_ID    
        FROM
            ACCOUNT_CARD_ROWS,ACCOUNT_CARD
        WHERE
            BA = 0 AND ACCOUNT_CARD.CARD_ID=ACCOUNT_CARD_ROWS.CARD_ID
            AND ACTION_DATE BETWEEN  '2013-11-01 00:00:00' AND  '2013-11-30 00:00:00' GROUP BY
            ACCOUNT_CARD_ROWS.ACCOUNT_ID
        HAVING SUM(ROUND(ACCOUNT_CARD_ROWS.AMOUNT,2))<>0
    UNION ALL
        SELECT
             SUM(ROUND(ACCOUNT_CARD_ROWS.AMOUNT,2)) AS ALACAK,
            0 AS BORC,
            ACCOUNT_CARD_ROWS.ACCOUNT_ID        
        FROM
            ACCOUNT_CARD_ROWS,
            ACCOUNT_CARD
        WHERE
            BA = 1 AND ACCOUNT_CARD.CARD_ID=ACCOUNT_CARD_ROWS.CARD_ID       
            AND ACTION_DATE BETWEEN  '2013-11-01 00:00:00' AND  '2013-11-30 00:00:00' GROUP BY
            ACCOUNT_CARD_ROWS.ACCOUNT_ID            
        HAVING SUM(ROUND(ACCOUNT_CARD_ROWS.AMOUNT,2))<>0 
        UNION ALL
            SELECT DISTINCT
                0 AS ALACAK,
                0 AS BORC,
                ACCOUNT_PLAN.ACCOUNT_CODE
            FROM
                ACCOUNT_PLAN,
                ACCOUNT_PLAN ACCOUNT_ACCOUNT_REMAINDER
            WHERE
                ACCOUNT_PLAN.ACCOUNT_CODE NOT IN 
                                    (SELECT 
                                        ACCOUNT_ID 
                                    FROM 
                                        ACCOUNT_CARD_ROWS,
                                        ACCOUNT_CARD
                                    WHERE
                                        ACCOUNT_PLAN.ACCOUNT_CODE = ACCOUNT_CARD_ROWS.ACCOUNT_ID AND
                                        ACCOUNT_CARD_ROWS.CARD_ID = ACCOUNT_CARD.CARD_ID
                                        AND ACCOUNT_CARD.ACTION_DATE BETWEEN  '2013-11-01 00:00:00' AND  '2013-11-30 00:00:00' )

                AND ACCOUNT_PLAN.ACCOUNT_CODE = LEFT(ACCOUNT_ACCOUNT_REMAINDER.ACCOUNT_CODE,3)

        )
        AS ACCOUNT_ACCOUNT_REMAINDER,
        ACCOUNT_PLAN
    WHERE
        1=1
        AND  ACCOUNT_PLAN.ACCOUNT_CODE = LEFT(ACCOUNT_ACCOUNT_REMAINDER.ACCOUNT_ID,3)
    GROUP BY    
        ACCOUNT_PLAN.ACCOUNT_CODE, 
        ACCOUNT_PLAN.ACCOUNT_NAME,
    ACCOUNT_PLAN.ACCOUNT_ID,
    ACCOUNT_PLAN.SUB_ACCOUNT    
    )T1
    GROUP BY
        ACCOUNT_CODE, 
        ACCOUNT_NAME,
        ACCOUNT_ID,
        SUB_ACCOUNT     
    ORDER BY    
        ACCOUNT_CODE
</cfquery>

这个查询太慢了。为此，我尝试使用查询查询。我编码了一些东西。

<cfquery name="ACCOUNT_PLAN" datasource="#dsn2#">
select * from ACCOUNT_PLAN
</cfquery>
<cfquery name="ACCOUNT_CARD" datasource="#dsn2#">
select * from ACCOUNT_CARD
</cfquery>
<cfquery name="ACCOUNT_CARD_ROWS" datasource="#dsn2#">
select * from ACCOUNT_CARD_ROWS
</cfquery>
<cfquery name="GET_ACC_REMAINDER" dbtype="query">
    SELECT
        SUM(BAKIYE) AS BAKIYE, 
        SUM(BORC) AS BORC,
        SUM(ALACAK) AS ALACAK, 
        ACCOUNT_CODE,
        ACCOUNT_NAME,
        ACCOUNT_ID
    FROM
    (
    SELECT
        ROUND(SUM(ACCOUNT_ACCOUNT_REMAINDER.BORC - ACCOUNT_ACCOUNT_REMAINDER.ALACAK),2) AS BAKIYE, 
        ROUND(SUM(ACCOUNT_ACCOUNT_REMAINDER.BORC),2) AS BORC,
        ROUND(SUM(ACCOUNT_ACCOUNT_REMAINDER.ALACAK),2) AS ALACAK, 
        ACCOUNT_PLAN.ACCOUNT_CODE,
        ACCOUNT_PLAN.ACCOUNT_NAME,
    ACCOUNT_PLAN.ACCOUNT_ID,
    ACCOUNT_PLAN.SUB_ACCOUNT
    FROM
        (
        SELECT
            0 AS ALACAK,
             SUM(ROUND(ACCOUNT_CARD_ROWS.AMOUNT,2)) AS BORC,
            ACCOUNT_CARD_ROWS.ACCOUNT_ID    
        FROM
            ACCOUNT_CARD_ROWS,ACCOUNT_CARD
        WHERE
            BA = 0 AND ACCOUNT_CARD.CARD_ID=ACCOUNT_CARD_ROWS.CARD_ID
            AND ACTION_DATE BETWEEN  '2013-11-01 00:00:00' AND  '2013-11-30 00:00:00' GROUP BY
            ACCOUNT_CARD_ROWS.ACCOUNT_ID
        HAVING SUM(ROUND(ACCOUNT_CARD_ROWS.AMOUNT,2))<>0
    UNION ALL
        SELECT
             SUM(ROUND(ACCOUNT_CARD_ROWS.AMOUNT,2)) AS ALACAK,
            0 AS BORC,
            ACCOUNT_CARD_ROWS.ACCOUNT_ID        
        FROM
            ACCOUNT_CARD_ROWS,
            ACCOUNT_CARD
        WHERE
            BA = 1 AND ACCOUNT_CARD.CARD_ID=ACCOUNT_CARD_ROWS.CARD_ID       
            AND ACTION_DATE BETWEEN  '2013-11-01 00:00:00' AND  '2013-11-30 00:00:00' GROUP BY
            ACCOUNT_CARD_ROWS.ACCOUNT_ID            
        HAVING SUM(ROUND(ACCOUNT_CARD_ROWS.AMOUNT,2))<>0 
        UNION ALL
            SELECT DISTINCT
                0 AS ALACAK,
                0 AS BORC,
                ACCOUNT_PLAN.ACCOUNT_CODE
            FROM
                ACCOUNT_PLAN,
                ACCOUNT_PLAN ACCOUNT_ACCOUNT_REMAINDER
            WHERE
                ACCOUNT_PLAN.ACCOUNT_CODE NOT IN 
                                    (SELECT 
                                        ACCOUNT_ID 
                                    FROM 
                                        ACCOUNT_CARD_ROWS,
                                        ACCOUNT_CARD
                                    WHERE
                                        ACCOUNT_PLAN.ACCOUNT_CODE = ACCOUNT_CARD_ROWS.ACCOUNT_ID AND
                                        ACCOUNT_CARD_ROWS.CARD_ID = ACCOUNT_CARD.CARD_ID
                                        AND ACCOUNT_CARD.ACTION_DATE BETWEEN  '2013-11-01 00:00:00' AND  '2013-11-30 00:00:00' )

                AND ACCOUNT_PLAN.ACCOUNT_CODE = LEFT(ACCOUNT_ACCOUNT_REMAINDER.ACCOUNT_CODE,3)

        )
        AS ACCOUNT_ACCOUNT_REMAINDER,
        ACCOUNT_PLAN
    WHERE
        1=1
        AND  ACCOUNT_PLAN.ACCOUNT_CODE = LEFT(ACCOUNT_ACCOUNT_REMAINDER.ACCOUNT_ID,3)
    GROUP BY    
        ACCOUNT_PLAN.ACCOUNT_CODE, 
        ACCOUNT_PLAN.ACCOUNT_NAME,
    ACCOUNT_PLAN.ACCOUNT_ID,
    ACCOUNT_PLAN.SUB_ACCOUNT    
    )T1
    GROUP BY
        ACCOUNT_CODE, 
        ACCOUNT_NAME,
        ACCOUNT_ID,
        SUB_ACCOUNT     
    ORDER BY    
        ACCOUNT_CODE
</cfquery>

Coldfusion调试显示此错误。

执行数据库查询时错误。查询语法错误。“遇到"(.错误发生在第10行.)。

你知不知道？

sql

database

coldfusion

回答 2

Stack Overflow用户

发布于 2014-11-07 13:05:13

查询不支持子查询。

票数 9

Stack Overflow用户

发布于 2014-11-07 14:58:42

Stormcloak，我知道您想做什么，但是您的第一个工作应该是尝试确定原始查询速度慢的原因。如果您想通过Q的Q来完成这个任务，您就不能(正如Dan所说的那样)使用子查询。Q的q支持相当谨慎的命令和语法子集。不支持从Q的Q子查询中进行选择。

但是，如果您想利用q的Q，并且愿意将子查询逻辑移到代码中，您可以执行类似于以下psuedo代码的操作：

<cfquery name="ACCOUNT_PLAN" datasource="#dsn2#">
select * from ACCOUNT_PLAN
</cfquery>
<cfquery name="ACCOUNT_CARD" datasource="#dsn2#">
select * from ACCOUNT_CARD
</cfquery>
<cfquery name="ACCOUNT_CARD_ROWS" datasource="#dsn2#">
select * from ACCOUNT_CARD_ROWS
</cfquery>

<!---MAK:   get the unique account_ids--->
<cfquery name="getIDs" dbtype="query">
    SELECT UNIQUE ACCOUNT_ID
    FROM    ACCOUNT_PLAN  
</cfquery>


<Cfset GET_ACC_REMAINDER = querynew("BAKIYE,BORC,ALACAK,ACCOUNT_CODE,ACCOUNT_NAME,ACCOUNT_ID","decimal,decimal,decimal,varchar,varchar,varchar")/>

<cfloop query="getIDs">
    <!---MAK:   Pull out a subset of data based on account_ID--->
    <cfquery name="getPlans" dbtype="query">
        SELECT * FROM ACCOUNT_PLAN  
        WHERE    account_id = '#account_id#'    
    </cfquery>
    <cfquery name="getCards" dbtype="query">
        SELECT * FROM ACCOUNT_CARD  
        WHERE    account_id = '#account_id#'    
    </cfquery>
    <cfquery name="getPlans" dbtype="query">
        SELECT * FROM ACCOUNT_CARD_ROWS  
        WHERE    account_id = '#account_id#'    
    </cfquery>

    Now, using implement your logic using these subqueries - probably with a block of code to calculate BAKIYE, BORC, AND ALACAK - 

    <cfset BAKIYE = 0/>
    <!---MAK:   Block 1 - calculate BAKIYE--->  
    ... code for BAKIYE goes here

    <cfset BORC = 0/>
    <!---MAK:   block 2 calculate BORC--->
    ... code for BORC...

    <cfset ALACAK = 0/>
    <!---MAK:   block 3 calclate ALACAK--->
    ... code for ALACAK 

    Final step:

    <cfset querysetcell(GET_ACC_REMAINDER,"BAKIYE",BAKIYE)/>
    <cfset querysetcell(GET_ACC_REMAINDER,"BORC",BORC)/>
    <cfset querysetcell(GET_ACC_REMAINDER,"ALACAK",ALACAK)/>
    <cfset querysetcell(GET_ACC_REMAINDER,"ACCOUNT_CODE",getPlans.ACCOUNT_CODE)/>
    <cfset querysetcell(GET_ACC_REMAINDER,"ACCOUNT_NAME",getplans.ACCOUNT_NAME)/>
    <cfset querysetcell(GET_ACC_REMAINDER,"ACCOUNT_ID",ACCOUNT_ID)/>

</cfloop>

最终的结果是一个查询，GET_ACC_REMAINDER中填充了类似于复杂的联合/子查询的值。

这将涉及重新设计在复杂的联合查询中找到的逻辑--这超出了堆栈溢出解决方案的范围--但您了解了这一点。考虑到您的查询的复杂性，我认为这是一项艰巨的任务，我将确保和质量保证。不仅逻辑是多姿多彩的，而且DB对待数字的舍入等方式可能与Java对待它的方式不同--这是另一个需要考虑的问题。

注意，您可能也需要在原始查询中获取表--例如，我注意到一个似乎无法识别为聚合的ACCOUNT_ACCOUNT_REMAINDER --所以它可能是一个表。毫无疑问，你将需要额外的Q的q，数学运算，如果你的每一个模块内的计算。

现在的问题可能是你是否应该这么做(我对此的回答有点谨慎)？我可能会在亚当的营地里劝你不要这样做。从性能的角度来看，它可能不会为您节省太多--特别是当您必须访问数据库以获取逻辑块中的其他数据时。最后，它可能会慢得多。这类事情存在于DB上是合乎逻辑的，而DB Server是为这种复杂的逻辑配备的。我可能会花时间在服务器上调优查询，或者用临时表将其重写为存储的proc。

Q是一个很好的工具，但对于如何使用它还不够复杂:)

票数 3

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/26801324

复制

相似问题

问查询的Coldfusion查询
EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问查询的Coldfusion查询EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问查询的Coldfusion查询
EN