php - 如何将数组分配给sql查询的变量

Question

我希望将查询结果(“A”)作为数组分配给另一个查询(“B”)的自定义变量。我意识到 sql 中不允许数组变量，所以我希望用 JSON 来做。 以下是示例:

Query Result A:
Staff ID | Mariage status | Kids Details |
I022144  | yes            |              |
I062541  | yes            |              |
I322411  | yes            |              |
I445211  | no             |              |
D235544  | yes            |              |

Query Result B:
Staff ID | Kids Name      | Kids Gender  | Kids Age  |
I022144  | Pete           | M            | 3          |
I022144  | Sarah          | F            | 5          |
I062541  | Don            | M            | 10         |
I322411  | Keith          | M            | 9          |
D235544  | John           | M            | 2          |
D235544  | Nancy          | F            | 13         |
D235544  | Don            | M            | 1          |

JSON 格式的预期结果

Dataset: [
  {"Staff ID": I022144, "Mariage status": yes, "Kids Details": [{"Kids Name": Pete, "Kids Gender": M, "Kids Age": 3}, {"Kids Name": Sarah, "Kids Gender": F, "Kids Age": 5}]},
  {"Staff ID": I062541, "Mariage status": yes, "Kids Details": [{"Kids Name": Don, "Kids Gender": M, "Kids Age": 10}]},
  {"Staff ID": I322411, "Mariage status": yes, "Kids Details": [{"Kids Name": Keith, "Kids Gender": M, "Kids Age": 9}]},
  {"Staff ID": I445211, "Mariage status": no, "Kids Details": []},
  {"Staff ID": D235544, "Mariage status": yes, "Kids Details": [{"Kids Name": John, "Kids Gender": M, "Kids Age": 2}, {"Kids Name": Nancy, "Kids Gender": F, "Kids Age": 13}, {"Kids Name": Don, "Kids Gender": M, "Kids Age": 1}]}
]

感谢您的指导。 :)

开心

Answer 1

You actualy can use array variables in SQL, You can do this
DECLARE @TableVariableName TABLE(
IDs VARCHAR(100),
numbers Int
);
Insert into @TableVariableName
 -----Do the select you want it to store it into the new table for example:
Select IDs,numbers from tbl1

然后您可以在另一个查询中使用该表，如下所示:

Select tbl2.column1, @TableVariableName.IDs, @TableVariableName.numbers from tbl2
join @TableVariableName ON tbl2.IDs = @TableVariableName.IDs

给你。希望这有帮助