SQL从一个表中取出每一行并在第二个表中总结 "hits"

Question

相当复杂...我有两个表，t1 包含有关特定对象的描述信息，第二个表 t2 包含用于解码 t1 中对象的信息。

我将尝试提供一个简单的示例:

----
t1 (Name, Type, Size, Color)
----
dog1 - mammal - big  -  brown

dog2 - mammal - big  -  black

cat1 - mammal - small - black

fish1 - fish - small - gold


---
t2 (Type, Size, Color, Value)
---
mammal - "" - blue - 1

mammal - big - brown - 0.5

mammal - big - black - 0.75

mammal - small - "" - 2

fish - big - "" - 5

fish - small - "" - 0.5

fish - small - gold - 0.5

"" - "" - black - 1

"" - big - "" - 0.75

---
result(name, value)
---
dog1 - 1.25 //receives a value of 1.25 (0.5+0.75) because it hits the row of being mammal/big/brown (0.5) and the extra hit of just being big (0.75)

dog2 - 2.5 // 0.75 + 1 + 0.75 for being mammal/big/black (0.75), for being black (1), and for being big (0.75)

cat1 - 2 //2 for being mammal/small

fish1 - 1 // 0.5 + 0.5 for being fish/small and fish/small/gold

---

这是我当前的查询，因为它与此示例有关，但当然由于多种原因它不起作用。

Select t1.Name, SUM(counter.Value) as Sums
 From
 (Select *
 From t1, t2
 where t1.Type = t2.Type and t1.Size = t2.Size and t1.Color = t2.Color)

Answer 1

试试这个(如果您使用的是空格，则将 t2.XXX is null 替换为 t2.XXX = ''):

declare @t1 table(name nvarchar(32), [type] nvarchar(32), size nvarchar(32), colour nvarchar(32))
declare @t2 table([type] nvarchar(32), size nvarchar(32), colour nvarchar(32), value numeric(15,9))

insert @t1
          select 'dog1','mammal','big','brown'
union all select 'dog2','mammal','big','black'
union all select 'cat1','mammal','small','black'
union all select 'fish1','fish','small','gold'

insert @t2
          select 'mammal',null,'blue',1
union all select 'mammal','big','brown',0.5
union all select 'mammal','big','black',0.75
union all select 'mammal','small',null,2
union all select 'fish','big',null,5
union all select 'fish','small',null,0.5
union all select 'fish','small','gold',0.5
union all select null,null,'black',1
union all select null,'big',null,0.75

select t1.name, sum(t2.value) Value
from @t1 t1
inner join @t2 t2
on (t1.type = t2.type or t2.type is null)
and (t1.colour = t2.colour or t2.colour is null)
and (t1.size = t2.size or t2.size is null)
group by t1.name

结果:

name    Value
cat1    3.000000000
dog1    1.250000000
dog2    2.500000000
fish1   1.000000000