我对这个查询做了什么

lf3rwulv 于 2022-10-22 发布在其他

关注(0)|答案(1)|浏览(164)

晚上好，我现在正在做一个涉及解决这个问题的SQL案例研究。
所有交付的披萨中使用的每种配料的总量是多少？
计划是为每个顶部创建一个带有case语句的查询。如果一个比萨饼有一个额外的顶部，那么它将在相应的顶部列中计算为2。
带有pizza_id及其相应配料的table
|pizza_id |配料|
| ------------ | ------------ |
| 1 | 1, 2, 3, 4, 5, 6, 8, 10 |
| 2 | 4, 6, 7, 9, 11, 12 |
披萨_配料
|topping_id | topping_name|
| ------------ | ------------ |
|1|培根|
|2 |烧烤酱|
|3|牛肉|
|4|奶酪|
|5|鸡肉|
|6|蘑菇|
|7 |洋葱|
|8 |辣椒香肠|
|9 |辣椒|
|10 |萨拉米|
|11 |番茄|
|12 |番茄酱|
当我应用下面的查询时

;with ingredient_cte as (
 select c.order_id, c.pizza_id, exclusions, extras,
 case
     when c.pizza_id = 1 then 1
     when extras in ('1', '1', '1, 4') then 2
     else 0 
 end bacon,
 case
     when c.pizza_id = 1 or c.order_id != 10 and exclusions != '2, 6' then 1
     else 0
 end bbq_sauce,
 case
     when c.pizza_id = 1 then 1
     else 0 
 end beef,
 case
     when c.pizza_id in (1, 2) or exclusions != '4' then 1
     else 0
 end cheese
 from customer_orders c
 join runner_orders r
     on c.order_id = r.order_id
join pizza_names n
     on c.pizza_id = n.pizza_id
where cancellation is null
)
select *
from ingredient_cte

我有这张table
|order_id | pizza_id | exclusions | extras |培根|烤肉|牛肉|奶酪|
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
|1|1|NULL|NULL|1|1|1|1|
|2|1|NULL|NULL|1|1|1|1|
|3|1|NULL|NULL|1|1|1|1|
|3|2|NULL|NULL|0 |0 |1|
|4|1|4|空|1|1|1|1|
|4|1|4|空|1|1|1|1|
|4|2|4|NULL|0|1|0|1|
|5|1|空|1|1|1|1 |1|1|
|7|2|NULL|1|2|0|1|
|8|1|NULL|NULL|1|1|1|1|
|10|1|NULL|NULL|1|1|1|1|
| 10 | 1 | 2, 6 | 1, 4 | 1 | 1 | 1 | 1 |
我的查询的问题是它从我的一些case语句中返回了一些不正确的响应，例如，最后一行的培根列是2，因为它包含额外的培根（1，4）。此外，order_id 5的培根行也应该是2，这就是为什么我在查询中使用extras in（'1'，'1'，'1,4'）然后使用2的原因。

sql-server

来源：https://stackoverflow.com/questions/74104496/what-am-i-doing-wrong-with-this-query

1条答案

按热度按时间

kuhbmx9i1#

为了解决我的问题，我必须重新安排案例陈述的结构，首先从涉及额外配料（额外配料）和/或排除配料（排除配料）的条件开始，然后为配料和相应的比萨饼创建条件，例如培根配肉卷（又名pizza_id 1）。我希望我更新的解释有意义，并为浪费您的时间感到抱歉。

;with ingredient_cte as ( select c.order_id, customer_id, 
c.pizza_id, exclusions, extras,
case
    when extras = '1' then 2
    when extras = '1, 4' then 2
    when c.pizza_id = 1 then 1
    else 0 
end bacon,
case
    when exclusions = '2, 6' then 0
    when c.pizza_id = 1 then 1
    else 0 
end bbq_sauce,
case
    when c.pizza_id = 1 then 1
    else 0 
end beef,
case
    when exclusions = '4' then 0
    when extras = '1, 4' then 2
    when c.pizza_id in (1, 2) then 1
end cheese,
case
    when c.pizza_id = 1 then 1
    else 0 
end chicken,
case
    when exclusions = '2, 6' then 0
    when c.pizza_id = 2 then 1
    else 0
end mushrooms,
case
    when c.pizza_id = 2 then 1
    else 0 
end onions,
case
    when c.pizza_id = 1 then 1
    else 0
end pepperoni,
case
    when c.pizza_id = 2 then 1
    else 0
end peppers,
case
    when c.pizza_id = 1 then 1
    else 0 
end salami,
case
    when c.pizza_id = 2 then 1
    else 0
end tomatoes,
    case
    when  c.pizza_id = 2 then 1
    else 0
end tomato_sauce
from customer_orders c
join runner_orders r
    on c.order_id = r.order_id
join pizza_names n
    on c.pizza_id = n.pizza_id
where cancellation is null
)
select sum(bacon) bacon, sum(cheese) cheese, sum(beef) beef, 
sum(chicken) chicken, sum(pepperoni) pepperoni, 
sum(salami) salami, sum(bbq_sauce) bbq_sauce, sum(mushrooms) 
mushrooms, sum(onions) onions, sum(peppers) peppers, 
sum(tomatoes) tomatoes, sum(tomato_sauce) tomato_sauce
from ingredient_cte

赞(0）回复(0）举报 2022-10-22

我来回答

我对这个查询做了什么

1条答案

相关问题

热门标签

最新问答