行變列

表 byf10
858,2F000858,36193845,2006-03-16 00:00:00.000,2,5210216,訊網有限公司,CHRISTINE,35292970,,5210,05,1,,深圳市新達實業有限公司,蔣先生,0755-83490151,,5125,00,1,30.00,1.0340,1.0305,HKD,161.00,1,.00,.00,.00,161.00,161.00,RMB,166,1,.00,.00,135.00,135.00,0,5210,蘇蓓,2006-03-14 10:44:39.000
表byf01
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,RMB,NTD,3.9400,陳佳琦    ,1,2006-03-16 09:25:31.000,3.9400,1.0010,1126,.0200,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,RMB,HKD,.9540,陳佳琦    ,1,2006-03-16 09:43:31.000,.9540,.0010,1127,.0020,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,NTD,RMB,.2410,陳佳琦    ,1,2006-03-16 09:59:31.000,.2410,.0010,1128,.0020,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,NTD,HKD,.2340,陳佳琦    ,1,2006-03-16 09:12:32.000,.2340,.0010,1129,.0020,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,HKD,RMB,1.0340,陳佳琦    ,1,2006-03-16 09:31:32.000,1.0340,1.0010,1130,.0020,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,HKD,NTD,4.1200,陳佳琦    ,1,2006-03-16 09:47:32.000,4.1200,1.0010,1131,.0200,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,RMB,NTD,3.9400,陳佳琦    ,1,2006-03-16 09:25:31.000,3.9400,1.0010,1126,.0200,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,RMB,HKD,.9540,陳佳琦    ,1,2006-03-16 09:43:31.000,.9540,.0010,1127,.0020,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,NTD,RMB,.2410,陳佳琦    ,1,2006-03-16 09:59:31.000,.2410,.0010,1128,.0020,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,NTD,HKD,.2340,陳佳琦    ,1,2006-03-16 09:12:32.000,.2340,.0010,1129,.0020,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,HKD,RMB,1.0340,陳佳琦    ,1,2006-03-16 09:31:32.000,1.0340,1.0010,1130,.0020,NULL
2006-03-16 00:00:00.000,2006-03-16 00:00:00.000,HKD,NTD,4.1200,陳佳琦    ,1,2006-03-16 09:47:32.000,4.1200,1.0010,1131,.0200,NULL
寫個SQL語句得到下面的結果
2F000858,5210,5125,HKD,161.00,RMB,.00,135.00,NULL,4.1200,3.9400

二種方法：
第一種方法：

/* 查找出收款幣別轉付款幣別對應的及時匯率*/

create view byf10_b10_30b10_40view as

select DISTINCT byf10.B10_01 as b10_30b10_40B10_01,byf01.B01_05 as b10_30b10_40B01_05,byf10.B10_09,byf10.B10_17,byf10.B10_30,byf10.B10_31,byf10.B10_40,byf10.B10_43,byf10.B10_46

from byf10

left join byf01

on byf10.B10_30+byf10.B10_40=byf01.B01_02+byf01.B01_03

where byf01.B01_01<=byf10.b10_02 and byf10.b10_02<=byf01.B01_011

/* 查找出收款幣別轉臺幣匯率*/

create view byf10_b10_30NTDview as

select DISTINCT byf10.B10_01 as b10_30NTDB10_01,byf01.B01_04 as b10_30NTDB01_04

from byf10

left join byf01

on byf10.B10_30+'NTD'=byf01.B01_02+byf01.B01_03

where byf01.B01_01<=byf10.b10_02 and byf10.b10_02<=byf01.B01_011

/* 查找出付款幣別轉臺幣匯率*/

create view byf10_b10_40NTDview as

select DISTINCT byf10.B10_01 as b10_40NTDB10_01,byf01.B01_04 as b10_40NTDB01_04

from byf10

left join byf01

on byf10.B10_40+'NTD'=byf01.B01_02+byf01.B01_03

where byf01.B01_01<=byf10.b10_02 and byf10.b10_02<=byf01.B01_011

/* 將所有查找結果合并成一張表*/

