如何使用Where条件从一个表中两次检索同一列 [英] How to retrieve same column twice from one table with Where condition

问题描述

我正在尝试从一个表中两次检索一列，例如:

I am trying to retrieve a column twice from one table for ex:

select M.Event_Name as 'Male',
       F.Event_Name as 'Female' 
from   Table1 M, Table1 F
where  M.Gender = 'M'
       and F.Gender = 'F'
       and F.Country = 12
       and M.Country = 12

表1数据

ID    Event_Name   Gender  Country
1     Cricket      M       12
2     FootBall     M       13
3     BasketBall   M       12
4     Hockey       M       12
5     Tennis       M       13
6     Volly Ball   M       13
7     Cricket      F       13
8     FootBall     F       13
9     BasketBall   F       12
10    Hockey       F       13
11    Tennis       F       12
12    Volly Ball   F       12

我得到的是:

Male           Female
Cricket        Tennis
Cricket        BasketBall
Cricket        Volly ball
BasketBall     Tennis
BasketBall     BasketBall
BasketBall     Volly ball
Hockey         Tennis
Hockey         BasketBall
Hockey         Volly ball

期望:

Male          Female
Cricket       Tennis
BasketBall    BasketBall
Hockey        Volly ball

帮帮我..谢谢

推荐答案

您应该可以使用包含PIVOT的类似内容:

You should be able to use something like this which incorporates a PIVOT:

select M as Male, 
  F as Female
from
(
  select event_name, gender,
    row_number() over(partition by gender, country order by id) rn
  from yourtable
  where gender in ('M', 'F')
    and country = 12
) src
pivot
(
  max(event_name)
  for gender in (M, F)
) piv

请参见带有演示的SQL小提琴

或者您可以在CASE语句中使用聚合函数:

Or you can use an aggregate function with a CASE statement:

select 
  max(case when gender = 'M' then event_name end) male,
  max(case when gender = 'F' then event_name end) female
from
(
  select event_name, gender,
      row_number() over(partition by gender, country order by id) rn
  from yourtable 
  where gender in ('M', 'F')
    and country = 12
) src
group by rn

请参见带有演示的SQL提琴

两者都会产生相同的结果:

Both produce the same result:

|       MALE |     FEMALE |
---------------------------
|    Cricket | BasketBall |
| BasketBall |     Tennis |
|     Hockey | Volly Ball |

这篇关于如何使用Where条件从一个表中两次检索同一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！