c# - 选择具有不同别名和不同条件的同一列

Question

因此，我有一个员工工作时间表，我想按日期名称选择员工 ID，以便在 Crystal Report Visual Studio 2012 C# 中进行报告。

数据库

  Employee_ID  |   DayName   | WorkStart 
    EM0001     |   Monday    |  Morning  
    EM0002     |   Monday    |  Morning  
    EM0003     |   Monday    |  Noon     
    EM0004     |   Monday    |  Noon     
    EM0003     |   Thursday  |  Morning  
    EM0004     |   Thursday  |  Morning  
    EM0001     |   Thursday  |  Noon     
    EM0002     |   Thursday  |  Noon     

我已经尝试过了

SELECT Employee_ID as EmMonday FROM table WHERE dayName = 'Monday'
UNION
SELECT Employee_ID as EmThursday FROM table WHERE dayName = 'Thursday';

但它只显示 EmMonday，EmThursday 的显示情况如何？

就像下面这个

 EmMonday | EmThursday 
  EM0001  |   EM0003   
  EM0002  |   EM0004   
  EM0003  |   EM0001   
  EM0004  |   EM0002   

Answer 1

对于 MYSQL

SET @rank1=0;
SET @rank2=0;

select t1.Employee_ID as EmMonday, t2.Employee_ID as EmThursday from 
(SELECT @rank1 := @rank1+1  as id, Employee_ID FROM T WHERE dayName = 'Monday' ) as t2
left join 
(SELECT @rank2 := @rank2+1  as id, Employee_ID FROM T WHERE dayName = 'Thursday') t1 on t1.id = t2.id

Demo is here.

对于 MSSQL:

select t1.Employee_ID as EmMonday, t2.Employee_ID as EmThursday from 
(SELECT ROW_NUMBER() OVER (ORDER BY Employee_ID) as id, Employee_ID FROM T WHERE dayName = 'Monday' ) as t1
left join 
(SELECT ROW_NUMBER() OVER (ORDER BY Employee_ID) as id, Employee_ID FROM T WHERE dayName = 'Thursday') t2 on t1.id = t2.id

rank 和 RowNumber() 此处用于指示我们将在周一和周四加入的行号。

T 是您的表名称。