需要帮助为复杂场景构建SQL查询

Question

我需要帮助为稍微复杂的场景构建SQL查询。我有一个与以下下拉列表的接口。

美食:任何，中国，印度，意大利，法国

送货类型:任何，送货，收件，进餐

用餐类型:任何，早餐，午餐，晚餐

厨艺桌

CuisineId  | Type
-------------------
     1     | Chinese
     2     | Indian
     3     | Italian
     4     | French

传递类型表

DeliveryTypeId | Type
--------------------------
         1     | Delivery
         2     | Pickup
         3     | Dine In

餐型桌

    MealTypeId | Type
--------------------------
         1     | Breakfast
         2     | Lunch
         3     | Dinner

食品桌

    Food            CuisineId   MealTypeId      DeliveryTypeId
---------           -------     ---------       -------------
Cakes               NULL         NULL            NULL
Pizza               3 (Italian)  NULL            NULL
Noodles             1 (Chinese)  2 (Lunch)       NULL
Butter Chicken      2 (Indian)   3 (Dinner)      1 (Delivery)
Ice cream           NULL         2 (Lunch)       2 (Pickup)
Soup                NULL        NULL             2 (Pickup)
Drinks              NULL        NULL             1 (Delivery)

请注意:在上表中，NULL表示从下拉列表中选择的任何内容。

这里的逻辑如下：

烹饪:如果该选项被选中为Any，或者如果所选选项在Food表的对应列中没有匹配值，则在Cuisine列中显示空项。

传递类型:除非选择了收件或Dine，否则默认应为传递/空

套餐类型:除非选择了任何特定选项，否则只显示空(任意)项。

例如，请根据选择找到示例输出。

Cuisine     Meal type       Delivery Type       Output
-------     ----------      -------------       ------
Any         Any             Any                 Cakes,Drinks
Any         Any             Delivery            Cakes,Drinks
Any         Lunch           Any                 NO RECORDS
Any         Any             Pickup              Soup
French      Any             Any                 Cakes,Drinks
French      Any             Delivery            Cakes,Drinks
French      Lunch           Any                 NO RECORDS
French      Any             Pickup              Soup
Chinese     Any             Any                 NO RECORDS
Chinese     Lunch           Any                 Noodles
Indian      Any             Any                 NO RECORDS
Indian      Dinner          Any                 Butter Chicken

如何为上述场景构建SQL查询。任何帮助都会得到高度认可。提前谢谢。

查询将在存储的proc中使用，所传递的参数将是@cusineType (如中文或法文)、@交货类型(类似于传递或拾取)和@mealType (如早餐或午餐)或NULL。

上面带有数据的表的脚本如下：script.sql

Answer 1

我不想把诺尔斯放在餐桌上，坚持你的模式

declare @cuisine nvarchar(10) = 'Chinese'
declare @mealtype nvarchar(10) = 'Lunch'
declare @deliverytype nvarchar(10) = 'Any'

select @cuisine = (select cuisineid from #cuisine where v_type = @cuisine)
select @deliverytype = (select deliverytypeid from #delivery where v_type = @deliverytype)
select @mealtype = (select mealtypeid from #meal where v_type = @mealtype)

if not exists (select 1 from #food where cuisineid = @cuisine)
    select @cuisine = null

;with t1 as (
    select f.food
      from #food f
      left join #cuisine c
        on f.cuisineid = c.cuisineid
      where 1=1
        and f.cuisineid is null
        and @cuisine is null
    union

    select f.food
      from #food f
      join #cuisine c
        on f.cuisineid = c.cuisineid
      where 1=1
        and c.cuisineid = @cuisine
), t3 as (
    select f.food
      from #food f
      left join #meal c
        on f.meatltypeid = c.mealtypeid
      where 1=1
        and f.meatltypeid is null
        and @mealtype is null
    union

    select f.food
      from #food f
      join #meal c
        on f.meatltypeid = c.mealtypeid
      where 1=1
        and f.meatltypeid = @mealtype
), t5 as (
    select f.food
      from #food f
      left join #delivery c
        on f.deliverytypeid = c.deliverytypeid
      where 1=1
        and f.deliverytypeid is null
        and @deliverytype is null

    union

    select f.food
      from #food f
      where 1=1
        and f.deliverytypeid = 1
        and @deliverytype is null       

    union

    select f.food
      from #food f
      join #delivery c
        on f.deliverytypeid = c.deliverytypeid
      where 1=1
        and f.deliverytypeid = @deliverytype

    union

    select f.food
      from #food f
      where 1=1
        and (f.deliverytypeid is null or f.deliverytypeid = 1)
        and (@deliverytype = 1)

)

select t.food
  from (
select t1.food
  from t1
  join t3
    on t1.food = t3.food
) t
join t5
  on t.food = t5.food

db小提琴，它包含测试模式和数据。

Answer 2

准确地说，您将如何传递参数值？

在我的示例中，我是在变量中传递参数值。

请说清楚，我知道上面的输出只是为了解释。

create table #Cuisine(CuisineId int,CuisineType varchar(50))
insert into #Cuisine values
( 1 ,'Chinese'),( 2 ,'Indian'),( 3 ,'Italian'),( 4 ,'French')

create table #DeliveryType (DeliveryTypeId int, DeliveryType varchar(50))
insert into #DeliveryType values
 (  1,'Delivery') ,(  2,'Pickup') ,(  3,'Dine In')

  create table #MealType (MealTypeId int, MealType varchar(50))
 insert into #MealType values
(1,'Breakfast')
,(2,'Lunch')
,(3,'Dinner')


 create table #Food(Food  varchar(50),CuisineId int,MealTypeId int,DeliveryTypeId int)
insert into #Food values
('Cakes',               NULL ,        NULL,          NULL)
,('Pizza',               3 ,  NULL,            NULL)
,('Noodles',             1 ,  2 ,       NULL)
,('Butter Chicken',      2  ,  3,       1 )
,('Ice cream',           NULL,         2 ,      2 )
,('Soup',                NULL,        NULL,             2 )
,('Drinks',              NULL,        NULL,             1 )

参数值，

Declare @Cuisine int,@Mealtype int,@DeliveryType int=2

Since，您的条件是如果传递是Any，那么同时考虑null、Delivery=1和vice versa。

因此，创建一个本地temp table来定义value.It是easier和cost effective to join，而不是写complicated和expensive where条件。

Create table #DeliveryParameter (DeliveryTypeId int)

if(@DeliveryType is null or @DeliveryType=1)
insert into #DeliveryParameter values(-1),(1)
else
insert into #DeliveryParameter values(@DeliveryType)


select * from #Food F
inner join #DeliveryParameter DP on f.DeliveryTypeId=dp.DeliveryTypeId
where ((@Cuisine is null and CuisineId is null) or(CuisineId=@Cuisine)) 
and ((@Mealtype is null and MealTypeId is null) or(MealTypeId=@Mealtype)) 

drop table #Cuisine,#DeliveryType,#MealType,#Food,#DeliveryParameter

注意：((@Cuisine is null and CuisineId is null) or(CuisineId=@Cuisine))

如果你的记录是数百万的话，这种情况是非常昂贵的。

因此，如果您将Null或Any视为-1或0，则条件将简化为

CuisineId=@Cuisine

就像在#Food表中，存储-1而不是Null，在variABLE中传递-1而不是null。

如果您需要其他格式的输出，那么请提到它。