sql - 在 SQL Server Compact 中加入逗号分隔值不起作用

Question

我有 2 表如下

CREATE TABLE [Chart]
(
   [Id] BIGINT NOT NULL IDENTITY (1,1),
   [Owner] NVARCHAR(100),
   [Identifier] NVARCHAR(128) NOT NULL,
   [Title] NVARCHAR(100),
   [Type] INT NOT NULL,
   [Category] INT NOT NULL,
   [CreatedDate] DATETIME NOT NULL,
   [ModifiedDate] DATETIME,
   [ExpireDate] DATETIME,
   [VisitCount] BIGINT NOT NULL,
   [ConfigurationId] BIGINT NOT NULL,
   [Theme] NVARCHAR(50)
);

ALTER TABLE [Chart] ADD CONSTRAINT [PK_dbo.Chart] PRIMARY KEY ([Id]);


CREATE TABLE [SelectedTag]
(
   [Id] BIGINT NOT NULL IDENTITY (1,1),
   [Identifier] NVARCHAR(4000),
   [TagName] NVARCHAR(4000),
   [Description] NVARCHAR(4000),
   [EuUnit] NVARCHAR(50),
   [EuRangeLow] NVARCHAR(50),
   [EuRangeHigh] NVARCHAR(50),
   [MinStats] FLOAT,
   [MaxStats] FLOAT,
   [Min] FLOAT,
   [Max] FLOAT,
   [GroupCode] NVARCHAR(4000)
);

ALTER TABLE [SelectedTag] ADD CONSTRAINT [PK_dbo.SelectedTag] PRIMARY KEY ([Id]);

我为查询标记计数和标记列表尝试了这个答案，它在 SQL Server 中运行良好

https://dba.stackexchange.com/q/112408/172901

select c.Identifier, count(c.Identifier) as TagCount, (
       select concat(',', s.TagName)  from selectedtag  as s
       where concat(',', c.identifier, ',') like '%,'+s.identifier+',%'
       for xml path(''), type
       ).value('substring(text()[1], 2)', 'varchar(max)') as TagList
 from chart c
 group by c.Identifier

但是对于 SQL Server 紧凑型数据库，它不起作用

我使用 CompactView 通过 SQL 紧凑型数据库运行查询

https://sourceforge.net/projects/compactview/

我的查询或 SQL Compact 问题有什么问题？谢谢

标签： sqlsql-serversql-server-cesql-server-2017

Answer 1

SQL Server Compact 不支持 XML 数据类型和函数，嵌套的 sSELECT 也不起作用。编写一个普通的选择，并在代码中执行额外的处理。