求教各位大俠,幫下小弟解困。謝謝!Ta表P_No, Rm_Code, Billno_Out, BillDate_Out, Qty_Out, Billno_Return, BillDate_Return, Qty_Return, Billno_Ship, BillDate_ship, Qty_Ship
B1214 9M-0008/24 P110223 2011-01-01 26 NULL NULL 0 NULL NULL 0
B1214 9M-0008/24 P110208 2011-01-01 26 NULL NULL 0 NULL NULL 0
B1214 9M-0008/24 NULL NULL 0 NULL NULL 0 W110501 2011-01-06 31
B1214 9M-0008/24 NULL NULL 0 M110402 2011-01-30 21 NULL NULL 0如何查詢得到如下的結果:
P_No, Rm_Code, Billno_Out, BillDate_Out, Qty_Out, Billno_Return, BillDate_Return, Qty_Return, Billno_Ship, BillDate_ship, Qty_Ship
B1214 9M-0008/24 P110223 2011-01-01 26 M110402 2011-01-30 21 W110501 2011-01-06 31
B1214 9M-0008/24 P110208 2011-01-01 26 NULL NULL 0 NULL NULL 0
B1214 9M-0008/24 P110223 2011-01-01 26 NULL NULL 0 NULL NULL 0
B1214 9M-0008/24 P110208 2011-01-01 26 NULL NULL 0 NULL NULL 0
B1214 9M-0008/24 NULL NULL 0 NULL NULL 0 W110501 2011-01-06 31
B1214 9M-0008/24 NULL NULL 0 M110402 2011-01-30 21 NULL NULL 0如何查詢得到如下的結果:
P_No, Rm_Code, Billno_Out, BillDate_Out, Qty_Out, Billno_Return, BillDate_Return, Qty_Return, Billno_Ship, BillDate_ship, Qty_Ship
B1214 9M-0008/24 P110223 2011-01-01 26 M110402 2011-01-30 21 W110501 2011-01-06 31
B1214 9M-0008/24 P110208 2011-01-01 26 NULL NULL 0 NULL NULL 0
max(Qty_Return),max(Billno_Ship),max(BillDate_ship),max(Qty_Ship)
from Ta
group by P_No, Rm_Code)
union
(select P_No, Rm_Code,min(Billno_Out),min(BillDate_Out),min(Qty_Out),min(Billno_Return),min(BillDate_Return),
min(Qty_Return),min(Billno_Ship),min(BillDate_ship),min(Qty_Ship)
from Ta
group by P_No, Rm_Code)
(p_no char(10),rm_code char(10),billno_out char(10),billDate_out smalldatetime,Qty_out decimal(2),billno_return char(10),billDate_return smalldatetime,Qty_return decimal(2),billno_ship char(10),billDate_ship smalldatetime,Qty_ship decimal(2)
)
insert @Ta
select 'B1214','9M-0008/24','P110223','2011-01-01',26,NULL,NULL,0,NULL,NULL,0 union
select 'B1214','9M-0008/24','P110208','2011-01-01',26,NULL,NULL,0,NULL,NULL,0 union
select 'B1214','9M-0008/24',NULL,NULL,0,NULL,NULL,0,'W110501','2011-01-06',31 union
select 'B1214','9M-0008/24',NULL,NULL,0,'M110402','2011-01-30',21,NULL,NULL,0
select * from @Ta
P_No, Rm_Code,
max(Billno_Out),max(BillDate_Out),max(Qty_Out),max(Billno_Return),max(BillDate_Return),
max(Qty_Return),max(Billno_Ship),max(BillDate_ship),max(Qty_Ship)
from
Ta
group by
P_No, Rm_Code
union all
select
P_No,
Rm_Code,min(Billno_Out),min(BillDate_Out),min(Qty_Out),min(Billno_Return),min(BillDate_Return),
min(Qty_Return),min(Billno_Ship),min(BillDate_ship),min(Qty_Ship)
from
Ta
group by
P_No, Rm_Code
B1214 9M-0008/24 P110208 2011-01-01 00:00:00 0 M110402 2011-01-30 00:00:00 0 W110501 2011-01-06 00:00:00
B1214 9M-0008/24 P110223 2011-01-01 00:00:00 26 M110402 2011-01-30 00:00:00 21 W110501 2011-01-06 00:00:00
create table #t(
P_No varchar(50),
Rm_Code varchar(50),
Billno_Out varchar(50),
BillDate_Out datetime,
Qty_Out varchar(50),
Billno_Return varchar(50),
BillDate_Return datetime,
Qty_Return varchar(50),
Billno_Ship varchar(50),
BillDate_ship datetime,
Qty_Ship varchar(50)
)
insert into #t( P_No, Rm_Code, Billno_Out, BillDate_Out, Qty_Out, Billno_Return, BillDate_Return, Qty_Return,Billno_Ship, BillDate_ship, Qty_Ship)
select
'B1214', '9M-0008/24', 'P110223', '2011-01-01', '26', NULL, NULL, '0', NULL ,NULL, '0' union select
'B1214', '9M-0008/24', 'P110208', '2011-01-01', '26', NULL, NULL, '0', NULL, NULL, '0' union select
'B1214', '9M-0008/24', NULL, NULL, '0', NULL, NULL, '0', 'W110501', '2011-01-06', '31' union select
'B1214', '9M-0008/24', NULL, NULL, '0', 'M110402', '2011-01-30', '21', NULL, NULL, '0'select
P_No, Rm_Code,
max(Billno_Out),max(BillDate_Out),max(Qty_Out),max(Billno_Return),max(BillDate_Return),
max(Qty_Return),max(Billno_Ship),max(BillDate_ship),max(Qty_Ship)
from
#t
group by
P_No, Rm_Code
union all
select
P_No,
Rm_Code,min(Billno_Out),min(BillDate_Out),min(Qty_Out),min(Billno_Return),min(BillDate_Return),
min(Qty_Return),min(Billno_Ship),min(BillDate_ship),min(Qty_Ship)
from
#t
group by
P_No, Rm_Code
B1214 9M-0008/24 P110223 2011-01-01 00:00:00 26 M110402 2011-01-30 00:00:00 21 W110501 2011-01-06 00:00:00 31
B1214 9M-0008/24 P110208 2011-01-01 00:00:00 0 M110402 2011-01-30 00:00:00 0 W110501 2011-01-06 00:00:00 0同我想要的有點出入,請幫忙再看看,謝謝
B1214 9M-0008/24 P110223 2011-01-01 26 M110402 2011-01-30 21 W110501 2011-01-06 31
B1214 9M-0008/24 P110208 2011-01-01 26 NULL NULL 0 NULL NULL 0
B1214 9M-0008/24 P110223 2011-01-01 00:00:00 26 M110402 2011-01-30 00:00:00 21 W110501 2011-01-06 00:00:00 31
B1214 9M-0008/24 P110208 2011-01-01 00:00:00 0 M110402 2011-01-30 00:00:00 0 W110501 2011-01-06 00:00:00 0同上面幾位的結果都是一樣的。