SQL case语句-3子句？
SQL case语句-3子句？
提问于 2018-01-25 17:35:39
希望有人能帮上忙
我有一个表格，看起来像下面这样
ID | Name | Age | GroupID | Services
33 | Pam | 34 | 334 | 3
33 | Pam | 34 | 334 | 4
43 | Lily | 56 | 10 | 3
67 | Bob | 22 | 11 | 4请注意，Pam有2行，因为她有2种不同类型的服务。
如何编写case语句来显示Name列中的person是否拥有服务3、服务4或两者都有。
例如，在查询上面的代码时，我的输出是
ID | Name | Age | GroupID | Services | SG
33 | Pam | 34 | 334 | 3 | 3 & 4
33 | Pam | 34 | 334 | 4 | 3 & 4
43 | Lily | 56 | 10 | 3 | 3 Only
67 | Bob | 22 | 11 | 4 | 4 Only一如既往的感谢大家
回答 5
Stack Overflow用户
发布于 2018-01-25 17:40:16
像这样的东西？
编辑:添加DISTINCT以避免出现3 & 4 & 4的情况
DECLARE @mockupTable TABLE(ID INT,Name VARCHAR(100),Age INT, GroupID INT,[Services] INT);
INSERT INTO @mockupTable VALUES
(33,'Pam',34,334,3)
,(33,'Pam',34,334,4)
,(43,'Lily',56,10,3)
,(67,'Bob',22,11,4);
SELECT t.*
,STUFF((
SELECT DISTINCT ' & ' + CAST(x.[Services] AS VARCHAR(10))
FROM @mockupTable AS x
WHERE t.ID=x.ID
ORDER BY x.[Services]
FOR XML PATH(''),TYPE
).value('.','nvarchar(max)'),1,3,'') AS SG
FROM @mockupTable AS t;结果
+----+------+-----+---------+----------+--------------------+
| ID | Name | Age | GroupID | Services | SG |
+----+------+-----+---------+----------+--------------------+
| 33 | Pam | 34 | 334 | 3 | 3 & 4 |
+----+------+-----+---------+----------+--------------------+
| 33 | Pam | 34 | 334 | 4 | 3 & 4 |
+----+------+-----+---------+----------+--------------------+
| 43 | Lily | 56 | 10 | 3 | 3 |
+----+------+-----+---------+----------+--------------------+
| 67 | Bob | 22 | 11 | 4 | 4 |
+----+------+-----+---------+----------+--------------------+如果要添加only，请执行以下查询
SELECT t.*
,STUFF((
SELECT DISTINCT ' & ' + CAST(x.[Services] AS VARCHAR(10))
FROM @mockupTable AS x
WHERE t.ID=x.ID
ORDER BY x.[Services]
FOR XML PATH(''),TYPE
).value('.','nvarchar(max)'),1,3,'')
+ CASE WHEN COUNT(*) OVER(PARTITION BY ID) = 1 THEN ' Only' ELSE '' END AS SG
FROM @mockupTable AS t;Stack Overflow用户
发布于 2018-01-25 17:42:35
如果您真的只有两个服务3和4，并且同一个人不会有两次相同的服务，那么我将使用
select *, case when (count(*) over (partition by name)) = 2 then '3 & 4'
else cast(services as varchar) + ' Only' end
from tdbfiddle demo
Stack Overflow用户
发布于 2018-01-25 17:42:47
尝尝这个
SELECT
*,
SG = CASE Q.Cnt
WHEN 1 THEN '3 & 4'
ELSE CAST(T1.[Services] AS VARCHAR(10))+' Only' END
FROM YourTable T1
OUTER APPLY
(
SELECT
Cnt = COUNT(DISTINCT Srv)
FROM YourTable T2
WHERE t2.Id = t1.Id
AND t2.[Services] IN (3,4)
AND t2.[Services] <> t1.[Services]
)Q页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/48439659
复制相关文章
相似问题
腾讯云开发者
