有如下:
COL1 COL2 COL3 COL4 COL6
01 01 2009-03-02
01 01 2009-03-02 12:18:4
01 01 2009-03-02 08:28:57
01 01 2009-03-03
01 01 2009-03-03 08:28:58
01 01 2009-03-04
01 01 2009-03-04 08:26:56
01 01 2009-03-05
变为:
01 01 2009-03-02 08:28:57 12:18:4
01 01 2009-03-03 08:28:58
01 01 2009-03-04 08:26:56
01 01 2009-03-05
请高手指点下啊?
COL1 COL2 COL3 COL4 COL6
01 01 2009-03-02
01 01 2009-03-02 12:18:4
01 01 2009-03-02 08:28:57
01 01 2009-03-03
01 01 2009-03-03 08:28:58
01 01 2009-03-04
01 01 2009-03-04 08:26:56
01 01 2009-03-05
变为:
01 01 2009-03-02 08:28:57 12:18:4
01 01 2009-03-03 08:28:58
01 01 2009-03-04 08:26:56
01 01 2009-03-05
请高手指点下啊?
解决方案 »
- SQL查询怎么会无结果呢?
- in 是不是相对效率低?
- 如何查询select后的倒是n行
- 求一条SQL 语句 , 高手指教!关于取“其他”值
- 在线急等:跪求一段时间序列查询SQL,具体描述如下:(看见此帖的朋友点进来看看)
- 问:两个表的数据如何相加啊~
- 很奇怪的问题,想不通!!!!
- 如何判断全局临时表的存在与否?(急!)
- 在有物理防火墙的情况下,已经打开1433端口,不通过WEB服务器,通过VB的ADO是否可以存取SQL SERVER的数据?我这里没有物理防火墙,无法验
- 怎么写SQL能实现且效率会高?
- 请高手接招:如果表中没有ID自动标示字段,如何去重复项
- sql2005 使用OPENROWSET时报错
col2,
col3,
col4=stuff((select ' '+isnull(convert(varchar(10),col4,108),'')
from tb
where col3=t.col3 and col2=t.col2
and col1=t.col1
for xml path('')),1,1,'')
from [tb] t
group by col1,col2,col3
go
create table [tb] (COL1 nvarchar(4),COL2 nvarchar(4),COL3 nvarchar(10),COL4 nvarchar(10),COL6 nvarchar(4))
insert into [tb]
select '01','01','2009-03-02',null,null union all
select '01','01','2009-03-02','12:18:40',null union all
select '01','01','2009-03-02','08:28:57',null union all
select '01','01','2009-03-03',null,null union all
select '01','01','2009-03-03','08:28:58',null union all
select '01','01','2009-03-04',null,null union all
select '01','01','2009-03-04','08:26:56',null union all
select '01','01','2009-03-05',null,nullselect col1,
col2,
col3,
col4=stuff((select ' '+col4
from tb
where col3=t.col3 and col2=t.col2 and col1=t.col1
for xml path('')),1,1,'')
from [tb] t
group by col1,col2,col3
/*
col1 col2 col3 col4
---- ---- ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
01 01 2009-03-02 12:18:40 08:28:57
01 01 2009-03-03 08:28:58
01 01 2009-03-04 08:26:56
01 01 2009-03-05 NULL(4 個資料列受到影響)
*/
col2,
col3,
col4=stuff((select ' '+col4
from tb
where col3=t.col3 and col2=t.col2 and col1=t.col1 ORDER BY COL4
for xml path('')),1,1,'')
from [tb] t
group by col1,col2,col3
这句话是什么意思吗?
COL1 COL2 COL3 COL4 COL5 Col6
01 01 2009-03-02
01 01 2009-03-02 12:18:4
01 01 2009-03-02 08:28:57
01 01 2009-03-03
01 01 2009-03-03 08:28:58
01 01 2009-03-04
01 01 2009-03-04 08:26:56
01 01 2009-03-05
变为:
01 01 2009-03-02 08:28:57 12:18:4
01 01 2009-03-03 08:28:58
01 01 2009-03-04 08:26:56
01 01 2009-03-05
请高手指点下啊?
COL1 COL2 COL3 COL4 COL5 Col6
01 01 2009-03-02
01 01 2009-03-02 12:18:4
01 01 2009-03-02 08:28:57
01 01 2009-03-03
01 01 2009-03-03 08:28:58
01 01 2009-03-04
01 01 2009-03-04 12:26:56
01 01 2009-03-05
变为:
01 01 2009-03-02 08:28:57 12:18:4
01 01 2009-03-03 08:28:58
01 01 2009-03-04 12:26:56
01 01 2009-03-05
请高手指点下啊?
go
create table [tb] (COL1 nvarchar(4),COL2 nvarchar(4),COL3 nvarchar(10),COL4 nvarchar(10),COL6 nvarchar(4))
insert into [tb]
select '01','01','2009-03-02',null,null union all
select '01','01','2009-03-02','12:18:40',null union all
select '01','01','2009-03-02','08:28:57',null union all
select '01','01','2009-03-03',null,null union all
select '01','01','2009-03-03','08:28:58',null union all
select '01','01','2009-03-04',null,null union all
select '01','01','2009-03-04','08:26:56',null union all
select '01','01','2009-03-05',null,null
if object_id('dbo.f_str')is not null drop function dbo.f_str
go
CREATE FUNCTION dbo.f_str(@id varchar(20))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r = ''
SELECT @r = @r + ' ' + isnull(COL4,'')
FROM tb
WHERE COL3=@id
RETURN STUFF(@r, 1, 1, '')
END
GO
select col1,
col2,
col3,
col4=dbo.f_str(col3)
from [tb] t
group by col1,col2,col3
/*
col1 col2 col3 col4
---- ---- ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
01 01 2009-03-02 12:18:40 08:28:57
01 01 2009-03-03 08:28:58
01 01 2009-03-04 08:26:56
01 01 2009-03-05 (4 個資料列受到影響)
*/
COL1 COL2 COL3 COL4 COL5 Col6
01 01 2009-03-02
01 01 2009-03-02 12:18:4
01 01 2009-03-02 08:28:57
01 01 2009-03-03
01 01 2009-03-03 08:28:58
01 01 2009-03-04
01 01 2009-03-04 12:26:56
01 01 2009-03-05
变为:
01 01 2009-03-02 08:28:57 12:18:4
01 01 2009-03-03 08:28:58
01 01 2009-03-04 12:26:56
01 01 2009-03-05
请高手指点下啊?
go
create table [tb] (COL1 nvarchar(4),COL2 nvarchar(4),COL3 nvarchar(10),COL4 nvarchar(10),COL6 nvarchar(4))
insert into [tb]
select '01','01','2009-03-02',null,null union all
select '01','01','2009-03-02','12:18:40',null union all
select '01','01','2009-03-02','08:28:57',null union all
select '01','01','2009-03-03',null,null union all
select '01','01','2009-03-03','08:28:58',null union all
select '01','01','2009-03-04',null,null union all
select '01','01','2009-03-04','08:26:56',null union all
select '01','01','2009-03-05',null,null
select * into #
from tb
order by COL4
select col1,
col2,
col3,
col4=stuff((select ' '+col4
from #
where col3=t.col3 and col2=t.col2 and col1=t.col1
for xml path('')),1,1,'')
from # t
group by col1,col2,col3
go
drop table #
/*
col1 col2 col3 col4
---- ---- ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
01 01 2009-03-02 08:28:57 12:18:40
01 01 2009-03-03 08:28:58
01 01 2009-03-04 08:26:56
01 01 2009-03-05 NULL(4 個資料列受到影響)
*/
go
create table [tb] (COL1 nvarchar(4),COL2 nvarchar(4),COL3 nvarchar(10),COL4 nvarchar(10),COL6 nvarchar(4))
insert into [tb]
select '01','01','2009-03-02',null,null union all
select '01','01','2009-03-02',null,''12:18:40'' union all
select '01','01','2009-03-02','08:28:57',null union all
select '01','01','2009-03-02','08:30:57',null union all
select '01','01','2009-03-03',null,null union all
select '01','01','2009-03-03','08:28:58',null union all
select '01','01','2009-03-04',null,null union all
select '01','01','2009-03-04',null,''12:18:40'' union all
select '01','01','2009-03-05',null,null题要是这样的呢,结果还是我刚才想要的结果。。
go
create table [tb] (COL1 nvarchar(4),COL2 nvarchar(4),COL3 nvarchar(10),COL4 nvarchar(10),COL6 nvarchar(10))
insert into [tb]
select '01','01','2009-03-02',null,null union all
select '01','01','2009-03-02',null,'12:18:40' union all
select '01','01','2009-03-02','08:28:57',null union all
select '01','01','2009-03-02','08:30:57',null union all
select '01','01','2009-03-03',null,null union all
select '01','01','2009-03-03','08:28:58',null union all
select '01','01','2009-03-04',null,null union all
select '01','01','2009-03-04',null,'12:18:40' union all
select '01','01','2009-03-05',null,null
select col1,
col2,
col3,
col4=stuff((select ' '+isnull(col4,col6)
from tb
where col3=t.col3 and col2=t.col2 and col1=t.col1
for xml path('')),1,1,'')
from [tb] t
group by col1,col2,col3
/*
col1 col2 col3 col4
---- ---- ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
01 01 2009-03-02 12:18:40 08:28:57 08:30:57
01 01 2009-03-03 08:28:58
01 01 2009-03-04 12:18:40
01 01 2009-03-05 NULL(4 個資料列受到影響)*/
---try
declare @tb table(col1 nvarchar(10),col2 nvarchar(10),col3 datetime)
insert into @tb select 01,01,'2009-03-02'
union all select 01,01,'2009-03-02 12:18:4'
union all select 01,01,'2009-03-02 08:28:57'
union all select 01,01,'2009-03-03'
union all select 01,01,'2009-03-03 08:28:58'
union all select 01,01,'2009-03-04'
union all select 01,01,'2009-03-04 08:26:56'
union all select 01,01,'2009-03-05'
select distinct a.col1,a.col2,col3= replace((select ''+ max(convert(nvarchar(10),col3,121))from @tb
where convert(nvarchar(10),col3,121)=convert(nvarchar(10),a.col3,121) for xml path(''))
+stuff((select ','+ replace(convert(nvarchar(10),col3,108),'00:00:00','')from @tb
where convert(nvarchar(10),col3,121)=convert(nvarchar(10),a.col3,121) for xml path('')),1,1,''),',',' ')
from @tb a
/*col1 col2 col3
---------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1 2009-03-02 12:18:04 08:28:57
1 1 2009-03-03 08:28:58
1 1 2009-03-04 08:26:56
1 1 2009-03-05(4 行受影响)
*/
go
create table [tb] (COL1 nvarchar(4),COL2 nvarchar(4),COL3 nvarchar(10),COL4 nvarchar(10),COL6 nvarchar(10))
insert into [tb]
select '01','01','2009-03-02',null,null union all
select '01','01','2009-03-02',null,'12:18:40' union all
select '01','01','2009-03-02','08:28:57',null union all
select '01','01','2009-03-02','08:30:57',null union all
select '01','01','2009-03-03',null,null union all
select '01','01','2009-03-03','08:28:58',null union all
select '01','01','2009-03-04',null,null union all
select '01','01','2009-03-04',null,'12:18:40' union all
select '01','01','2009-03-05',null,null
select max(col1),max(col2),col3,isnull(max(col4),'')+' '+isnull(max(col6),'')
from tb
group by col3drop table tb