代码之家 › 专栏 › 技术社区 › ookie

SQL添加价格以生成蛋糕表

multi-table multiple-columns sql-server sql

-1

ookie · 技术社区 · 10 年前

我真的希望有人能帮助我完成这个sql查询,一直在绞尽脑汁,但我知道这是可能的…这是我当前的查询并生成正确的格式:

DECLARE
@Price1 NVARCHAR(20),
@Price2 NVARCHAR(20),
@Price3 NVARCHAR(20),
@Price4 NVARCHAR(20)

SET @Price1 = (select Price from CakeSize where SizeId = '1')
SET @Price2 = (select Price from CakeSize where SizeId = '2')
SET @Price3 = (select Price from CakeSize where SizeId = '3')
SET @Price4 = (select Price from CakeSize where SizeId = '4')

SELECT

c.Name_en as Flavor,
@Price1 as Price1,
@Price2 as Price2,
@Price3 as Price3,
@Price4 as Price4


FROM
cake a
 Left outer JOIN CakeSize b ON a.SizeId = b.SizeId
 Left outer JOIN CakeFlavor c ON a.FlavorId = c.FlavorId
 Left outer JOIN CakeFilling d ON a.FillingId = d.FillingId
 Left outer JOIN CakeIcing f ON a.IcingId = f.IcingId
group by c.Name_en

我似乎无法从所有的桌子和显示器上得到所有价格的总和。

我可以检索数据,但不能像上面那样格式化?

SELECT 

   c.Name_en as Flavor,
   ISNULL(b.Price, 0) + ISNULL(c.Price, 0) + ISNULL(d.Price, 0) +  ISNULL(f.Price, 0) as aPrice,
   ISNULL(b.Price, 0) + ISNULL(c.Price, 0) + ISNULL(d.Price, 0) + ISNULL(f.Price, 0) as bPrice,
  ISNULL(b.Price, 0) + ISNULL(c.Price, 0) + ISNULL(d.Price, 0) + ISNULL(f.Price, 0) as cPrice,
    ISNULL(b.Price, 0) + ISNULL(c.Price, 0) + ISNULL(d.Price, 0) + ISNULL(f.Price, 0) as dPrice

FROM
   cake a
          Left Outer JOIN CakeSize b
                 ON a.SizeId = b.SizeId
          Left Outer JOIN CakeFlavor c
                 ON a.FlavorId = c.FlavorId
          Left Outer JOIN CakeFilling d
                 ON a.FillingId = d.FillingId
          Left Outer JOIN CakeIcing f
                 ON a.IcingId = f.IcingId

我想得到上面的输出,而不是4行巧克力蛋糕;1排巧克力蛋糕。(胡萝卜蛋糕比其他蛋糕少5美元) 数据正确,格式错误列aPrice行1、2、3、4包含巧克力蛋糕的正确值。

(想要每种口味的以下格式)

巧克力18.95 18.95 23.50 38.50

2 回复 | 直到 10 年前

Community Mohan Dere 9 年前

好啊所以你想得到按大小分组的所有口味的总价格, 总价格是根据每种口味的价格+馅料的价格+冰块的价格计算的,对吗?

选项1。如果尺寸是静态的并且从不改变, 你可以这样做

