sql - 对于每个样式的 SQL 查询

Question

基本上我想用伪代码做的是:

FOR EACH pig_id IN (SELECT pig_id FROM farm AS f)
BEGIN
-- Do something funky with the f.pig_id, for example
  SELECT bacon, ham, pork, (face + guts + brains + testicles) AS 'sausage'
  FROM farm
  WHERE pig_id = f.pig_id
END

循环的实际内部更复杂，但这个简单的 SELECT 语句演示了在循环中使用 f.pig_id FOR EACH pig_id in the farm TABLE。我看过 CREATE TRIGGER 解决方案，但我希望能找到更简单的东西。我知道这是一个低效的查询，但该项目需要对非技术人员进行简单且易于人类阅读的查询。

编辑:它被用于小型数据集，因此人类可读性比效率更重要。

Answer 1

如果 pig_id 是唯一列(例如数据类型为 int)，您可以使用不带 CURSOR 的循环

DECLARE @id int = (SELECT MIN(pig_id) FROM farm) 
WHILE (@id IS NOT NULL)
BEGIN  
  SELECT bacon, ham, pork, (face + guts + brains + testicles) AS 'sausage'
  FROM farm
  WHERE pig_id = @id

  SELECT @id = MIN(pig_id) FROM farm WHERE pig_id > @Id
END

或

DECLARE @id int = 0
WHILE 1 = 1
BEGIN
  SELECT @id = (select min(pig_id) from farm where pig_id > @id)     

  IF @id IS NULL
  BREAK
  ELSE

  SELECT bacon, ham, pork, (face + guts + brains + testicles) AS 'sausage'
  FROM farm
  WHERE pig_id = @id
  CONTINUE
END