SQL 去重复 并且合并

[xiaoke3411]

各位:
我是个新手, 这个问题困扰了我好几天了, 麻烦高人指点一下.

问题是这样的, 我要从下面这个表中取一个记录

编号 指数简称 收盘 涨跌幅 最后修改日期
id zsmc sp zdf zhxgrq
1 港股 11890 -186 2008-10-13 13:07:08 000
2 美股 8090 -56 2008-10-13 18:08:07 000
3 日经 27683 -238 2008-10-13 08:06:07 000

4 港股 11990 +100 2008-10-14 13:07:08 000
5 日经 27689 +6 2008-10-14 08:06:07 000
6 美股 8050 -40 2008-10-14 18:08:07 000

7 日经 27989 +300 2008-10-15 08:06:07 000
8 港股 11890 -100 2008-10-15 13:07:08 000
9 美股 8070 +20 2008-10-15 18:08:07 000




想从表中得到这样一条查询记录, 应该如何写语句

ID RQ NR
1 2009-10-13 周边市场: 港股收盘11890 涨跌-186 美股收盘 8090 涨跌-56 日经收盘27683 涨跌-238
2 2009-10-14 周边市场: 港股收盘11900 涨跌+100 美股收盘 8050 涨跌-40 日经收盘27689 涨跌+6
3 2009-10-15 周边市场: 港股收盘11890 涨跌-100 美股收盘 8070 涨跌+20 日经收盘27989 涨跌+300

[xiaoke3411]

我会一直在线等, 如有回复, 我会第一时间回应.

[xiaoke3411]

DD

[lass_name]

create function [dbo].[f_str](@date varchar(20))
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret+','+zsmc+'涨跌 '+zdf from tab where convert(varchar(10),zhxgrq)  = @date
set @ret = stuff(@ret,1,1,'')
return @ret 
end


select rq,dbo.f_str(convert(varchar(10),zhxgrq) from tb group by convert(varchar(10),zhxgrq) 

[feixianxxx]

select 
 ID=ROW_NUMBER() OVER(ORDER BY GETDATE()),
 RQ=CONVERT(varchar(10),zhxgrq,120),
 NR='周边市场:'+(select ' '+zsmc+'收盘'+rtrim(sp)+' '+'涨跌'+RTRIM(zdf) from tb where CONVERT(varchar(10),k.zhxgrq,120)=CONVERT(varchar(10),zhxgrq,120) for XML path (''))
 from tb k 

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-11-18 14:48:58
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([id] int,[zsmc] varchar(4),[sp] int,[zdf] int,[zhxgrq] datetime)
insert [tb]
select 1,'港股',11890,-186,'2008-10-13 13:07:08' union all
select 2,'美股',8090,-56,'2008-10-13 18:08:07' union all
select 3,'日经',27683,-238,'2008-10-13 08:06:07' union all
select 4,'港股',11990,+100,'2008-10-14 13:07:08' union all
select 5,'日经',27689,+6,'2008-10-14 08:06:07' union all
select 6,'美股',8050,-40,'2008-10-14 18:08:07' union all
select 7,'日经',27989,+300,'2008-10-15 08:06:07' union all
select 8,'港股',11890,-100,'2008-10-15 13:07:08' union all
select 9,'美股',8070,+20,'2008-10-15 18:08:07'
--------------开始查询--------------------------
select
 convert(varchar(10),[zhxgrq],120) as RQ,
 [zsmc]=stuff((select ' '+[zsmc]+' '+ltrim(zdf)+' '+ltrim(sp) from tb t where convert(varchar(10),[zhxgrq],120)=convert(varchar(10),tb.[zhxgrq],120) for xml path('')), 1, 1, '') 
from
 tb 
group by
 convert(varchar(10),[zhxgrq],120)
----------------结果----------------------------
/*RQ         zsmc
---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2008-10-13 港股 -186 11890 美股 -56 8090 日经 -238 27683
2008-10-14 港股 100 11990 日经 6 27689 美股 -40 8050
2008-10-15 日经 300 27989 港股 -100 11890 美股 20 8070

(3 行受影响) 
*/

[icelovey]

declare @TB table([id] int,[zsmc] varchar(4),[sp] int,[zdf] int,[zhxgrq] datetime,[C6] varchar(3))
insert @TB
select 1,'港股',11890,-186,'2008-10-13 13:07:08','000' union all
select 2,'美股',8090,-56,'2008-10-13 18:08:07','000' union all
select 3,'日经',27683,-238,'2008-10-13 08:06:07','000' union all
select 4,'港股',11990,+100,'2008-10-14 13:07:08','000' union all
select 5,'日经',27689,+6,'2008-10-14 08:06:07','000' union all
select 6,'美股',8050,-40,'2008-10-14 18:08:07','000' union all
select 7,'日经',27989,+300,'2008-10-15 08:06:07','000' union all
select 8,'港股',11890,-100,'2008-10-15 13:07:08','000' union all
select 9,'美股',8070,+20,'2008-10-15 18:08:07','000'

;with cte as
(
	select CONVERT(NVARCHAR,[zhxgrq],111) AS ID ,[zsmc]+'收盘'+CAST([sp] AS NVARCHAR(10))+' 涨跌'+CAST([zdf] AS NVARCHAR(10)) AS VALUE
	from @TB
)


SELECT A.ID AS RQ, '周边市场: '+N.[values]
FROM(SELECT DISTINCT id FROM CTE)A OUTER APPLY( 
        SELECT [values]= STUFF(REPLACE(REPLACE( 
            ( 
                SELECT value FROM CTE N 
                WHERE id = A.id 
                FOR XML AUTO 
            ), '<N value="', ','), '"/>', ''), 1, 1, '') 
)N 


/*

RQ                             
------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2008/10/13                     周边市场: 港股收盘11890 涨跌-186,美股收盘8090 涨跌-56,日经收盘27683 涨跌-238
2008/10/14                     周边市场: 港股收盘11990 涨跌100,日经收盘27689 涨跌6,美股收盘8050 涨跌-40
2008/10/15                     周边市场: 日经收盘27989 涨跌300,港股收盘11890 涨跌-100,美股收盘8070 涨跌20

(3 row(s) affected)

[--小F--]

差的字自己到语句中加上

[sych888]

--提供一个函数
create function GetStr(@time varchar(10))
returns varchar(500)
as
begin
declare @ss varchar(500)
set @ss='周边市场 '
select @ss=@ss+zsmc+' 收盘'+ltrim(sp)+' 涨幅'+ltrim(zdf)
from tb8
where convert(varchar(10),zhxgrq,120)=@time

return @ss
end