时间戳

首页 > 解决方案 > 如何从sql查询返回连接值

r - 如何从sql查询返回连接值

问题描述

我正在查询 SQL Server 数据库以返回连接值。是否可以？

cnn <- odbcConnect("CN")
query = gsub("\n", " ", " DECLARE @Price VARCHAR(50) = ''
SELECT @Price =+ @Price + [Price] + ' + '
FROM [dbo].[wine]
SELECT @Price")
query

dataset1 <- sqlQuery(cnn, query)
dataset1

当我在 SSMS 中运行查询时，它可以工作。这是dataset1运行时的错误：

[1]“42S02 208 [Microsoft][SQL Server Native Client 11.0][SQL Server]无效的对象名称'dbo.wine'。”
[2] "[RODBC] 错误：不能 SQLExecDirect ' DECLARE @Price VARCHAR(50) = '' SELECT @Price =+ @Price + [Price] + ' + ' FROM [dbo].[wine] SELECT @Price' "

代码：

CREATE TABLE [dbo].[wine]
(
    [Year] [varchar](50) NULL,
    [Price] [varchar](50) NULL,
    [WinterRain] [varchar](50) NULL,
    [AGST] [varchar](50) NULL,
    [HarvestRain] [varchar](50) NULL,
    [Age] [varchar](50) NULL,
    [FrancePop] [varchar](50) NULL
) ON [PRIMARY]
GO

INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1952', N'7.495', N'600', N'17.1167', N'160', N'31', N'43183.569')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1953', N'8.0393', N'690', N'16.7333', N'80', N'30', N'43495.03')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1955', N'7.6858', N'502', N'17.15', N'130', N'28', N'44217.857')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1957', N'6.9845', N'420', N'16.1333', N'110', N'26', N'45152.252')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1958', N'6.7772', N'582', N'16.4167', N'187', N'25', N'45653.805')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1959', N'8.0757', N'485', N'17.4833', N'187', N'24', N'46128.638')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1960', N'6.5188', N'763', N'16.4167', N'290', N'23', N'46583.995')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1961', N'8.4937', N'830', N'17.3333', N'38', N'22', N'47128.005')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1962', N'7.388', N'697', N'16.3', N'52', N'21', N'48088.673')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1963', N'6.7127', N'608', N'15.7167', N'155', N'20', N'48798.99')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1964', N'7.3094', N'402', N'17.2667', N'96', N'19', N'49356.943')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1965', N'6.2518', N'602', N'15.3667', N'267', N'18', N'49801.821')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1966', N'7.7443', N'819', N'16.5333', N'86', N'17', N'50254.966')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1967', N'6.8398', N'714', N'16.2333', N'118', N'16', N'50650.406')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1968', N'6.2435', N'610', N'16.2', N'292', N'15', N'51034.413')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1969', N'6.3459', N'575', N'16.55', N'244', N'14', N'51470.276')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1970', N'7.5883', N'622', N'16.6667', N'89', N'13', N'51918.389')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1971', N'7.1934', N'551', N'16.7667', N'112', N'12', N'52431.647')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1972', N'6.2049', N'536', N'14.9833', N'158', N'11', N'52894.183')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1973', N'6.6367', N'376', N'17.0667', N'123', N'10', N'53332.805')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1974', N'6.2941', N'574', N'16.3', N'184', N'9', N'53689.61')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1975', N'7.292', N'572', N'16.95', N'171', N'8', N'53955.042')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1976', N'7.1211', N'418', N'17.65', N'247', N'7', N'54159.049')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1977', N'6.2587', N'821', N'15.5833', N'87', N'6', N'54378.362')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1978', N'7.186', N'763', N'15.8167', N'51', N'5', N'54602.193')
GO

标签： rsql-server

解决方案

为什么不直接使用字符串聚合而不是这种相当不安全的=+语法呢？

declare @price nvarchar(max);
select @price = string_agg(price, ' + ') from [dbo].[wine];
select @price;

请注意，您可能需要超过 50 个字符来存储这 25 个价格；我用过nvarchar(max)。

DB Fiddle 上的演示

推荐阅读