sql - 编写SQL查询,使用过滤器将一列分成两列

sql - 编写SQL查询,使用过滤器将一列分成两列,第1张

我不是数据库管理员,现在我遇到查询单表的问题。

我得到了如下表格

更新

sql - 编写SQL查询,使用过滤器将一列分成两列,enter image description here,第2张

我想进行查询,如下图所示

更新

sql - 编写SQL查询,使用过滤器将一列分成两列,enter image description here,第3张

我该怎么办?非常感谢查询谢谢。

最佳答案:

2 个答案:

答案 0 :(得分:1)

你可以这样做:

SELECT MAX(T1.CloseDate) as [Date],
       T1.Job,
       MAX(CASE WHEN T1.WorkRole = 'Case' THEN COALESCE(T2.WorkType,T1.WorkType) END) AS [Case],
       MAX(CASE WHEN T1.WorkRole = 'Engineer' THEN COALESCE(T1.WorkType,T2.WorkType) END) AS [WT]
FROM TableName T1 LEFT JOIN
     TableName T2 ON T1.CloseDate<T2.CloseDate AND T1.Job=T2.Job 
GROUP BY T1.Job
ORDER BY [Date] DESC

结果:

Date                        Job     Case    WT
------------------------------------------------------
January, 01 2008 00:00:00   Senior  Normal  Electronic
January, 01 2005 00:00:00   Junior  Average Electronic

SQL Fiddle中的示例结果。

答案 1 :(得分:0)

这称为pivoting。一种方法:

select  CloseDate as [Date]
,       Job
,       max(case WorkRole when 'Case' then WorkType end) as [Case]
,       max(case WorkRole when 'Engineer' then WorkType end) as WT
from    YourTable
group by
        CloseDate
,       Job
本文经用户投稿或网站收集转载,如有侵权请联系本站。

发表评论

0条回复