R Shiny更新数据表适配多选功能问题求解

你遇到的问题根源是原代码仅适配单行选中的逻辑：当设置selection = 'multiple'后，input$MyTable_rows_selected会返回包含所有选中行索引的整数向量，而原代码中直接用=匹配单个值的SQL写法只会读取向量的第一个元素，因此仅返回第一条选中用户的数据。

具体修改方案 #

只需要调整table2、table8的SQL查询逻辑，将单值匹配改为多值的IN匹配即可，修改后的核心代码如下：

修改table2的查询逻辑 #

table2 <- reactive({
  rows <- input$MyTable_rows_selected
  # 无选中行时返回空数据框避免报错
  if(length(rows) == 0) return(data.frame())
  
  # 拼接多个PartyID用于IN查询
  party_ids <- paste(table1()[rows,"PartyID"], collapse = ",")
  # 拼接多个地址，字符串要加单引号，同时转义内容自带的单引号
  addresses <- paste0("'", gsub("'", "''", table1()[rows,"Address"]), "'", collapse = ",")
  
  sqlQuery(BDW,paste0("SELECT DISTINCT FullName as 'Name',DATEDIFF(hour,DateofBirthorIncorporation,GETDATE())/8766 as 'Age',CONVERT(varchar(10),DateofBirthorIncorporation, 120) as 'Date of Birth',TaxID as 'SSN',GenderCode as 'Gender', OccupationCodeDescription as 'Occupation',DigitalAddress as 'Email', AddressLine1+' '+City+' '+StateCode+' '+PostalCode as 'Address'
  FROM [EnterpriseCustomer].[dbo].[Party]a
  LEFT JOIN [EnterpriseCustomer].[dbo].[PartytoAddressRelationship]b ON b.PartyID = a.PartyID
  LEFT JOIN [EnterpriseCustomer].[dbo].[Address]c ON c.AddressID = b.AddressID
  LEFT JOIN [EnterpriseCustomer].[dbo].[PartytoDigitalAddressRelationship]d ON d.PartyID = a.PartyID
  LEFT JOIN [EnterpriseCustomer].[dbo].[DigitalAddress]e ON e.DigitalAddressID = d.DigitalAddressID
  LEFT JOIN [BDW].[EnterpriseCustomer].[PartyOccupationCodes]g ON a.OccupationCode = g.OccupationCode
  WHERE
  a.PartyID IN (", party_ids, ")
  and (AddressLine1+' '+City+' '+StateCode+' '+PostalCode) IN (", addresses, ")
  and a.ActiveRecordIndicator = 1 and b.ActiveRecordIndicator = 1"), as.is = TRUE)
})

修改table8的查询逻辑 #

table8 <- reactive({
  rows <- input$MyTable_rows_selected
  if(length(rows) == 0) return(data.frame())
  
  # 拼接多个SSN，字符串加单引号，转义内容里的单引号避免SQL语法错误
  ssns <- paste0("'", gsub("'", "''", table1()[rows,"Social Security Number"]), "'", collapse = ",")
  
  sqlQuery(BOSCDB,paste0("SELECT DISTINCT TaxID, ltrim(rtrim(MinimumAnnualIncomeAmount))+' - '+ltrim(rtrim(MaximumAnnualIncomeAmount)) as 'Annual Income Band',ltrim(rtrim(MinimumNetWorthAmount))+' - '+ltrim(rtrim(MaximumNetWorthAmount)) as 'Net Worth Band',ltrim(rtrim(MinLiquidNetWorthAmt))+' - '+ltrim(rtrim(MaxLiquidNetWorthAmt)) as 'Liquid Net Worth Band'
  FROM [ExternalData_Stage].[dbo].[tblPrsCustomerAccount_G]a
  LEFT JOIN [ExternalData_Stage].[dbo].[tblPrsCustomerAccount_H]b ON b.AccountNumber = a.AccountNumber
  WHERE TaxID IN (", ssns, ")
  and a.DataDate = (SELECT MAX(DataDate) FROM [ExternalData_Stage].[dbo].[tblPrsCustomerAccount_G] WHERE TaxID IN (", ssns, "))"),as.is = TRUE)
})

额外说明 #

• 原有的table9合并逻辑不需要修改，两个返回多用户数据的表合并后自然就是所有选中用户的完整信息
• 直接拼接用户输入内容到SQL语句存在SQL注入风险，若有更高安全要求，建议改用RODBCext包的参数化查询功能

内容的提问来源于stack exchange，提问作者Lcsballer1