在where子句中使用多个列值

Question

我需要像时间表一样每周显示单个用户的日程安排，

场景：某个教师在同一周内被分配到多个批次中（例如：BBA、数学和上午1、2小时）&（MBA、数学、上午3&4小时），在同一日期（2015年6月30日）。一个网格视图的一行将存储为1，第二行存储为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

- Prhem

---

这是一个糟糕的表格设计。您不应该存储数据以模仿某些显示/表单。应将数据解除枢轴，使所有例如“主题”数据都出现在同一列中。然后添加一个额外的列，可以存储1-5的数字，并给该列一个名称，解释该数据代表什么（您目前错误地嵌入到表格元数据中的数据）。 - Damien_The_Unbeliever

谢谢您的建议。如果我能够在where子句中使用它来显示教师日程安排的详细信息，那将非常有帮助，同时我会尝试按照您的建议更改表定义。 - Prhem

在更改表定义时，请记住日期值应存储在日期数据类型中，而nvarchar(max)适用于极长的Unicode字符串。由于您的字符串似乎既短又是普通英语，因此您可以使用varchar(50)或任何其他适合您需求的长度。另外，我建议规范化您的数据 - 为主题、批次和故障等分别建立一个表。 - Zohar Peled

Nvarchar(max) - 不好的想法!!! - Pepys

你的表格没有规范化。请修正你的数据库设计。 - SQL Police

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'

你也可以将主题规范化。

- Addy G

Answer 2

我将作为独立的答案进行添加。请尝试这个：

--INSERT INTO dbo.test(datedif,[hour],[subject],faculty,batch)
--VALUES
--('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');

WITH CTE_Hours
AS
(
    SELECT
            1 AS hour1,
            MAX(CASE WHEN [Hour] = 1 THEN [subject] END)    AS subject1,
            MAX(CASE WHEN [Hour] = 1 THEN [batch] END)      AS batch1 ,

            2 AS hour2,
            MAX(CASE WHEN [Hour] = 2 THEN [subject] END)    AS subject2,
            MAX(CASE WHEN [Hour] = 2 THEN [batch] END)      AS batch2

            --etc...
    FROM dbo.test
    WHERE faculty = 'Amal'
)

SELECT *
FROM    (
            SELECT DISTINCT datedif
            FROM dbo.test
        ) A
CROSS JOIN CTE_Hours

- Stephan

Answer 3

以下是如何规范化您的数据，这将使查询变得更加简单。

使用您的表定义，我插入了您的数据。

INSERT INTO test(Datedif,hour1,subject1,faculty1,hour2,subject2,faculty2,hour3,subject3,faculty3,batch)
VALUES  ('30-06-2015',1,'Maths','Kevin',1,'Maths','Kevin',1,'Science','Amal','BBA'),
        ('30-06-2015',1,'Science','Amal',1,'Science','Amal',1,'Maths','Kevin','MBA');

然后我为您的数据创建了一个新的规范化结构，使用更窄的数据类型。这有助于保持数据的清洁，同时不浪费存储空间，意味着查询将更快，因为它们不必处理那么多数据。

CREATE TABLE new_test
(
    dt          DATE            NULL,
    hr          TINYINT         NULL, --holds values between 0 to 255
    subj        VARCHAR(100)    NULL, --plenty big enough. No need for NVARCHAR unless you are using Unicode characters
    faculty     VARCHAR(100)    NULL,
    attendance  BIT             NULL,
    dayweek     TINYINT         NULL,
    weekmonth   TINYINT         NULL,
    section     VARCHAR(100)    NULL, --not sure what this is
    sess        VARCHAR(100)    NULL, --not sure what this is
    batch       CHAR(3)         NULL  --looks like there are three character codes
);

这里是我对您的数据进行标准化的地方。我不确定您是否有出席2、3、4等...如果您的实际表格中有，那么您应该修复我的代码。

注意：我从日期计算出了日期星期和日期月份。我尽力猜测了它们，如果它们有误，那么请随意调整！

WITH norm_data
AS
(
SELECT datedif,hour1,subject1,faculty1,attendence1,dayweek,weekmonth,section,[session],batch FROM test
UNION ALL
SELECT datedif,hour2,subject2,faculty2,attendence1,dayweek,weekmonth,section,[session],batch FROM test
UNION ALL
SELECT datedif,hour3,subject3,faculty3,attendence1,dayweek,weekmonth,section,[session],batch FROM test
UNION ALL
SELECT datedif,hour4,subject4,faculty4,attendence1,dayweek,weekmonth,section,[session],batch FROM test
UNION ALL
SELECT datedif,hour5,subject5,faculty5,attendence1,dayweek,weekmonth,section,[session],batch FROM test
)

INSERT INTO new_test
SELECT  PARSE(datedif AS DATE USING 'de-DE') AS [datedif],
        hour1,
        subject1,
        faculty1,
        attendence1,
        DATEPART(WEEKDAY,PARSE(datedif AS DATE USING 'de-DE')) AS dayweek,
        datepart(day, datediff(day, 0, PARSE(datedif AS DATE USING 'de-DE'))/7 * 7)/7 + 1 AS weekmonth,
        section,
        [session],
        batch
FROM norm_data

现在让我们来看看你的新表格

SELECT *
FROM new_test

如果是正确的话，那么下面是如何重命名表的方法。

EXEC SP_rename  @objname = 'test', --if you don't want to drop the old table
                @newname = 'test_old'

EXEC SP_rename  @objname = 'new_test', --now give the new table the actual name
                @newname = 'test'


SELECT *
FROM test

- Stephan

---

我已根据您的建议更改了我的表格。 您可以在上面看到我的更改后的表格。 我该如何将它们连接并显示在网格视图中？ 请给我一些建议。 像上面的网格视图一样，我应该得到输出。 - Prhem