SQL:如何跟踪相关子查询中已经匹配的行? [英] SQL: How to keep track rows already matched in a correlated subquery?

问题描述

这是 SQL 专家面临的另一个挑战......

Here's another challenge for you SQL-gurus out there...

我在这里写了另一个关于匹配两个结果集的问题(如何匹配/比较 SQL Server 2008 中两个结果集中的值?).这个问题是那个问答的续集，但由于它是一个不同的主题，我将其创建为一个新问题.

I wrote another question here on matching two result sets(How to match/compare values in two resultsets in SQL Server 2008?). This question is a sequel to that question and answer, but since its a different topic I'm creating it as a new question.

Quassnoi 建议使用以下解决方案来选择满足给定项目所需技能的所有用户:

Quassnoi suggests the following solution to selecting all the users that meet the skills required by a given project:

SELECT * 
FROM   Users u  
WHERE  NOT EXISTS 
 ( 
 SELECT  NULL  
 FROM    ProjectSkill ps        
 WHERE   ps.pk_project = @someid               
 AND NOT EXISTS  
  (                
  SELECT  NULL                
  FROM    UserSkills us                
  WHERE   us.fk_user = u.id                        
          AND us.fk_skill = ps.fk_skill               
   )        
 )

这很好用.但是如果我想创建一个查询，它会列出特定日期的所有项目，包括他们最匹配的用户，该怎么办.我想象以下格式:

This works great. But what if I wanted to create a single query, that lists all the projects of a specific day including their best matching user. I imagine the following format:

projectid        userid
----------       -----------
1                1234
2                5678
3                4321
4                8765

重要的是，一个用户在列表中只被推荐一次！...因为项目是在同一天，用户不能重复预订.

It is important, that a user is only suggested once in the list! ... because the projects are on the same day and Users must not be double-booked.

现在，我可以想象一些 SQL:

Now, I can imagine some SQL like:

SELECT p.id 'projectid', ( SELECT TOP 1 u.id 
        FROM   Users u  
        WHERE  NOT EXISTS 
        ( 
             SELECT  NULL  
             FROM    ProjectSkill ps        
             WHERE   ps.pk_project = p.id 
             AND NOT EXISTS  
                 (                
          SELECT  NULL                
          FROM    UserSkills us                
          WHERE   us.fk_user = u.id                        
                 AND us.fk_skill = ps.fk_skill               
         )        
         )
         ORDER BY rating DESC
      ) 'userid'
FROM Projects p 
WHERE startdate > @beginningofday 
      AND startdate < @endofday

但是这(显然)很高兴地一次又一次地推荐同一个用户，只要他具备项目所需的技能.

But this (obviously) happily suggests the same user again and again, as long as he has the required skills for the projects.

有人对如何跟踪用户表中的哪些行在查询的早期已经匹配有任何建议吗?也许使用变量?还是有另一种聪明的方法来解决这个问题?

Does anybody have a suggestion on how to keep track of which rows in the Users table are already matches earlier in the query? A use of a variable maybe? Or is there another smart way around this that I'm missing?

查询必须在 SQL Server 2008 上运行.

The query must run on SQL Server 2008.

任何帮助将不胜感激！谢谢.

Any help will be greatly appreciated! Thanks.

问候亚历克斯

推荐答案

您的任务是 车子问题.

Your task is a classical example of rooks problem.

在SQL中无法有效解决.

如果您的工人可能具备所需技能(即，非熟练工人是罕见的例外而非规则)，则有一些简单的算法可以很好地工作.

There are some simple algorithms that work well if your workers are likely to have required skills (i. e. an unskilled worker is a rare exception rather than a rule).

但是，您最好使用 SQL 来检索限制，即.e.哪些用户适合(或不适合)哪些项目，并将它们输入到启发式算法中.

However, you better use SQL to retrieve the limitations, i. e. which users fit (or don't fit) which projects, and feed them into a heuristic algorithm.

这篇关于SQL:如何跟踪相关子查询中已经匹配的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！