/*
考勤日期,签到记载类别 违规人姓名
2008-1-1 迟到 张三
2008-1-1 迟到 李四
2008-1-1 早退 王五
2008-1-2 早退 张三
2008-1-2 早退 李四
2008-1-2 早退 王五 我需要一个SQL语句,按照考勤日期与签到记载类别 来分组,并将违规人姓名内容融合到一个字段内
表A的查询返回结果应该这样: 考勤日期,签到记载类别 违规人姓名
2008-1-1 迟到 张三,李四
2008-1-1 早退 王五
2008-1-2 早退 张三,李四,王五
*/
=========================================
---SQL 2000create table tb(date varchar(10),tb_type varchar(20),tb_id varchar(20))
insert into tb values('2008-1-1','迟到','张三')
insert into tb values('2008-1-1','迟到','李四')
insert into tb values('2008-1-1','早退','王五')
insert into tb values('2008-1-2','早退','张三')
insert into tb values('2008-1-2','早退','李四')
insert into tb values('2008-1-2','早退','王五')
CREATE FUNCTION dbo.f_str(@id varchar(20),@tb_type varchar(20))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r = ''
SELECT @r = @r + ',' + tb_id FROM tb WHERE date=@id AND tb_type=@tb_type
RETURN STUFF(@r, 1, 1, '')
END
GO SELECt date, value = dbo.f_str(date,tb_type) FROM tb GROUP BY date ,tb_type drop table tb
drop function dbo.f_str
/*
=======================
date value
---------- -----------------
2008-1-1 张三,李四
2008-1-1 王五
2008-1-2 张三,李四,王五 (3 行受影响)
*/
============================
---SQL 2005
create table kq(考勤日期 datetime,签到记载类别 nvarchar(5), 违规人姓名 nvarchar(5))
insert into kq select '2008-1-1', '迟到', '张三'
insert into kq select '2008-1-1', '迟到', '李四'
insert into kq select '2008-1-1', '早退', '王五'
insert into kq select '2008-1-2', '早退', '张三'
insert into kq select '2008-1-2', '早退', '李四'
insert into kq select '2008-1-2', '早退', '王五'
go
select distinct T.* from (
select 考勤日期,签到记载类别,stuff((select ','+违规人姓名 from kq where 考勤日期=a.考勤日期 and 签到记载类别=a.签到记载类别 for xml path('')),1,1,'') as 违规人姓名 from kq a
)Tgo
drop table kq
/*
考勤日期 签到记载类别 违规人姓名
----------------------- ------ ---------------------------
2008-01-01 00:00:00.000 迟到 张三,李四
2008-01-01 00:00:00.000 早退 王五
2008-01-02 00:00:00.000 早退 张三,李四,王五(3 行受影响)*/
解决方案 »
- 求教:任何做到原先数据完好,向表中插入一列
- 视图能够像普通表一样进行数据库的插入,更新,删除操作吗?还是只能进行查询操作?
- 存储过程如何捕获异常?求教
- 求一个简单的触发器!insert delete
- 求一sql语句,十万火急********************************
- ------分享:SQL Server高并发性的设计和优化--------
- 为什么SQL2005标准版和开发版在安装到XP的过程中都报兼容性错误
- 请教语法问题
- 去掉一个字段中的标点符号的SQL语句怎么写
- 不能向SQL SERVER中添加记录,请帮助
- 在触发器里回滚触发器里的SQL,且不影响原有的操作
- 如果把表里面的两列数据 合并成一列显示
-->> Author: 让你望见影子的墙(HEROWANG)生成测试数据 Date:2009-04-24 11:33:22
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE ta
Go
CREATE TABLE ta(ID INT,E1 NVARCHAR(1),E2 NVARCHAR(1),E3 NVARCHAR(1))
Go
INSERT INTO ta
SELECT 1,'a','b','c' UNION ALL
SELECT 2,'d','e','f'
GOSELECT * FROM TA-->> Author: 让你望见影子的墙(HEROWANG)生成测试数据 Date:2009-04-24 11:28:46
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
Go
CREATE TABLE tb(ID INT,E NVARCHAR(1))
Go
INSERT INTO tb
SELECT 1,'M' UNION ALL
SELECT 1,'N' UNION ALL
SELECT 1,'O' UNION ALL
SELECT 2,'P' UNION ALL
SELECT 2,'Q'
GOSELECT * FROM TB
with
wang as (select row=row_number() over (partition by id order by id),* from tb),
wang1 as(
select id,e1=max(case when row=1 then E end),e2=max(case when row=2 then E end),e3=max(case when row=3 then E end)
from wang
group by id)update ta
set e1=wang1.e1,e2=wang1.e2,e3=wang1.e3
from wang1
where ta.id=wang1.idselect * from ta
ID E1 E2 E3
1 M N O
2 P Q NULL
GO
UPDATE TA
SET E1=TB.E
FROM TB
WHERE TA.ID=TB.ID
AND PX=1
UPDATE TA
SET E2=TB.E
FROM TB
WHERE TA.ID=TB.ID
AND PX=2
UPDATE TA
SET E3=TB.E
FROM TB
WHERE TA.ID=TB.ID
AND PX=3
GO
GO
UPDATE TA
SET E1=TB.E
FROM # TB
WHERE TA.ID=TB.ID
AND PX=1
UPDATE TA
SET E2=TB.E
FROM # TB
WHERE TA.ID=TB.ID
AND PX=2
UPDATE TA
SET E3=TB.E
FROM # TB
WHERE TA.ID=TB.ID
AND PX=3
GODROP TABLE #
GOmodify
go
create table [TA]([ID] int,[E1] varchar(1),[E2] varchar(1),[E3] varchar(1))
insert [TA]
select 1,'a','b','c' union all
select 2,'d','e','f'
if object_id('[TB]') is not null drop table [TB]
go
create table [TB]([ID] int,[E] varchar(1))
insert [TB]
select 1,'M' union all
select 1,'N' union all
select 1,'O' union all
select 2,'P' union all
select 2,'Q'
go
--select * from [TA]
--select * from [TB];with szx as
(
select id,e,rn=row_number() over(partition by id order by e) from tb
)
update ta
set E1=(select e from szx where id=ta.id and rn=1)
,E2=(select e from szx where id=ta.id and rn=2)
,E3=(select e from szx where id=ta.id and rn=3)select * from [TA]
/*
ID E1 E2 E3
----------- ---- ---- ----
1 M N O
2 P Q NULL(2 行受影响)
*/