select byf10_b10_30b10_40view.b10_30b10_40B10_01,byf10_b10_30b10_40view.B10_09,byf10_b10_30b10_40view.B10_17,byf10_b10_30b10_40view.B10_30,byf10_b10_30b10_40view.B10_31,byf10_b10_30b10_40view.B10_40,byf10_b10_30b10_40view.B10_43,byf10_b10_30b10_40view.B10_46,byf10_b10_30b10_40view.b10_30b10_40B01_05,byf10_b10_30NTDview.b10_30NTDB01_04, byf10_b10_40NTDview.b10_40NTDB01_04

from byf10_b10_30b10_40view

left join byf10_b10_30NTDview

on byf10_b10_30b10_40view.b10_30b10_40B10_01=byf10_b10_30NTDview.b10_30NTDB10_01

left join byf10_b10_40NTDview

on byf10_b10_30b10_40view.b10_30b10_40B10_01=byf10_b10_40NTDview.b10_40NTDB10_01

drop view byf10_b10_30NTDview

drop view byf10_b10_40NTDview

drop view byf10_b10_30b10_40view

第二種方法：

select a.b10_30b10_40B10_01,a.B10_09,a.B10_17,a.B10_30,a.B10_31,a.B10_40,a.B10_43,a.B10_46,a.b10_30b10_40B01_05,b.b10_30NTDB01_04, c.b10_40NTDB01_04

from

(select DISTINCT byf10.B10_01 as b10_30b10_40B10_01,byf01.B01_05 as b10_30b10_40B01_05,byf10.B10_09,byf10.B10_17,byf10.B10_30,byf10.B10_31,byf10.B10_40,byf10.B10_43,byf10.B10_46

from byf10

left join byf01

on byf10.B10_30+byf10.B10_40=byf01.B01_02+byf01.B01_03

where byf01.B01_01<=byf10.b10_02 and byf10.b10_02<=byf01.B01_011)

as a

left join

(select DISTINCT byf10.B10_01 as b10_30NTDB10_01,byf01.B01_04 as b10_30NTDB01_04

from byf10

left join byf01

on byf10.B10_30+'NTD'=byf01.B01_02+byf01.B01_03

where byf01.B01_01<=byf10.b10_02 and byf10.b10_02<=byf01.B01_011)

as b

on a.b10_30b10_40B10_01=b.b10_30NTDB10_01

left join

(select DISTINCT byf10.B10_01 as b10_40NTDB10_01,byf01.B01_04 as b10_40NTDB01_04

from byf10

left join byf01

on byf10.B10_40+'NTD'=byf01.B01_02+byf01.B01_03

where byf01.B01_01<=byf10.b10_02 and byf10.b10_02<=byf01.B01_011)

as c

on a.b10_30b10_40B10_01=c.b10_40NTDB10_01

創建表：

CREATE TABLE [byf01] (

[B01_01] [datetime] NULL ,

[B01_011] [datetime] NULL ,

[B01_02] [nvarchar] (3) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,

[B01_03] [nvarchar] (3) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,

[B01_04] [decimal](6, 4) NULL ,

[bName] [char] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,

[B01_06] [bit] NULL ,

[bdate] [datetime] NULL ,

[B01_07] [decimal](18, 4) NULL ,

[B01_08] [decimal](18, 4) NULL ,

[ID] [int] IDENTITY (1, 1) NOT NULL ,

[B01_Span] [decimal](6, 4) NULL ,

[B01_05] [decimal](6, 4) NULL

) ON [PRIMARY]

