select id ,factory,rm,userName from t_masterselect id,master_id,qty,days from t_details-- master爲主表,Details爲子表 一對多的關係, 通過主表ID關聯子表 master_Id
--現在需要查詢數據,同一days的數據組合成一條數據
--例如:
/*
Master 有數據:ID factory rm userName
1 AMD a john
3 MIC c john
4 Test d jack
Details 數據ID master_ID qty days
1 1 50 2009-01-01
2 1 60 2010-10-15
3 3 90 2010-10-15
4 4 40 2010-10-15組合後數據： days UserName Factory rm qty
2009-01-01 john AMD A 50
2010-10-15 john,jack AMD,MIC,Test a,c,d 190就是以days爲主鍵,組合一條新的數據,同時根據權限，管理員可以查詢所有userName的數據
其他人只能查詢自己的Username地數據盡可能的使用視圖實現.
*/
--現在需要查詢數據,同一days的數據組合成一條數據
--例如:
/*
Master 有數據:ID factory rm userName
1 AMD a john
3 MIC c john
4 Test d jack
Details 數據ID master_ID qty days
1 1 50 2009-01-01
2 1 60 2010-10-15
3 3 90 2010-10-15
4 4 40 2010-10-15組合後數據： days UserName Factory rm qty
2009-01-01 john AMD A 50
2010-10-15 john,jack AMD,MIC,Test a,c,d 190就是以days爲主鍵,組合一條新的數據,同時根據權限，管理員可以查詢所有userName的數據
其他人只能查詢自己的Username地數據盡可能的使用視圖實現.
*/
-- Author :fredrickhu(小F，向高手学习)
-- Date :2011-05-14 11:58:34
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[Master]
if object_id('[Master]') is not null drop table [Master]
go
create table [Master]([ID] int,[factory] varchar(4),[rm] varchar(1),[userName] varchar(4))
insert [Master]
select 1,'AMD','a','john' union all
select 3,'MIC','c','john' union all
select 4,'Test','d','jack'
--> 测试数据：[Details]
if object_id('[Details]') is not null drop table [Details]
go
create table [Details]([ID] int,[master_ID] int,[qty] int,[days] datetime)
insert [Details]
select 1,1,50,'2009-01-01' union all
select 2,1,60,'2010-10-15' union all
select 3,3,90,'2010-10-15' union all
select 4,4,40,'2010-10-15'
--------------开始查询--------------------------
;with f as
(
select
a.*,b.qty
from
(
select
convert(varchar(10),b.[days],120) as [days],a.[userName],a.[factory],a.rm
from
[Master] a,[Details] b
where
a.ID=b.master_ID
)a,
(select convert(varchar(10),[days],120) as [days],SUM(qty) as qty from [Details] group by convert(varchar(10),[days],120) )b
where
a.days=b.days
)
select
days,
[userName]=stuff((select ','+[userName] from f where days=t.days for xml path('')), 1, 1, '') ,
[factory]=stuff((select ','+[factory] from f where days=t.days for xml path('')), 1, 1, '') ,
[rm]=stuff((select ','+[rm] from f where days=t.days for xml path('')), 1, 1, '') ,
qty
from
f t
group by
days,qty
----------------结果----------------------------
/* days userName factory rm qty
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------
2009-01-01 john AMD a 50
2010-10-15 john,john,jack AMD,MIC,Test a,c,d 190(2 行受影响)*/
樹哥，如果需要查詢某一User的記錄應該怎麽增加條件？
同一用戶的記錄需合併。。
2010-10-15 john,john,jack
應爲 2010-10-15 john,jack
那你把user當成參數傳遞過去不就OK了