在 R 值的 SQL 块中显示值

发布于08月24日

根据the docs，我可以在RMarkdown的SQL块中使用(简单的)R表达式，如下所示:

```{r}
subjects = 10
```

```{sql, connection=db}
SELECT * FROM trials WHERE subjects >= ?subjects
```

然而，在呈现的文档中，我希望看到实际值subjects，但它从字面上显示为键入的值，即

SELECT * FROM trials WHERE subjects >= ?subjects

您知道如何将SQL区块中的?subjects替换为实际值吗，即我希望看到

SELECT * FROM trials WHERE subjects >= 10

在我的渲染输出中.

推荐答案

从knitr version 1.16开始，您可以使用选项sql.show_interpolated = TRUE来 Select 加入此行为.它已经被添加到这个PR中.

请从以下现有问题中查看此示例:

https://github.com/yihui/knitr/issues/2131个

---
title: "Get SQL with evaluated variables from SQL chunks"
output: html_document
---
  
In order to provide a reproducible example, we use dplyr and dbplyr with `tbl_memdb` to load a table into a memory db.

```{r}
library(dplyr)
library(dbplyr)
iris_db <- tbl_memdb(iris)
# we isolate the connection object 
con <- src_memdb()$con
species <- "setosa"
```

We check that it works with SQL chunk to filter data into an R object

```{sql, connection=con, output.var="iris_subset", sql.show_interpolated = TRUE}
SELECT * FROM iris WHERE Species = ?species
```

```{r}
head(iris_subset)
```

You'll see the following where ?species has been replaced