c# - where 子句中的多个列值

Question

我需要显示每周的单个用户时间表，如时间表，

场景:一个教员在同一天被分配到多个批处理(例如:BBA、数学和下午 1 小时和 2 小时)和(MBA、数学、下午 3 和 4 小时)在同一日期说(30-06 -2015).I 行的 gridview 将存储为 1 行和 2 行存储为 2 等等.........

我的表格定义:

CREATE TABLE [dbo].[test] (
    [datedif]     NVARCHAR (50)  NOT NULL,
    [hour]         INT              NULL,
    [subject]    NVARCHAR (MAX) NULL,
    [faculty]    NVARCHAR (MAX) NULL,
    [attendence] BIT            NULL,
    [dayweek]     NVARCHAR (50)  NULL,
    [weekmonth]   NVARCHAR (MAX) NULL,
    [batch]       NVARCHAR (MAX) NULL,
    [section]     NVARCHAR (MAX) NULL,
    [session]     NVARCHAR (MAX) NULL
);

表格看起来像:

Datefdiff | hour | subject | faulty| batch
----------+-------+----------+---------+-------+----------+--------+-------+----------+---------+-------
30-06-2015| 1| Maths    | Kevin   | BBA
30-06-2015| 2| Science  | Amal    | MBA
30-06-2015|3 | chemistry|Jaya     |BBA
30-06-2015|4 | chemistry|Jaya     |BBA 
30-06-2015|5 | chemistry|Jaya     |BBA
31-06-2015 |1| science  | Amal    |BBA
31-06-2015 |2| Maths    | kevin   |BBA 
31-06-2015 |3| Science  | Amal    |BBA 
31-06-2015 |4 | chemistry|Jaya     |BBA
31-06-2015 |5| science  | Amal    |BBA

仅为教员提供的预期输出:Amal

Datefdiff |hour|subject| batch |hour|subject | batch |faculty|hour | subject | batch | hour | subject | batch| hour | subject | batch | 
----------+-------+----------+-------+-------+----------+-------+-------+----------+
30-06-2015| 1    | Maths| BBA| 2| Science  | MBA   | 3| Science  |   BBA| 4| chemistry| BBA | 5 |Physics |MBA
31-06-2015| 1    | Maths| BBA| 2| Science  | MBA   | 3| Science  |   BBA| 4| chemistry| BBA | 5 |Physics |MBA

Answer 1

这可能是您设计表格的起点:

declare @tbFaculty table (
      FacultyID int --identity(1,1) primary key
    , Name varchar(50)
)

insert into @tbFaculty ( FacultyID, Name )
values    ( 1, 'Kevin' )
        , ( 2, 'Amal' )

declare @tbBatch table(
      BatchID int --identity(1,1) primary key
    , Name char(3)
)

insert into @tbBatch ( BatchID, Name )
values    ( 1, 'BBA' )
        , ( 2, 'MBA' )

declare @tbClass table (
      [Hour] tinyint
    , [Subject] nvarchar (128)
    , [FacultyID] int
    , [Attendence] bit
    , [BatchID] char(3)
    , [ClassDate] date
)

insert into @tbClass ( [Hour], [Subject], FacultyID, Attendence, BatchID, ClassDate )
values    ( 1, 'Maths', 1, 1, 1, '2015-06-30' )
        , ( 2, 'Maths', 1, 1, 1, '2015-06-30' )
        , ( 3, 'Science', 2, 1, 1, '2015-06-30' )
        , ( 1, 'Science', 2, 1, 2, '2015-06-30' )
        , ( 2, 'Science', 2, 1, 2, '2015-06-30' )
        , ( 3, 'Maths', 1, 1, 2, '2015-06-30' )

select
    cl.ClassDate
    , cl.[Hour]
    , cl.[Subject]
    , ba.Name as BatchName
    , fa.Name as FacultyName
from
    @tbClass cl
    inner join @tbBatch ba on ba.BatchID = cl.BatchID
    inner join @tbFaculty fa on fa.FacultyID = cl.FacultyID
where
    fa.Name = 'Amal'

您也可以规范化主题。