您的位置: 首页  >  文章  >  SQL Server中的字段合并

SQL Server中的字段合并

分类: 文章 • 2024-06-08 23:25:04

在查询语句编写过程中，常常会遇到将ID相同的几个字段合并到一个字段的情况，今天就给大家分享在SQL Server中的字段进行合并的操作。

我们以合并打卡记录的例子来说明怎么合并。


我们来看下面两个截图，图一是原始的打卡记录表，EmpID表示员工的ID，该表记录了每个员工每次打卡的时间。每打一次卡就生成一行记录。

直接展示或导出图一的记录，就会不直观，不好看，如果变成图二那样的格式展示和导出就好看了，每个员工每一天的打卡记录都一目了然。没有打卡的日期，打卡记录就用null表示。


SQL Server中的字段合并        SQL Server中的字段合并            SQL Server中的字段合并                           

                               图一（原始的打开记录）    


           SQL Server中的字段合并SQL Server中的字段合并

                                    图二（展示的打卡记录）


下面就举例说明怎样将图一的原始表数据加工成图二的结果展示。


1，首先创建测试用的表，并插入数据。代码如下：

---员工信息表
CREATE TABLE Employee (
id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
EmpID INT NOT NULL,
EmpName NVARCHAR(50)     
)

INSERT INTO dbo.Employee (EmpID ,EmpName)
SELECT 10001,'张三' UNION ALL
SELECT 10002,'李四'


---打卡记录表
CREATE TABLE CARD_RECORD (
    id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    EmpID INT NOT NULL,
    FDateTime DATETIME NOT NULL
)
GO

INSERT INTO CARD_RECORD (EmpID,FDateTime)
SELECT '10001','2017-09-01 08:21'
UNION ALL
SELECT '10002','2017-09-01 08:22'
UNION ALL
SELECT '10001','2017-09-01 12:00'
UNION ALL
SELECT '10002','2017-09-01 12:01'
UNION ALL
SELECT '10001','2017-09-01 13:00'
UNION ALL
SELECT '10002','2017-09-01 13:01'
UNION ALL
SELECT '10002','2017-09-01 18:05'
UNION ALL
SELECT '10001','2017-09-01 18:12'
UNION ALL
SELECT '10002','2017-09-02 08:31'
UNION ALL
SELECT '10001','2017-09-02 08:42'
UNION ALL
SELECT '10001','2017-09-02 12:10'
UNION ALL
SELECT '10002','2017-09-02 12:11'
UNION ALL
SELECT '10001','2017-09-02 13:00'
UNION ALL
SELECT '10002','2017-09-02 13:11'
UNION ALL
SELECT '10001','2017-09-02 18:05'
UNION ALL
SELECT '10002','2017-09-02 18:12'
UNION ALL
SELECT '10002','2017-09-02 19:34'
UNION ALL
SELECT '10001','2017-09-03 08:36'
UNION ALL
SELECT '10002','2017-09-03 08:40'
UNION ALL
SELECT '10001','2017-09-03 12:20'
UNION ALL
SELECT '10002','2017-09-03 12:20'
UNION ALL
SELECT '10001','2017-09-03 12:55'
UNION ALL
SELECT '10002','2017-09-03 12:56'
UNION ALL
SELECT '10001','2017-09-03 18:05'
UNION ALL
SELECT '10001','2017-09-05 08:05'
UNION ALL
SELECT '10001','2017-09-05 12:30'
UNION ALL
SELECT '10001','2017-09-05 13:02'
UNION ALL
SELECT '10001','2017-09-05 18:10'
GO


2，合并每个员工的打卡时间到一个字段内

合并打卡时间，我们使用 STUFF() 函数结合 For xml path 参数来对员工的打卡时间进行合并。语句如下：

----加工打卡记录表，将打卡时间合并到一个字段里面

SELECT a.EmpID,CAST(a.FDateTime AS DATE) dates,

times= STUFF( ( SELECT ','+ CONVERT(VARCHAR(5),FDateTime,108) FROM dbo.CARD_RECORD 

WHERE CAST(FDateTime AS DATE)= CAST(a.FDateTime AS DATE) AND EmpID=a.empid FOR XML PATH('') ) ,1,1,'')

