如何对一系列值对运行查询并将结果存储在临时表中？

Question

假设我有一个值数组：

(A1，a1，A2，a2，A3，a3，A4，a4.....，An，an)

我应该如何对(A1，a1..... )自动执行和运行下面的TSQL查询，An，an)对

SELECT COUNT (1)
FROM food
WHERE calories = Ai AND ingredient = ai --i = {1..n}

以使(Ai，ai) {i=1..n}获得的每个计数都存储在一个临时表中？

谢谢

Answer 1

将数组中的值插入到临时表中(稍后我将对结果使用相同的值)：

create table #pairs (
    calories    varchar(50),
    ingredient  varchar(50),
    count       int
)

然后，我们可以一步得到我们的结果：

UPDATE p
SET count = x.count
FROM #pairs p inner join
    (   SELECT f.calories, f.ingredient, COUNT(*) as count
        FROM food f inner join
            #pairs p ON f.calories = p.calories and f.ingredient = p.ingredient
        GROUP BY f.calories, f.ingredient
    ) x ON p.calories = x.calories and p.ingredient = x.ingredient


select * from #pairs

Answer 2

您可以使用dynamix SQL完成此操作，如下所示：

declare @count int, @limit int
select @count = 0, @limit = 5 -- limit is n

declare @sqlcmd varchar(300)

-- table
create table #result (
combination varchar(10),
total int
)

while @count < @limit
begin
  select @sqlcmd = 'insert into #results select distinct "(" + ingredient + "," + calories + ")", count(*) from food where calories = A' + convert(varchar, @count) + ' and ingredient = a' + convert(varchar, @count)

  exec(@sqlcmd)

  select @count = @count + 1
end