SQL-如何选择两列中具有相同值的不同行

Question

我有这个输入表

+--------+---------+---------+-------+-----------+
| TaskId | member1 | member2 | score | functions |
+--------+---------+---------+-------+-----------+
|      1 | Jack    | Jack    |   100 | marketing |
|      1 | Jack    | Jack    |   100 | marketing |
|      2 | Jack    | Steve   |    90 | interior  |
|      2 | Jack    | Steve   |    90 | interior  |
|      3 | Steve   | Jack    |    70 | program   |
|      3 | Steve   | Jack    |    70 | program   |
|      4 | Jack    | Mia     |    30 | develop   |
|      4 | Jack    | Mia     |    30 | develop   |
|      5 | Mia     | Jack    |    20 | interior  |
|      5 | Mia     | Jack    |    20 | interior  |
+--------+---------+---------+-------+-----------+

我计算了两件事

• UniqueHighTasks:Distinct #Of tasks that (for eg:) Jack existed in score(75-100) whether he is in member 1 or 2
• UniqueLowTasks:Distinct #Of tasks that (for eg:) Jack existed in score(0-100) whether he is in member 1 or 2
• 我排除了 if member1 = member2 用于该计算

现在，我想统计 Jack 在 member1 或 member2< 中存在的不同函数数/

• 例如:member1 中的 Jack 在 (interior,develop)

• 成员 2 中的 Jack 在(程序，内部)

我的最终结果的计数是 4，这是错误的，我怎样才能得到 Jack 的 distinct count 是 3，它们是 (interior,develop,program)，其余名称也是如此。

SQL Fiddle Code

Answer 1

您可以使用 UNION ALL 将两个集合组合在一起，并在一个地方应用您的逻辑，而不是将您的逻辑应用于单个集合。

SELECT member, 
       uniquehightasks, 
       uniquelowtasks, 
       [%High] = uniquehightasks * 100.0 / ( uniquehightasks + uniquelowtasks ), 
       [%Low] = uniquelowtasks * 100.0 / ( uniquehightasks + uniquelowtasks ), 
       functions 
FROM   (SELECT member, 
               Sum(uniquehightasks) AS 'UniqueHighTasks', 
               Sum(uniquelowtasks)  AS 'UniqueLowTasks', 
               Sum(functions)       AS 'functions' 
        FROM   (SELECT member, 
                       UniqueHighTasks = Count(DISTINCT CASE WHEN score >= 75 THEN [taskid] END), 
                       UniqueLowTasks = Count(DISTINCT CASE  WHEN score < 75 THEN  [taskid] END), 
                       functions=Count(DISTINCT functions) 
                FROM   (SELECT [taskid], member1 AS Member, functions, score 
                        FROM   mytable 
                        WHERE  member1 != member2 
                        UNION ALL
                        SELECT [taskid], member2 AS member,functions, score 
                        FROM   mytable 
                        WHERE  member1 != member2) t22 
                GROUP  BY member)t3 
        GROUP  BY t3.member) t4 

输出

+--------+-----------------+----------------+-----------------+------------------+-----------+
| member | UniqueHighTasks | UniqueLowTasks | %High           | %Low             | functions |
+--------+-----------------+----------------+-----------------+------------------+-----------+
| Jack   | 1               | 3              | 25.000000000000 | 75.000000000000  | 3         |
+--------+-----------------+----------------+-----------------+------------------+-----------+
| Mia    | 0               | 2              | 0.000000000000  | 100.000000000000 | 2         |
+--------+-----------------+----------------+-----------------+------------------+-----------+
| Steve  | 1               | 1              | 50.000000000000 | 50.000000000000  | 2         |
+--------+-----------------+----------------+-----------------+------------------+-----------+

DEMO