sql-server - MSSQL Server ',' 附近的语法不正确
问题描述
我正在使用 sql server 并试图查看如何将变量设置为如下位置列表:
Declare @StartDate datetime,
@EndDate datetime,
@EmpName varchar(50),
@LOCID varchar(25);
Set @StartDate = '2016/01/01';
Set @EndDate = '2019/02/01';
Set @EmpName = 'David Brierton';
Set @LOCID = ('desoto_tickets','ccc_tickets');
select
ID,
requester_name,
requester_id,
COUNT(*) as numberOfTickets
from Tickets
where created_at between @StartDate and @EndDate
and requester_name = @EmpName
and ',' + tags + ',' like '%,' + @LOCID + ',%'
group by Id, requester_name, requester_id
我收到一个逗号错误Set @LOCID = ('desoto_tickets','ccc_tickets');
错误状态:
',' 附近的语法不正确。
我在这里做错了什么?
任何帮助将不胜感激!
编辑:在标签列的数据库中,每个标签的列出如下:
desoto_counter,desoto_tickets,new_associate_workchecked,new_hire__paperwork_check__transaction_complete___express,newhire_assistance,no_error,ops,walk
每一行在数据库中都有一个这样的标签列表。
新编辑:
Declare @StartDate datetime,
@EndDate datetime,
-- @EmpName varchar(50),
@LOCID1 varchar(25),
@LOCID2 varchar(25),
@LOCID3 varchar(25),
@LOCID4 varchar(25),
@LOCID5 varchar(25),
@LOCID6 varchar(25);
Set @StartDate = '2016/01/01';
Set @EndDate = '2019/02/01';
-- Set @EmpName = 'David Brierton';
Set @LOCID1 = 'desoto_counter';
Set @LOCID2 = 'lwr_counter';
Set @LOCID3 = 'nr_tickets';
Set @LOCID4 = 'ps_tickets';
Set @LOCID5 = 'sdl_tickets';
Set @LOCID5 = 'ccc_tickets';
select
ID,
requester_name,
requester_id,
COUNT(*) as numberOfTickets,
case
when tags like '%,' + @LOCID1 + ',%' then 'CDS'
tags like '%,' + @LOCID2 + ',%' then 'CLR'
tags like '%,' + @LOCID3 + ',%' then 'CNR'
tags like '%,' + @LOCID4 + ',%' then 'CPS'
tags like '%,' + @LOCID5 + ',%' then 'SDL'
tags like '%,' + @LOCID6 + ',%' then 'CCC'
else 'Unknown'
end [Location],
from Tickets
where created_at between @StartDate and @EndDate
-- and requester_name = @EmpName
and ',' + tags + ',' like '%,' + @LOCID1 + ',%'
or ',' + tags + ',' like '%,' + @LOCID2 + ',%'
or ',' + tags + ',' like '%,' + @LOCID3 + ',%'
or ',' + tags + ',' like '%,' + @LOCID4 + ',%'
or ',' + tags + ',' like '%,' + @LOCID5 + ',%'
or ',' + tags + ',' like '%,' + @LOCID6 + ',%'
group by Id, requester_name, requester_id
解决方案
将扩展我的评论。首先,您使用的语法是:
SET @LOCID = ('desoto_tickets','ccc_tickets');
这不是有效值,因为标量变量只能包含 1 个值。如果要存储分隔字符串,则它需要是 1(文字)值:
SET @LOCID = 'desoto_tickets,ccc_tickets';
但是,WHERE
您拥有的条款并不理想,特别是:
AND ',' + tags + ',' LIKE '%,' + @LOCID + ',%'
我建议不要使用这样的语法,而是使用拆分器。如果您使用的是 SQL Server 2016+,则可以使用STRING_SPLIT:
SELECT ID,
requester_name,
requester_id,
COUNT(*) AS numberOfTickets
FROM Tickets T
CROSS APPLY STRING_SPLIT(@LOCID,',') SS
WHERE created_at BETWEEN @StartDate AND @EndDate
AND requester_name = @EmpName
AND SS.[value] = T.Tags --Assumes tags isn't delimited either, if it is, then your data is flawed
GROUP BY Id,
requester_name,
requester_id;
如果您使用的是 SQL Server 2008,请搜索delimitedsplit8k
,如果您使用的是 2012/2014 ,请搜索delimitedsplit8k_lead
。
或者,您可以使用表变量:
DECLARE @Loc table (tag varchar(50));
INSERT INTO @Loc
VALUES('desoto_tickets'),('ccc_tickets');
SELECT ID,
requester_name,
requester_id,
COUNT(*) AS numberOfTickets
FROM Tickets T
JOIN @Loc L ON T.tags = L.Tag --Assumes tags isn't delimited either, if it is, then your data is flawed
WHERE created_at BETWEEN @StartDate AND @EndDate
AND requester_name = @EmpName
GROUP BY Id,
requester_name,
requester_id;
编辑:好的,OP的数据有缺陷。我将更新此答案以尽快修复他们的数据。
编辑 2:下面“修复”了 OP 的数据。请注意接近尾声的清理和DROP
. 对于 OP,请确保在列之前修复所有DROP
tags
引用:
USE Sandbox;
GO
--Create a sample table
CREATE TABLE dbo.Tickets (ID int IDENTITY,
RequesterID int,
Tags varchar(MAX));
INSERT INTO dbo.Tickets (RequesterID,
Tags)
VALUES (1,'desoto_counter,desoto_tickets,new_associate_workchecked,new_hire__paperwork_check__transaction_complete___express,newhire_assistance,no_error,ops,walk'),
(2,'newhire_assistance,new_associate_workchecked,no_error');
GO
--Create 2 additional tables
CREATE TABLE dbo.Tags (ID int IDENTITY,
TagName varchar(100))
CREATE TABLE dbo.TicketTags (TicketTagID int IDENTITY,
TicketID int, --Should be a FOREIGN KEY
TagID int); --Should be a FOREIGN KEY
GO
--Get the unique tags. This might be slow if you have a lot of tickets
INSERT INTO dbo.Tags (TagName)
SELECT DISTINCT [value]
FROM tickets t
CROSS APPLY STRING_SPLIT(Tags,',') SS; --Assumes 2016+ see prior part of answer if not
GO
--Now populate the relational table
INSERT INTO dbo.TicketTags (TicketID,
TagID)
SELECT T.ID,
tags.ID
FROM dbo.tickets T
CROSS APPLY STRING_SPLIT(Tags,',') SS --Assumes 2016+ see prior part of answer if not
JOIN dbo.Tags ON SS.[value] = tags.TagName;
GO
--Then drop tbhe old column, which you might want to do LATER
ALTER TABLE dbo.Tickets DROP COLUMN tags;
GO
--And now your "new" query.
DECLARE @Loc table (tag varchar(50));
INSERT INTO @Loc
VALUES('desoto_tickets'),('ccc_tickets');
SELECT *
FROM tickets t
WHERE EXISTS (SELECT 1
FROM dbo.TicketTags TT
JOIN dbo.Tags ON TT.TagID = dbo.Tags.ID
JOIN @Loc L ON tags.TagName = L.tag
WHERE TT.TicketID = T.ID);
GO
--Clean up
DROP TABLE dbo.TicketTags;
DROP TABLE dbo.Tags;
DROP TABLE dbo.Tickets
推荐阅读
- c - 遍历 argv 指针到 char 指针
- html - 在 CSS 中重现复杂的渐变
- javascript - 在按钮 onclick 上调用 javascript 函数
- flutter - 如何在不关闭 AdGuard 的情况下让 Flutter 工作
- php - 通过上传 RDF 文件在 PHP 中可视化 RDF 图
- javascript - 如何以角度更改对象的值
- oracle-apex - 如何删除交互式网格中的过滤器?
- python - ssm-state manager-ansible 剧本
- sql - 如何告诉 BigQuery 在我发送查询时不要“检查”我的查询?我一直找不到表
- google-chrome-extension - chrome 扩展 onHeadersReceived 侦听器重定向位置错误