SQL查询（pl / sql）用于复杂的逻辑

I have been stuck with this problem for a while. First I will explain the table structure

我一直坚持这个问题。首先,我将解释表结构

There are four tables in my system

我的系统中有四个表

Domain

Columns: 
      DomainID (primary key)
      DomainName

 Data:
        DomainID  DomainName
      1000        Google.com
      2000        mySql.com

WebPage

 Columns:
      WebpageID   (primary key)
      WebpageName
      DomainID   (FK from domain table)
 Data:
    5001   SearchPage.html   1000
    5002   Welcome.html      1000
    5003   ContactUs.htm     1000
    5004   AboutUs.html      1000

PluginType (PluginsType that can be added to a web page) This table lists the plugin types available for each Domain

PluginType(可添加到网页的PluginsType)此表列出了每个域可用的插件类型

  Columns: 
         PluginType     (primary key)
         DomainID       (primary key)
         PluginTypeName  

   Data
        PluginTypeID        DomainID        PluginTypeName
         8000                 1000             searchButton
         8001                 1000             DropDownMenu
         8002                 1000             InteractiveForm
         8003                 1000             loginForm
         8004                 1000             LogoutForm

Plugin: Each webpage in a domain can use any number of plugins.

插件:域中的每个网页都可以使用任意数量的插件。

      Columns:
             PluginID     (primary key)
             WebpageID    (FK from webpage table)
             pluginTypeID (FK from plugintype table)

      Data:
         pluginID        WebpageID(Name)         PluginTypeID
          10001             5001(SearchPage.html)   8000(SearchButton)
          10002             5001(SearchPage.html)   8001(DropDownMenu)
          10003             5002 (Welcome.html)     8000 (SearchButton)
          10004             5002 (Welcome.html)     8001 (DropDownMenu)
          10005             5002 (Welcome.html)     8004 (lotoutform)  
          10006             5003 (ContactUs.htm)    8003 (loginForm)
          10007             5004 (AboutUs.htm)      8002 (loginForm)

Now what I want is, given a domain ID, I want the list of all webpages-plugins available, in such a way that the plugin is not repeated in more than one webpage. To put in another way, webpage-plugin combination for each plugin, in such a way that the plugin is not repeated in more than one web page.

现在我想要的是,给定一个域ID,我想要所有可用的网页插件列表,这样插件不会在多个网页中重复。换句话说,每个插件的网页插件组合,以便插件不会在多个网页中重复。

So for the domain 1000(google.com)

所以对于域名1000(google.com)

The result I want is

我想要的结果是

          5002 (Welcome.html)     8000 (SearchButton)
          5002 (Welcome.html)     8001 (DropDownMenu)
          5002 (Welcome.html)     8004 (lotoutform)
          5003 (ContactUs.htm)    8003 (loginForm)

I have chosen only 5002 and 5003 webpages, because they include all the plugins for the domain 1000(google.com). There is one more thing, it is preferred to select a webpage with just one plugin. But I am interested in finding out the solution without this preference, later maybe I can improve on the solution.

我只选择了5002和5003个网页,因为它们包含了域名1000(google.com)的所有插件。还有一件事,最好选择只有一个插件的网页。但我有兴趣找到没有这种偏好的解决方案,后来也许我可以改进解决方案。

3 个解决方案

#1

You can look at it from the other perspective, since you are returning one row per plugin-type, you need all the plugin-typess on a domain, along with a sample page where that plugin-type is, well, pluged-in, something along the lines of:

你可以从另一个角度来看待它,因为你每个插件类型返回一行,你需要一个域上的所有插件类型,以及一个插件类型的示例页面,嗯,插入,类似的东西:

Select PluginTypeName, 
  (select top 1 WebpageName 
    from WebPage w
      inner join Plugin p on p.WebpageID = w.WebpageID
    where p.pluginTypeID = pt.pluginTypeID) as SampleWebPage
From PluginTypes pt

this will return something like:

这将返回如下内容:

searchButton    SearchPage.html
DropDownMenu    SearchPage.html
InteractiveForm null
loginForm       ContactUs.html
lotoutform      Welcome.html

#2

SELECT  PluginTypeId,
        (
        SELECT  webpageID
        FROM    plugin pi
        WHERE   pi.pluginTypeId = pd.pluginTypeId
                AND webPageID IN
                (
                SELECT  WebPageID
                FROM    WebPage
                WHERE   DomainId = 1000
                )
        ORDER BY
                (
                SELECT  COUNT(*)
                FROM    plugin pc
                WHERE   pc.webpageId = pi.webpageId
                )
        LIMIT 1
        ) AS WebPageId
FROM    Plugin pd
WHERE   WebPageId IS NOT NULL

#3

Above result (submitted by SWeko) should work, I was trying it out, may give repeated results. Also, it groups results per domain... Here is a modification:

上面的结果(由SWeko提交)应该有效,我正在尝试,可能会给出重复的结果。此外,它按域分组结果...这是一个修改:

Select DISTINCT DomainID, pt.PluginTypeName, 
  (select top 1 WebpageName 
    from WebPage w
      inner join Plugin p on p.WebpageID = w.WebpageID
    where p.pluginTypeID = pt.pluginTypeID) as SampleWebPage
From PluginTypes pt
GROUP BY DomainID, pt.PluginTypeName

#1