表A
create table A(A_ID int primary key,much_money float(8), units varchar(50))
create table B(B_ID int primary key, much_mo float(8),units varchar(50))
create table C(C_ID int primary key, unit_From varchar(50),units_To varchar(50),conversion float(8))insert into A (A_ID,much_money,units) values(1,7,'RMB')
insert into A (A_ID,much_money,units) values(2,8,'$$')insert into B (B_ID,much_mo,units) values(1,9,'$$')
insert into B (B_ID,much_mo,units) values(2,10,'RMB')insert into C (C_ID,unit_From,units_To) values(1,'$$','RMB',8)
insert into C (C_ID,unit_From,units_To) values(2,'RMB','$$',0.125)--A,B 通过主键关联
--把对表A和B,查询
--单位若不同则通过查C表转换,以该产品第一条记录的单位为基准转换
--得到如下效果
--1 79 RMB--2 8.8 $$
!!!!!!!!!用游标怎么实现!!!!!!!!!!
create table A(A_ID int primary key,much_money float(8), units varchar(50))
create table B(B_ID int primary key, much_mo float(8),units varchar(50))
create table C(C_ID int primary key, unit_From varchar(50),units_To varchar(50),conversion float(8))insert into A (A_ID,much_money,units) values(1,7,'RMB')
insert into A (A_ID,much_money,units) values(2,8,'$$')insert into B (B_ID,much_mo,units) values(1,9,'$$')
insert into B (B_ID,much_mo,units) values(2,10,'RMB')insert into C (C_ID,unit_From,units_To) values(1,'$$','RMB',8)
insert into C (C_ID,unit_From,units_To) values(2,'RMB','$$',0.125)--A,B 通过主键关联
--把对表A和B,查询
--单位若不同则通过查C表转换,以该产品第一条记录的单位为基准转换
--得到如下效果
--1 79 RMB--2 8.8 $$
!!!!!!!!!用游标怎么实现!!!!!!!!!!
insert into C (C_ID,unit_From,units_To) values(2,'RMB','$$',0.125)--以上兩句錯了, 前面是三個字段, 後面給出了四個值
--應改為
insert into C (C_ID,unit_From,units_To,conversion) values(1,'$$','RMB',8)
insert into C (C_ID,unit_From,units_To,conversion) values(2,'RMB','$$',0.125)
恩但该怎么做呢
------------
改過來了
DECLARE @B TABLE(B_ID INT PRIMARY KEY, MUCH_MO FLOAT(8),UNITS VARCHAR(50))
DECLARE @C TABLE(C_ID INT PRIMARY KEY, UNIT_FROM VARCHAR(50),UNITS_TO VARCHAR(50),CONVERSION FLOAT(8))DECLARE @RESULT TABLE([ID] INT PRIMARY KEY,MUCH_MONEY FLOAT(8), UNITS VARCHAR(50))DECLARE @ID INT,
@MONEY FLOAT,
@UNITS VARCHAR(10),
@CHANGE INTINSERT INTO @A
SELECT 1,7,'RMB' UNION
SELECT 2,8,'$$'INSERT INTO @B
SELECT 1,9,'$$' UNION
SELECT 2,10,'RMB'INSERT INTO @C
SELECT 1,'$$','RMB',8 UNION
SELECT 2,'RMB','$$',0.125--得到汇率
SELECT @CHANGE=CONVERSION FROM @C WHERE C_ID=1
PRINT CONVERT(CHAR(100),ISNULL(@CHANGE,0))DECLARE C CURSOR FOR
SELECT A_ID,MUCH_MONEY,UNITS FROM @A OPEN CFETCH NEXT FROM C
INTO @ID,@MONEY,@UNITSWHILE @@FETCH_STATUS=0
BEGIN
SELECT @MONEY=@MONEY+(SELECT MUCH_MO*CASE
WHEN UNITS=@UNITS THEN 1
WHEN UNITS<>@UNITS AND UNITS='RMB' THEN 1.0000/@CHANGE
ELSE
8
END
FROM @B WHERE B_ID=@ID) INSERT INTO @RESULT([ID],MUCH_MONEY,UNITS)VALUES(@ID,@MONEY,@UNITS) FETCH NEXT FROM C
INTO @ID,@MONEY,@UNITS
ENDCLOSE C
DEALLOCATE CSELECT * FROM @RESULT
from A a join B b on a.A_ID=b.B_ID join C c on b.units=c.unit_From