sql从列表中取出一个 [英] sql take one out from the list

问题描述

在这种情况下，我有几家公司，而每家公司中的合作伙伴（工人）都很少，我需要列出这些公司的清单，这个问题是要列出还是不列出的决定性因素。公司在合作伙伴的定义中，因此当我需要列出公司时，它们会被列出几次，因为每个公司中都有一些合作伙伴:)我希望一切都清楚了，到目前为止，我已经编写了一个sql代码：

here is the situation, i have a few companies, and in every company there are few partners(workers), and i need to make a list of these companies, the problem that the defining factor to list or not to list the company is in the partner's definition, so when i need to be listed the companies, they are listed a few times, because there are a few partners in each one :) i hope it is everything clear, so far i have wrote a sql code:

   <cfquery name="GET_POT_COMPANY" datasource="#DSN#">
                SELECT
                    C.COMPANY_ID,
                    C.MEMBER_CODE,
                    C.FULLNAME,
                    C.PARTNER_ID,
                    C.RECORD_DATE,
                    CC.COMPANYCAT
                    <cfif isdefined('attributes.report_sort2') and attributes.report_sort2 is 1>
                    ,CCD.SITE_DOMAIN
                    </cfif>
                FROM 
                    COMPANY C, 
                    COMPANY_CAT CC
                    <cfif isdefined('attributes.report_sort2') and attributes.report_sort2 is 1>
                    ,COMPANY_CONSUMER_DOMAINS CCD,
                    COMPANY_PARTNER CPA
                    </cfif>
                WHERE
                        C.COMPANYCAT_ID = #attributes.comp_cat# 
                        AND CC.COMPANYCAT_ID = C.COMPANYCAT_ID
                    <cfif isdefined('attributes.report_sort2') and attributes.report_sort2 is 1>
                        AND C.COMPANY_ID = CPA.COMPANY_ID
                        AND CPA.PARTNER_ID = CCD.PARTNER_ID
                        AND CCD.SITE_DOMAIN = 'www.projedepo.com'
                    </cfif>
                ORDER BY 
                    C.RECORD_DATE DESC
            </cfquery>

如您所见，我必须列出合作伙伴可以访问网站projedepo的公司，但是每次公司内部的合作伙伴访问此网站时，它都会重复公司的名称，因为它对合作伙伴而非公司进行排序，我如何删除这些重复的公司？我看到有一种方法可以实现，尽管删除重复的公司ID，但是我不知道该怎么做，需要帮助，并向所有人寻求帮助！

As you can see i have to list the companies in which the partners have an access to a website projedepo, but instead it repeats the name of the company each time the partner inside the company have an access to this website, because it sorts the partners rather than companies, how do i delete these repeating companies? i saw that there is a way to achieve it though deleting the repeating the same company id, but how to do it, i dont know, need help, and thx everyone for help!

推荐答案

尝试以下方法：

<cfquery name="GET_POT_COMPANY" datasource="#DSN#">
    SELECT
        C.COMPANY_ID,
        C.MEMBER_CODE,
        C.FULLNAME,
        C.PARTNER_ID,
        C.RECORD_DATE,
        CC.COMPANYCAT
          <cfif isdefined('attributes.report_sort2')
            and attributes.report_sort2 is 1>
            , 'www.projedepo.com' AS SITE_DOMAIN
          </cfif>
    FROM 
        COMPANY C, 
        COMPANY_CAT CC
    WHERE
          C.COMPANYCAT_ID = #attributes.comp_cat# 
      AND CC.COMPANYCAT_ID = C.COMPANYCAT_ID
        <cfif isdefined('attributes.report_sort2')
          and attributes.report_sort2 is 1>
          AND EXISTS
            ( SELECT *
              FROM
                COMPANY_CONSUMER_DOMAINS CCD,
                COMPANY_PARTNER CPA
              WHERE C.COMPANY_ID = CPA.COMPANY_ID
                AND CPA.PARTNER_ID = CCD.PARTNER_ID
                AND CCD.SITE_DOMAIN = 'www.projedepo.com'
            )
        </cfif>
    ORDER BY 
        C.RECORD_DATE DESC
</cfquery>

如果您使用 JOIN 语法，而不是 WHERE 的（隐式加入）：

It would also be good if you used the JOIN syntax and not the (implicit JOIN with) WHERE:

<cfquery name="GET_POT_COMPANY" datasource="#DSN#">
    SELECT
        C.COMPANY_ID,
        C.MEMBER_CODE,
        C.FULLNAME,
        C.PARTNER_ID,
        C.RECORD_DATE,
        CC.COMPANYCAT
          <cfif isdefined('attributes.report_sort2')
            and attributes.report_sort2 is 1>
            , 'www.projedepo.com' AS SITE_DOMAIN
          </cfif>
    FROM   COMPANY C
      JOIN COMPANY_CAT CC
          ON CC.COMPANYCAT_ID = C.COMPANYCAT_ID
    WHERE
          C.COMPANYCAT_ID = #attributes.comp_cat# 
        <cfif isdefined('attributes.report_sort2')
          and attributes.report_sort2 is 1>
          AND EXISTS
            ( SELECT *
              FROM   COMPANY_CONSUMER_DOMAINS CCD
                JOIN COMPANY_PARTNER CPA
                    ON CPA.PARTNER_ID = CCD.PARTNER_ID
              WHERE C.COMPANY_ID = CPA.COMPANY_ID
                AND CCD.SITE_DOMAIN = 'www.projedepo.com'
            )
        </cfif>
    ORDER BY C.RECORD_DATE DESC
</cfquery>

这篇关于sql从列表中取出一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！