时间戳

首页 > 解决方案 > 不能 GROUP BY 或 DISTINCT 我对 xml 路径的查询

sql - 不能 GROUP BY 或 DISTINCT 我对 xml 路径的查询

问题描述

我从表格中提取章节标题、章节标题和问题来制作目录。我正在使用 Caspio 的低代码平台，它在 SQL-Server 上运行。

我已经能够 GROUP BY 或 ORDER BY，但我似乎不能同时做这两个。就我而言，ORDER BY 更为重要。GROUP BY 只会让它更容易处理。当我添加 GROUP BY 语句时，它什么也不显示。

我希望能够使用 GROUP BY 或使用 DISTINCT，你知道为什么不能吗？

这是我的查询：

SELECT [@field:WF_tbl_Chapter_Title] as Chapter, [@field:WF_tbl_Section_Title] as Section, [@field:WF_tbl_Question_Description] as Question
FROM _v_Questions_View as QV
WHERE [@field:WF_tbl_Workbook_Author_ID] = '[@authfield:WF_tbl_Customer_Customer_ID]' 
AND [@field:WF_tbl_Workbook_Workbook_ID] = '[@WID] '
ORDER BY QV.WF_tbl_Chapter_Position, QV.WF_tbl_Section_Position, QV.WF_tbl_Question_Position

for xml path(N'')

这是一个无法显示任何内容的查询示例：

SELECT [@field:WF_tbl_Chapter_Title] as Chapter, [@field:WF_tbl_Section_Title] as Section, [@field:WF_tbl_Question_Description] as Question
FROM _v_Questions_View as QV
WHERE [@field:WF_tbl_Workbook_Author_ID] = '[@authfield:WF_tbl_Customer_Customer_ID]' 
AND [@field:WF_tbl_Workbook_Workbook_ID] = '[@WID]'
GROUP BY [@field:WF_tbl_Chapter_Title], [@field:WF_tbl_Section_Title], [@field:WF_tbl_Question_Description]
ORDER BY QV.WF_tbl_Chapter_Position, QV.WF_tbl_Section_Position, QV.WF_tbl_Question_Position
for xml path(N'')

标签： sqlsql-servergroup-byfor-xml-pathcaspio

解决方案

在堆栈溢出中搜索有关在 sql server 中与“for xml”不同的问题。Caspio 使用 sql server。

答案通常建议在子查询或 CTE 中隔离初始选择。

所以猜测.... 将您的工作查询包装为以下之一：

SELECT DISTINCT Chapter, Section, Question FROM
(SELECT [@field:WF_tbl_Chapter_Title] as Chapter, [@field:WF_tbl_Section_Title] as Section, [@field:WF_tbl_Question_Description] as Question
 FROM _v_Questions_View as QV
 WHERE [@field:WF_tbl_Workbook_Author_ID] = '[@authfield:WF_tbl_Customer_Customer_ID]' 
 AND [@field:WF_tbl_Workbook_Workbook_ID] = '[@WID] '
 ) AS SUBQ
 ORDER BY Chapter, Section, Question
for xml path(N'')

或者

WITH X AS
    (SELECT [@field:WF_tbl_Chapter_Title] as Chapter, [@field:WF_tbl_Section_Title] as Section, [@field:WF_tbl_Question_Description] as Question
     FROM _v_Questions_View as QV
     WHERE [@field:WF_tbl_Workbook_Author_ID] = '[@authfield:WF_tbl_Customer_Customer_ID]' 
     AND [@field:WF_tbl_Workbook_Workbook_ID] = '[@WID] '
     GROUP BY [@field:WF_tbl_Chapter_Title], [@field:WF_tbl_Section_Title], [@field:WF_tbl_Question_Description]
     )
SELECT Chapter, Section, Question
FROM X
ORDER BY  Chapter, Section, Question
for xml path(N'')

推荐阅读