首页 文章

如何设计两组数据交集的SQL查询

提问于
浏览 次
3

我有这些表

create table Programs(
Id int identity(1,1) not null,
Days int not null,
Monday bit not null,
Tuesday bit not null,
Wednesday bit not null,
Thursday bit not null,
Friday bit not null,
Saturday bit not null,
Sunday bit not null)

create table ProgramItems(
Id int identity(1,1) not null,
ProgramId int not null,
ItemId int not null
IsActive bit not null)

create table Items(
Id int identity(1,1) not null,
Monday bit not null,
Tuesday bit not null,
Wednesday bit not null,
Thursday bit not null,
Friday bit not null,
Saturday bit not null,
Sunday bit not null)

create table CustomerProgram(
Id int identity(1,1) not null,
CustomerId int not null,
ProgramId int not null,
StartDate datetime not null)

当用户定义程序时，他应该执行以下操作1-定义程序日（周一，周二，周三，周五），例如2-选择程序项

例如，我想在网格中显示与这些天匹配的所有项目

周一，周二，周三，周四可用的项目将显示在网格中以供选择，因为计划日期与项目天数相同（周一，周二，周三）

但是如果项目可用，例如（星期四，星期六），则不应在网格中显示 .

我的问题如何在SQL中继续此查询？

select * 
from Items
where Id not in(select ItemId from ProgramItems
where ProgramId=1)
/* here i should pick only items that match with the program days*/

提前致谢

c# sql sql-server winforms sql-server-2008

3 回答

  • 2

    试试这段代码

    declare @program int
set @program=1

select * 
from Items i, Programs p
where not exists(select 1 from ProgramItems where p.Id=ProgramId and i.Id=ItemId) and
      ((p.Monday=1 and i.Monday=p.Monday) or
      (p.Tuesday=1 and i.Tuesday=p.Tuesday) or
      (p.Wednesday=1 and i.Wednesday=p.Wednesday) or
      (p.Thursday=1 and i.Thursday=p.Thursday) or
      (p.Friday=1 and i.Friday=p.Friday) or
      (p.Saturday=1 and i.Saturday=p.Saturday) or
      (p.Sunday=1 and i.Sunday=p.Sunday))
where p.Program=@program

    在这里工作DEMO

    回复于
  • 1

    以下返回项目至少设置了 Monday ， Tuesday 或 Wednesday 之一：

    select i.*
from Items i
where Id not in(select ItemId from ProgramItems where ProgramId = 1) and
      (Monday = 1 or Tuesday = 1 or Wednesday = 1);

    以下内容返回设置了所有三天的项目：

    select i.*
from Items i
where Id not in(select ItemId from ProgramItems where ProgramId = 1) and
      (Monday = 1 and Tuesday = 1 and Wednesday = 1);

    编辑：

    如果您有特定的程序，您可以：

    select i.*
from Items i join
     Programs p
     on p.id = @ProgramId and
        (i.Monday >= p.Monday and i.Tuesday >= p.Tuesday and i.Wednesday >= p.Wednesday and
         i.Thursday >= p.Thursday and i.Friday >= p.Friday and
         i.Saturday >= p.Saturday and i.Sunday >= p.Sunday
        )
where not exists (select 1
                  from ProgramItems pi
                  where pi.ProgramId = p.id and pi.ItemId = i.Id);
    回复于
  • 1

    当然这只是一个简单的加入问题？

    Select I.* FROM Items AS I
INNER JOIN ProgramItems AS PI
ON PI.ItemId = I.Id
INNER JOIN Program AS P
ON P.Id = PI.ProgramID
WHERE (I.Monday = 1 OR I.TuesDat = 1 OR I.WednesDay =1) --you get the picture
AND P.Id = 1

    但我必须质疑项目和程序表中星期一 - 星期日位字段的重复：为什么它应该存在于两个地方？实际上，我会删除where（I.day = 1 etc）子句并根据代码中所选值是1还是0来填充日历 .

    回复于

相关问题