組織SQL語句

Question

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     AMD,MIC           a,c    150
2010-10-15     jack        Test            d     40
 
就是以days爲主鍵,組合一條新的數據,同時根據權限，管理員可以查詢所有userName的數據
其他人只能查詢自己的Username地數據數據庫爲SQL2000*/

Answer 1

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        AMD,MIC         a,c    150
2010-10-15     jack         Test           d       40
 
就是以days爲主鍵,組合一條新的數據,同時根據權限，管理員可以查詢所有userName的數據
其他人只能查詢自己的Username地數據數據庫爲SQL2000*/

Answer 2

你是怎么判读一个USERNAME是不是管理员的

Answer 3

存儲過程傳遞參數　@UserName，如果爲管理員，userName 爲空，否則，@UserName則爲相應的UserName

Answer 4

--按楼主给的测试数据和结果！/*
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 AMD,MIC a,c 150
2010-10-15 jack Test d 40
*/create table Master(id int,factory varchar(10),rm varchar(10),userName varchar(10))
insert into Master
select 1 ,'AMD' ,'a','john' union all
select 3 ,'MIC' ,'c' ,'john' union all
select 4 ,'Test' ,'d' ,'jack'create table Details(id int,master_id int,qty int,days datetime)
insert into 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'
gocreate function f_str(@data varchar(10),@name varchar(10),@state int)
returns varchar(8000)
as
begin
    declare @r varchar(8000)
    set @r = ''
if(@state = 0)
begin
select @r = @r + ',' + a.factory
from Master a join Details b on a.id = b.master_id
where a.userName = @name and convert(varchar(10),b.days,120) = @data
end
else
begin
select @r = @r + ',' + a.rm
from Master a join Details b on a.id = b.master_id
where a.userName = @name and convert(varchar(10),b.days,120) = @data
end
    return stuff(@r, 1, 1, '')
end
goselect convert(varchar(10),b.days,120) as days,a.userName,
dbo.f_str(convert(varchar(10),b.days,120),a.userName,0) as factory,
dbo.f_str(convert(varchar(10),b.days,120),a.userName,1) as rm,
sum(qty) as qty
from Master a join Details b on a.id = b.master_id
group by convert(varchar(10),b.days,120),a.userNamedrop function f_str
drop table Master,Details/*
days       userName   factory                                                                                                                                                                                                                                                          rm                                                                                                                                                                                                                                                               qty
---------- ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------
2010-10-15 jack       Test                                                                                                                                                                                                                                                             d                                                                                                                                                                                                                                                                40
2009-01-01 john       AMD                                                                                                                                                                                                                                                              a                                                                                                                                                                                                                                                                50
2010-10-15 john       AMD,MIC                                                                                                                                                                                                                                                          a,c                                                                                                                                                                                                                                                              150(3 行受影响)
*/

Answer 5

--> 生成测试数据表: [Master]
IF OBJECT_ID('[Master]') IS NOT NULL
DROP TABLE [Master]
GO
CREATE TABLE [Master] ([ID] [int],[factory] [nvarchar](10),[rm] [nvarchar](10),[userName] [nvarchar](10))
INSERT INTO [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 INTO [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'--SELECT * FROM [Master]
--SELECT * FROM [Details]-->SQL查询如下:
;WITH t AS 
(
SELECT CONVERT( varchar(10),b.days,23) days,a.username, a.Factory ,a.rm,b.qty
FROM [Master] a
JOIN [Details] b 
ON a.ID=b.master_ID
)
SELECT days,username,
STUFF((SELECT ','+factory FROM t a WHERE a.days=t.days AND username=t.username FOR XML PATH('')),1,1,'') factory,
STUFF((SELECT ','+rm FROM t a WHERE a.days=t.days AND username=t.username FOR XML PATH('')),1,1,'') rm,
SUM(qty) qty
FROM t 
GROUP BY days,username
ORDER BY (SELECT MIN(days) FROM t a WHERE username=t.username),username,days/*
days username factory rm qty
2009-01-01 john AMD a 50
2010-10-15 john AMD,MIC a,c 150
2010-10-15 jack Test d 40
*/

Answer 6

你這是sql2005吧。
2000裏面好象不能用with吧。。用臨時表試試。