CREATE TABLE t1(日期 datetime,甲盈利 int,乙盈利 int) INSERT t1 select '2006-01-01',1000,3000 union all select '2006-01-02',500,2000 union all select '2006-01-03',2000,3500SELECT A.人员,A.当日盈利 FROM ( SELECT 日期,甲盈利 as '当日盈利' ,'甲' as '人员' from t1 union all select 日期,乙盈利 as '当日盈利', '乙' as '人员' from t1) A where 日期='2006-01-01'
--借用下測試數據,只有甲,乙2個的話,就可以union --人員多的話,就建functionCREATE TABLE t1([date] datetime,A int,B int) INSERT t1 select '2006-01-01',1000,3000 union all select '2006-01-02',500,2000 union all select '2006-01-03',2000,3500declare @today datetime set @today='2006-01-01'select T.renyuan, T.today,TT.total from (select 'A' as renyuan,A as today from t1 where [date]=@today union select 'B' as renyuan,B as today from t1 where [date]=@today) T left join (select 'A' as renyuan,sum(A) as total from t1 union select 'B' as renyuan ,sum(B) as total from t1) TT on T.renyuan=TT.renyuan/*The result:*/ renyuan today total ------- ----------- ----------- A 1000 3500 B 3000 8500
INSERT t1 select '2006-01-01',1000,3000
union all select '2006-01-02',500,2000
union all select '2006-01-03',2000,3500SELECT A.人员,A.当日盈利
FROM
(
SELECT 日期,甲盈利 as '当日盈利' ,'甲' as '人员' from t1
union all
select 日期,乙盈利 as '当日盈利', '乙' as '人员' from t1) A
where 日期='2006-01-01'
甲 1000 3500
乙 3000 8500怎么办 我按你的方法再连接一个表B(算总盈利的),可是要sum就要group by B.人员,用了group表A的字段又投影不出来
--人員多的話,就建functionCREATE TABLE t1([date] datetime,A int,B int)
INSERT t1 select '2006-01-01',1000,3000
union all select '2006-01-02',500,2000
union all select '2006-01-03',2000,3500declare @today datetime
set @today='2006-01-01'select T.renyuan, T.today,TT.total from
(select 'A' as renyuan,A as today from t1 where [date]=@today
union
select 'B' as renyuan,B as today from t1 where [date]=@today) T
left join
(select 'A' as renyuan,sum(A) as total from t1
union
select 'B' as renyuan ,sum(B) as total from t1) TT
on T.renyuan=TT.renyuan/*The result:*/
renyuan today total
------- ----------- -----------
A 1000 3500
B 3000 8500