select Flavor, SUM(aPrice) as aPrice, SUM(bPrice) as bPrice, SUM(cPrice) as cPrice, SUM(dPrice) as dPrice
from (
    select c.Name_en as Flavor, 
        ISNULL(b.Price, 0) + ISNULL(c.Price, 0) + ISNULL(d.Price, 0) +  ISNULL(f.Price, 0) as aPrice,
        0 as bPrice, 0 as cPrice, 0 as dPrice
    from cake a 
        Left JOIN CakeSize b ON a.SizeId = b.SizeId
        Left JOIN CakeFlavor c ON a.FlavorId = c.FlavorId
        Left JOIN CakeFilling d ON a.FillingId = d.FillingId
        Left JOIN CakeIcing f ON a.IcingId = f.IcingId
    where b.sizeid = '1'

    UNION ALL

    select c.Name_en as Flavor, 0 as aPrice,
        ISNULL(b.Price, 0) + ISNULL(c.Price, 0) + ISNULL(d.Price, 0) +  ISNULL(f.Price, 0) as bPrice, 
        0 as cPrice, 0 as dPrice
    from cake a 
        Left JOIN CakeSize b ON a.SizeId = b.SizeId
        Left JOIN CakeFlavor c ON a.FlavorId = c.FlavorId
        Left JOIN CakeFilling d ON a.FillingId = d.FillingId
        Left JOIN CakeIcing f ON a.IcingId = f.IcingId
    where b.sizeid = '2'

    UNION ALL

    select c.Name_en as Flavor, 0 as aPrice, 0 as bPrice,
        ISNULL(b.Price, 0) + ISNULL(c.Price, 0) + ISNULL(d.Price, 0) +  ISNULL(f.Price, 0) as cPrice,
        0 as dPrice
    from cake a 
        Left JOIN CakeSize b ON a.SizeId = b.SizeId
        Left JOIN CakeFlavor c ON a.FlavorId = c.FlavorId
        Left JOIN CakeFilling d ON a.FillingId = d.FillingId
        Left JOIN CakeIcing f ON a.IcingId = f.IcingId
    where b.sizeid = '3'

    UNION ALL

    select c.Name_en as Flavor, 0 as aPrice, 0 as bPrice, 0 as cPrice, 
        ISNULL(b.Price, 0) + ISNULL(c.Price, 0) + ISNULL(d.Price, 0) +  ISNULL(f.Price, 0) as dPrice
    from cake a 
        Left JOIN CakeSize b ON a.SizeId = b.SizeId
        Left JOIN CakeFlavor c ON a.FlavorId = c.FlavorId
        Left JOIN CakeFilling d ON a.FillingId = d.FillingId
        Left JOIN CakeIcing f ON a.IcingId = f.IcingId
    where b.sizeid = '4'        
) a
where flavor is not null
group by Flavor

选项2。如果大小是动态的,可以在列和行之间转换。。。你可以在这里学习: Simple way to transpose columns and rows in Sql?

编辑:在第三和第四选择之间添加“UNION ALL”,感谢Sam Axe 编辑:在第二个查询中添加逗号编辑:添加“风味不为空”以避免风味为空

Raffaello.D.Huke 10 年前

我可以做到这一点,但我认为一定有更好的方法。所以我把这个放在这里作为备用答案

首先,我们可以立即创建一个表来存储表的数据,并添加一个大小列:

 SELECT 
  c.Name_en as Flavor,
  b.SizeId,
   ISNULL(b.Price,0)+ISNULL(c.Price,0)+ISNULL(d.Price,0)+ISNULL(f.Price,0) as Price -- do the price 4 times is meaningless so i cut them out just keep 1 left
 INTO #cakeP --i saw your tag as sqlserver so i create temp table like this
  FROM
  cake a
      Left Outer JOIN CakeSize b
             ON a.SizeId = b.SizeId
      Left Outer JOIN CakeFlavor c
             ON a.FlavorId = c.FlavorId
      Left Outer JOIN CakeFilling d
             ON a.FillingId = d.FillingId
      Left Outer JOIN CakeIcing f
             ON a.IcingId = f.IcingId

那么我们必须做一件蠢事:

    select 
       coalesce(a.Flavor,b.Flavor,c.Flavor,d.Flavor) as Flavor,
       a.price as aPrice,
       b.price as bPrice,
       c.price as cPrice,
       d.price as dPrice
       from 
         (select Flavor,size,price from #cakeP where SizeId=1) a
       full join (select Flavor,size,price from #cakeP where SizeId=2) b
            on a.Flavor = b.Flavor
       full join (select Flavor,size,price from #cakeP where SizeId=3) c
            on a.Flavor = c.Flavor
       full join (select Flavor,size,price from #cakeP where SizeId=4) d
            on a.Flavor = d.Flavor

它将被完成。

注:

如果Flavor没有1号,那么这将是错误的——好的,我想办法解决这个问题!
口味是1号,那么就可以了,但这会发生:

exp:口味巧克力大小1,3,4。那么数据将是这样的:巧克力10 null 30 40

希望这能帮助你