CREATE TABLE [byf10] (

[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,

[B10_01] [nvarchar] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,

[B10_011] [nvarchar] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,

[B10_02] [datetime] NULL ,

[B10_03] [nvarchar] (1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,

[B10_04] [nvarchar] (7) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,

[B10_05] [nvarchar] (40) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,

[B10_06] [nvarchar] (25) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,

[B10_07] [nvarchar] (16) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,

[B10_08] [nvarchar] (30) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,

[B10_09] [nvarchar] (4) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,

[B10_10] [nvarchar] (2) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,

[B10_11] [bit] NULL ,

[B10_12] [nvarchar] (7) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,

[B10_13] [nvarchar] (40) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,

[B10_14] [nvarchar] (25) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,

[B10_15] [nvarchar] (16) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,

[B10_16] [nvarchar] (30) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,

[B10_17] [nvarchar] (4) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,

[B10_18] [nvarchar] (2) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,

[B10_19] [bit] NULL ,

[B10_21] [numeric](8, 2) NULL ,

[B10_22] [numeric](8, 4) NULL ,

[B10_23] [numeric](8, 4) NULL ,

[B10_30] [nvarchar] (3) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,

[B10_31] [numeric](8, 2) NULL ,

[B10_32] [nvarchar] (1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,

[B10_33] [decimal](8, 2) NULL ,

[B10_34] [decimal](8, 2) NULL ,

[B10_35] [decimal](8, 2) NULL ,

[B10_36] [decimal](8, 2) NULL ,

[B10_37] [decimal](8, 2) NULL ,

[B10_40] [nvarchar] (3) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,

[B10_41] [decimal](8, 0) NULL ,

[B10_42] [nvarchar] (1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,

[B10_43] [decimal](8, 2) NULL ,

[B10_45] [decimal](8, 2) NULL ,

[B10_46] [decimal](8, 2) NULL ,

[B10_47] [decimal](8, 2) NULL ,

[del] [bit] NULL ,

[comID] [nvarchar] (4) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,

[bName] [nvarchar] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,

[bdate] [datetime] NULL ,

[B10_48] [decimal](6, 4) NULL ,

[B10_49] [decimal](6, 4) NULL ,

[B10_50] [decimal](6, 4) NULL

) ON [PRIMARY]

法律问题免费作答

posted on 2008-02-20 13:23 破曉之陽阅读(68) 评论(2) 编辑收藏所属分类: sql

#1楼 [楼主] 2008-02-20 17:29 破曉之陽

select DISTINCT byf10.B10_01,byf10.B10_09,byf10.B10_17,byf10.B10_36,byf10.B10_41,byf10.B10_46,a.B01_05,b.B01_05,b.B01_05
from byf10
left join (select B01_01,B01_011,B01_02,B01_03,B01_05,B01_06 from byf01) as a
on byf10.B10_30=B01_02 and byf10.B10_40=a.B01_03 and (byf10.B10_02 between a.B01_01 and a.B01_011 ) and a.B01_06='1'
left join (select B01_01,B01_011,B01_02,B01_03,B01_05,B01_06 from byf01) as b
on byf10.B10_30=b.B01_02 and a.B01_03='NTD' and (byf10.B10_02 between b.B01_01 and b.B01_011 ) and b.B01_06='1'
left join (select B01_01,B01_011,B01_02,B01_03,B01_05,B01_06 from byf01) as c
on byf10.B10_40=a.B01_02 and a.B01_03='NTD' and (byf10.B10_02 between c.B01_01 and c.B01_011 ) and c.B01_06='1'
order by byf10.B10_01 回复引用查看

#2楼 [楼主] 2008-02-21 08:44 破曉之陽

select DISTINCT byf10.B10_01,byf10.B10_03,byf10.B10_09,byf10.B10_17,byf10.B10_36,byf10.B10_41,byf10.B10_46,a.B01_05 as B10_30B10_40,b.B01_05 as B10_30NTD,c.B01_05 as B10_40NTD
from byf10
left join (select B01_01,B01_011,B01_02,B01_03,B01_05,B01_06 from byf01) as a
on byf10.B10_30=B01_02 and byf10.B10_40=a.B01_03 and (byf10.B10_02 between a.B01_01 and a.B01_011 ) and a.B01_06='1'
left join (select B01_01,B01_011,B01_02,B01_03,B01_05,B01_06 from byf01) as b
on byf10.B10_30=b.B01_02 and a.B01_03='NTD' and (byf10.B10_02 between b.B01_01 and b.B01_011 ) and b.B01_06='1'
left join (select B01_01,B01_011,B01_02,B01_03,B01_05,B01_06 from byf01) as c
on byf10.B10_40=a.B01_02 and a.B01_03='NTD' and (byf10.B10_02 between c.B01_01 and c.B01_011 ) and c.B01_06='1'
order by byf10.B10_01 回复引用查看