mDate Person_id Value1 value2 value3
2005-02-01 11 (1)20 (1)20 20
2005-02-02 2 (2)10 (2)10 10
2005-02-02 11 (1)10 (2)10 10
2005-02-03 1 (1)10 (1)10 10
2005-02-04 11 (3)10 (3)10 10
Value1是表A中Value1的合计和group by Person_id ,mDate 得出,其括号里面的值是该表中的iType值,Value2是表B中Value2的合计和group by Person_id ,mDate 得出,其括号里面的值是该表中的iType1值,Value3是表C中Value3的合计和group by Person_id ,mDate 得出它没有iType值,不用加上类型。
如何实现该Sql????----------------------------
怕看不清,格式一下。
2005-02-01 11 (1)20 (1)20 20
2005-02-02 2 (2)10 (2)10 10
2005-02-02 11 (1)10 (2)10 10
2005-02-03 1 (1)10 (1)10 10
2005-02-04 11 (3)10 (3)10 10
Value1是表A中Value1的合计和group by Person_id ,mDate 得出,其括号里面的值是该表中的iType值,Value2是表B中Value2的合计和group by Person_id ,mDate 得出,其括号里面的值是该表中的iType1值,Value3是表C中Value3的合计和group by Person_id ,mDate 得出它没有iType值,不用加上类型。
如何实现该Sql????----------------------------
怕看不清,格式一下。
where ...................
Create Table A(mDate DateTime, Person_id int, iType int, Value1 int)
Insert Into A
select '2005-02-01', 11, 1, 10 union all
select '2005-02-01', 11, 1, 10 union all
select '2005-02-02', 2 , 2 , 10 union all
select '2005-02-02', 11 , 1 , 10 union all
select '2005-02-03', 1, 1 , 10 union all
select '2005-02-04', 11 , 3 , 10
--------------建B表-------------------------------------
Create Table B(mDate datetime , Person_id int, itype1 int, Value2 int)
insert into B
select '2005-02-01', 11, 1, 10 union all
select '2005-02-01', 11, 1, 10 union all
select '2005-02-02', 2 , 2 , 10 union all
select '2005-02-02', 11, 2 , 10 union all
select '2005-02-03', 1, 1 , 10 union all
select '2005-02-04', 11, 3 , 10
--------------建C表-----------------------------------
Create Table C(mDate datetime, Person_id int, Value3 int)
insert into C
select '2005-02-01' , 11, 10 union all
select '2005-02-01' , 11 , 10 union all
select '2005-02-02' , 2 , 10 union all
select '2005-02-02', 11 , 10 union all
select '2005-02-03', 1 , 10 union all
select '2005-02-04', 11 , 10
------------下面的這段是一句sql 語句-----------實現語句---------------------
select f.mdate,f.person_id,value1='('+cast(f.itype as varchar(4))+')'+cast(f.value1 as varchar(4)),
value2='('+cast(k.itype1 as varchar(4))+')'+cast(k.value2 as varchar(4)),h.value3 from
(select mdate,person_id,itype=min(itype),value1=sum(value1) from A group by mdate,person_id) f
inner join
(select mdate,person_id,itype1=min(itype1),value2=sum(value2) from B group by mdate,person_id) k
on f.mdate=k.mdate and f.person_id=k.person_id
left join
(select mdate,person_id,value3=sum(value3) from C group by mdate,person_id) h
on f.mdate=h.mdate and f.person_id=h.person_id order by f.mdate,f.person_id
------------------語句結束-----------------------------------------------
-------------輸出----------------------------------
mDate Person_id Value1 value2 value3
2005-02-01 11 (1)20 (1)20 20
2005-02-02 2 (2)10 (2)10 10
2005-02-02 11 (1)10 (2)10 10
2005-02-03 1 (1)10 (1)10 10
2005-02-04 11 (3)10 (3)10 10
select f.mdate,f.person_id,value1='('+cast(f.itype as varchar(4))+')'+cast(f.value1 as varchar(4)),
value2='('+cast(k.itype1 as varchar(4))+')'+cast(k.value2 as varchar(4)),h.value3 from
(select mdate,person_id,itype=min(itype),value1=sum(value1) from A group by mdate,person_id) f
inner join
(select mdate,person_id,itype1=min(itype1),value2=sum(value2) from B group by mdate,person_id) k
on f.mdate=k.mdate and f.person_id=k.person_id
left join
(select mdate,person_id,value3=sum(value3) from C group by mdate,person_id) h
on f.mdate=h.mdate and f.person_id=h.person_id order by f.mdate,f.person_id
--------整個這段實為一句語句---------------------
mDate Person_id Value1 value2 value3
2005-02-01 11 (1)20 (1)20 20
2005-02-02 2 (1)10 (2)10 10
2005-02-02 11 (1)10 (2)10 10
2005-02-03 1 (1)10 (1)10 10
2005-02-04 11 (3)10 (3)10 10
再執行我的 這段語句應該是可以的
--------上面的提取出來 是用下面的語句實現-----
select f.mdate,f.person_id,value1='('+cast(f.itype as varchar(4))+')'+cast(f.value1 as varchar(4)),
value2='('+cast(k.itype1 as varchar(4))+')'+cast(k.value2 as varchar(4)),h.value3 from
(select mdate,person_id,itype=min(itype),value1=sum(value1) from A group by mdate,person_id) f
inner join
(select mdate,person_id,itype1=min(itype1),value2=sum(value2) from B group by mdate,person_id) k
on f.mdate=k.mdate and f.person_id=k.person_id
left join
(select mdate,person_id,value3=sum(value3) from C group by mdate,person_id) h
on f.mdate=h.mdate and f.person_id=h.person_id order by f.mdate,f.person_id
--------整個這段實為一句語句---------------------
mDate Person_id Value1 value2 value3
2005-02-01 11 (1)20 20
2005-02-02 2 (2)10 10
2005-02-02 11 (2)10 10
2005-02-03 1 (1)10 10
2005-02-04 11 (3)10 10
你的Inner join 已经不能用了,所以不能结贴。等真的有人解决了,就结贴,不好意思啊,不过你的分还是会有的,放心好了。