时间戳

首页 > 解决方案 > 使用 CTE 获取数据但使用不起作用的东西

sql - 使用 CTE 获取数据但使用不起作用的东西

问题描述

我有一个包含多个连接的非常大的查询，我有一些记录，但它们是重复的，有 3 列具有不同的数据

所以我试图将所有 3 个合并到一行中，值以逗号分隔

我使用 CTE 将表格设置为：

;with cte(id,name,email,roles,country) as( select from mytable 4 joins and where clauses with data passed to it));

现在我正在这样做

select id,name,stuff((select email + ',' from cte FOR XML PATH ('')), 1, 1, '')) from cte group by id,name

但它正在填充数据不属于的所有其他行......

我有这样的结果

ID     name      email              roles     campus
1      User1     user@gmail.com       Admin      Egypt
2      User1     user@gmail.com       Moderator  Egypt 
3      User1     user@gmail.com       Guest      USA
4      User2     user2@gmail.com     User        Brazil
5      User2     user2@gmail.com     Admin      Cairo
6      User2     user2@gmail.com     Dummy      Namibia

并尝试像这样构建

ID     name      email              roles                      campus
1      User1     user@gmail.com     Admin,Moderator,Egypt      Egypt,Egypt,USA
2      User2     user1@gmail.com    User,Admin,Dummy           Brazil,Cairo,Namibia

标签： sqlsql-server

解决方案

stuff()功能不是这里的问题。您应用for xml需求修复的方式。这个网站上已经有很多像这样的好例子了。

样本数据

create table cte
(
  ID int,
  name nvarchar(10),
  email nvarchar(20),
  role nvarchar(10),
  campus nvarchar(10)
)

insert into cte (ID, name, email, role, campus) values
(1, 'User1', 'user@gmail.com',  'Admin',     'Egypt'  ),
(2, 'User1', 'user@gmail.com',  'Moderator', 'Egypt'  ),
(3, 'User1', 'user@gmail.com',  'Guest',     'USA'    ),
(4, 'User2', 'user2@gmail.com', 'User',      'Brazil' ),
(5, 'User2', 'user2@gmail.com', 'Admin',     'Cairo'  ),
(6, 'User2', 'user2@gmail.com', 'Dummy',     'Namibia');

解决方案

  • 我完全忽略了这里的列，这些对我来说看起来像行号，而不是实际的 ID（意味着 、 和之间没有ID关系）。123User1
  • 我也没有理会这个email专栏，因为那些似乎与名字有关。

这给出了：

select row_number() over(order by c1.name) as ID,
       c1.name,
       c1.email,
       stuff((  SELECT ',' + Role
                FROM cte c3
                WHERE c3.name = c1.name
                FOR XML PATH(''),TYPE)
                .value('.','NVARCHAR(MAX)'),1,1,'') AS Roles,
       stuff((  SELECT ',' + Campus
                FROM cte c3
                WHERE c3.name = c1.name
                FOR XML PATH(''),TYPE)
                .value('.','NVARCHAR(MAX)'),1,1,'') AS Campuses
from cte c1
group by c1.name, c1.email
order by c1.name;

结果

ID  name   Emails           Roles                  Campuses
--- ------ ---------------- ---------------------- ---------------------
1   User1  user@gmail.com   Admin,Moderator,Guest  Egypt,Egypt,USA
2   User2  user2@gmail.com  User,Admin,Dummy       Brazil,Cairo,Namibia

小提琴

推荐阅读