首先是在sqlserver 2008下运行的:
CheckOut 表 列名如下:
CheckOut_OrderInformation_ID CheckOut_Time
11 2012/09/08
12 2012/09/09
13 2012/09/10
14 2012/09/10
15 2012/09/10
16 2012/09/08OrderInformation 表 列名如下:
OrderInformation_ID OrderInformation_NO
11 EX001
12 EX002
13 EX003
14 EX004
15 EX005
16 EX006
想要的结果为:
C_Time Order_NO
2012/09/08 EX001,EX006
2012/09/09 EX002
2012/09/10 EX003,EX004,EX005请高手帮帮忙,很急啊!谢谢了!!!
CheckOut 表 列名如下:
CheckOut_OrderInformation_ID CheckOut_Time
11 2012/09/08
12 2012/09/09
13 2012/09/10
14 2012/09/10
15 2012/09/10
16 2012/09/08OrderInformation 表 列名如下:
OrderInformation_ID OrderInformation_NO
11 EX001
12 EX002
13 EX003
14 EX004
15 EX005
16 EX006
想要的结果为:
C_Time Order_NO
2012/09/08 EX001,EX006
2012/09/09 EX002
2012/09/10 EX003,EX004,EX005请高手帮帮忙,很急啊!谢谢了!!!
FROM CheckOut a INNER JOIN OrderInformation b ON a.CheckOut_OrderInformation_ID =b.OrderInformation_ID
SELECT a.C_Time ,
Order_NO = STUFF(( SELECT ',' + Order_NO
FROM test
WHERE C_Time = a.C_Time
FOR
XML PATH('')
), 1, 1, '')
FROM test a
GROUP BY a.C_Time
go
create table [CheckOut]([CheckOut_OrderInformation_ID] int,[CheckOut_Time] datetime)
insert [CheckOut]
select 11,'2012/09/08' union all
select 12,'2012/09/09' union all
select 13,'2012/09/10' union all
select 14,'2012/09/10' union all
select 15,'2012/09/10' union all
select 16,'2012/09/08'
go
if object_id('[OrderInformation]') is not null drop table [OrderInformation]
go
create table [OrderInformation]([OrderInformation_ID] int,[OrderInformation_NO] varchar(5))
insert [OrderInformation]
select 11,'EX001' union all
select 12,'EX002' union all
select 13,'EX003' union all
select 14,'EX004' union all
select 15,'EX005' union all
select 16,'EX006'
go;with cte as
( select a.CheckOut_Time,b.OrderInformation_NO
from CheckOut a
join OrderInformation b on b.OrderInformation_ID=a.CheckOut_OrderInformation_ID
)
select convert(varchar(10),t.CheckOut_Time,120) as C_Time,
Order_NO=stuff((select ','+OrderInformation_NO from cte where convert(varchar(10),CheckOut_Time,120)=convert(varchar(10),t.CheckOut_Time,120) for xml path('')),1,1,'')
from cte t
group by convert(varchar(10),t.CheckOut_Time,120)/**
C_Time Order_NO
---------- ---------------------------------
2012-09-08 EX001,EX006
2012-09-09 EX002
2012-09-10 EX003,EX004,EX005(3 行受影响)**/
要看他的是什么类型的,如果是datetime的需要转换一下
;with cte as
( select a.CheckOut_Time,b.OrderInformation_NO
from CheckOut a
join OrderInformation b on b.OrderInformation_ID=a.CheckOut_OrderInformation_ID
)
select convert(varchar(10),t.CheckOut_Time,120) as C_Time,
Order_NO=stuff((select ','+OrderInformation_NO from cte where convert(varchar(10),CheckOut_Time,120)=convert(varchar(10),t.CheckOut_Time,120) for xml path('')),1,1,'')
from cte t
group by convert(varchar(10),t.CheckOut_Time,120)
表有多少数据与实现功能无关,表数据多少只是性能的问题