FROM dbo.CARD_RECORD a 

--- WHERE  真实使用时，这里需要加where条件，查询一段时间内的打卡记录，否则查询出来的就是全部的打卡记录

GROUP BY a.EmpID,CAST(a.FDateTime AS DATE)


得到的结果如下图所示：

SQL Server中的字段合并

说明：原始的打卡记录，是datetime型的，包含“年月日”和“时间”两部分，但在展示的时候“年月日”和“时间”是分开的，只需要将“时间”合并，“年月日”不需要合并，所以合并的是datetime型的“时间”部分。通过转换函数：CONVERT(VARCHAR(5),FDateTime,108)  即得到了FDateTime的“时间”的那部分。而CAST(a.FDateTime AS DATE) 是将FDateTime转换为Date型，Date型只有“年月日”，没有后面的“时间”。


简单来说明一下 STUFF()  函数结合 For xml path 参数是怎么实现字段合并的：

假如有这样一张表，表名是 Table_A

SQL Server中的字段合并
 我想把它变成下面的格式：

SQL Server中的字段合并

实现代码如下：

select 	类别, 
	名称 = ( stuff(
			(select ',' + 名称 from Table_A where 类别 = A.类别 for xml path('')),
			1,
			1,
			''
			)
	        ) 
from Table_A as A group by 类别



for xml path('') 
这句是把得到的内容以XML的形式显示。
stuff((select ',' + ep_name from ep_detail where ep_classes = a.ep_classes for xml path('')), 1, 1, '')
这句是把拼接的内容的第一个“，”去掉

3，构建日期

在完成第二步的时候，我们发现，员工打卡的日期是不完整的，没有打卡记录的那一天是没有记录的，我们需要将没有记录的那一天也展示出来，就需要构建当天的日期。

构建日期我们使用 Master 数据库中的系统表 spt_values 来生成连续的日期。代码如下：

declare @StartDate DATE = '2017-09-01'
declare @EndDate DATE ='2017-09-05'

--利用master库spt_values表构建时间，关联员工信息表，将每个员工附上构建的时间
SELECT b.EmpID,b.EmpName,dateadd(day,a.number,@StartDate) as dates
from master.dbo.spt_values a CROSS APPLY dbo.Employee b  
WHERE a.type ='P' and a.number <=DATEDIFF(day, @StartDate, @EndDate)


查询结果如下所示：


SQL Server中的字段合并


这样每个员工就给他构建了查询范围内连续的日期。将第二步和第三步查询的结果整合，就可以得到最终的结果。


整合后完整的代码如下：

/***查询数据***/
declare @StartDate DATE = '2017-09-01'       ----查询的开始日期
declare @EndDate DATE ='2017-09-05'         ----查询的结束日期


SELECT aa.EmpID,aa.EmpName,aa.dates,bb.times
FROM (
--利用master库spt_values表构建时间，关联员工信息表，将每个员工附上构建的时间
SELECT b.EmpID,b.EmpName,dateadd(day,a.number,@StartDate) as dates
from master.dbo.spt_values a CROSS APPLY dbo.Employee b  
WHERE a.type ='P' and a.number <=DATEDIFF(day, @StartDate, @EndDate)
) aa LEFT JOIN (
--加工打卡记录表，将打卡时间合并到一个字段里面
SELECT a.EmpID,CAST(a.FDateTime AS DATE) dates,
times= STUFF( ( SELECT ','+ CONVERT(VARCHAR(5),FDateTime,108) FROM dbo.CARD_RECORD WHERE CAST(FDateTime AS DATE)= CAST(a.FDateTime AS DATE) AND EmpID=a.empid FOR XML PATH('') ) ,1,1,'')
FROM dbo.CARD_RECORD a 
WHERE a.FDateTime >= @StartDate AND a.FDateTime <= @EndDate
GROUP BY a.EmpID,CAST(a.FDateTime AS DATE)
) bb ON aa.EmpID=bb.EmpID AND aa.dates=bb.dates


这样，我们就能得到 9月1日至 9月5日的员工打卡记录，当天如果没有打卡记录，则显示为null，而且日期也是连续的显示出来。


希望以上方法能够帮助到大家。










相关推荐