for循环只检查向量中的最后一个值，以便用R检查Server中的重复值？

Question

library(odbc)
library(DBI)
library(pool)
myDatabase <- 'MyDB'
myDriver <- 'SQL Server'
myServer <- 'localhost\\SQLEXPRESS'

con <- pool::dbPool(odbc::odbc(), Driver = myDriver,
                    Server = myServer,
                    Database = myDatabase,
                    Trusted_Connection = 'True')

Caddy_id <- c("C1","C2","C3","C4","C5","C6","C7","C8","C9","C10")

for(i in 1:length(Caddy_id)){
  
check_duplicate <- dbGetQuery(con,paste0("DECLARE @container AS INT = 0
                                            IF EXISTS(SELECT 1 FROM dbo.P1000_Packing WITH(NOLOCK)
                                              WHERE Caddy_1 = '",Caddy_id[i],"' OR
                                                    Caddy_2 = '",Caddy_id[i],"' OR
                                                    Caddy_3 = '",Caddy_id[i],"' OR
                                                    Caddy_4 = '",Caddy_id[i],"' OR
                                                    Caddy_5 = '",Caddy_id[i],"' OR
                                                    Caddy_6 = '",Caddy_id[i],"' OR
                                                    Caddy_7 = '",Caddy_id[i],"' OR
                                                    Caddy_8 = '",Caddy_id[i],"' OR
                                                    Caddy_9 = '",Caddy_id[i],"' OR
                                                    Caddy_10 = '",Caddy_id[i],"')
                                              BEGIN
                                                   SET @container = 1
                                              END
                                            SELECT @container;"))

}
check_duplicate

我想用R检查Server中重复项的所有值，但只能检查向量Caddy_id中的最后一个值。

为什么只有值10返回1而另一个值即使存在于sql表中也会返回？

此表返回1

​

。

但是这个表返回0。

​

​

如果我更改向量Caddy_id <- c("C0","C1","C2","C3","C4","C5","C6","C7","C8","C9")，它也返回1

Answer 1

目前，在每次将@container重置为零的迭代中，查询都会自行运行。因此，只保留最后一次运行计数，每次替换check_duplicate。

但是，考虑通过为每个caddy id构建一个聚合计数向量列表来计算R中的重复数。确保还使用包含的参数安全地运行查询。

Caddy_id <- c("C1","C2","C3","C4","C5","C6","C7","C8","C9","C10")

sql = "SELECT COUNT(*) AS [count]
       FROM dbo.P1000_Packing
       WHERE Caddy_1  = ?cad_id OR
             Caddy_2  = ?cad_id OR
             Caddy_3  = ?cad_id OR
             Caddy_4  = ?cad_id OR
             Caddy_5  = ?cad_id OR
             Caddy_6  = ?cad_id OR
             Caddy_7  = ?cad_id OR
             Caddy_8  = ?cad_id OR
             Caddy_9  = ?cad_id OR
             Caddy_10 = ?cad_id"

check_duplicates <- sapply(
    Caddy_id, 
    function(i) {
        qry <- sqlInterpolate(con, sql, ?cad_id = i)
        dbGetQuery(con, qry)$count
    }
)

total_duplicates <- sum(check_duplicates, na.rm = TRUE)

另一种SQL查询：

SELECT COUNT(*) AS [count]
FROM dbo.P1000_Packing
WHERE ?cad_id IN (
     Caddy_1, Caddy_2, Caddy_3, Caddy_4, Caddy_5,
     Caddy_6, Caddy_7, Caddy_8, Caddy_9, Caddy_10
)