1.写一段代码实现如下功能:
创建两张表TABLE_A和TABLE_B,TABLE_A含两列,一列为fid(varchar,非空),另外一列为ftype(varchar,80),TABLE_B也含两列,一列为fid(varchar,非空),一列为fname(varchar,80),用代码逐条插入如下表内容:
表table_a:
fid ftype
1001.01 a
1001.02 b
1001.03 c
1001.04 d
102.01 e
102.02 f
102.03 g
102.04 h
表table_b:
fid fname
1001.01 aa
1001.02 bb
1001.03 cc
1001.04 dd
102.01 ee
102.02 ff
102.03 gg
102.04 hh
写一条查询语句,以fid为关联,但是使表table_a的fid为*.02时自动关联表table_b的fid为*.03,表table_a的fid为*.03时自动关联表table_b的fid为*.02,并取出两表所有的列。结果:
fid fid ftype fname
1001.01 1001.01 a aa
1001.03 1001.02 c bb
1001.02 1001.03 b cc
1001.04 1001.04 d dd
102.01 102.01 e ee
102.03 102.02 g ff
102.02 102.03 f gg
102.04 102.04 h hh
创建两张表TABLE_A和TABLE_B,TABLE_A含两列,一列为fid(varchar,非空),另外一列为ftype(varchar,80),TABLE_B也含两列,一列为fid(varchar,非空),一列为fname(varchar,80),用代码逐条插入如下表内容:
表table_a:
fid ftype
1001.01 a
1001.02 b
1001.03 c
1001.04 d
102.01 e
102.02 f
102.03 g
102.04 h
表table_b:
fid fname
1001.01 aa
1001.02 bb
1001.03 cc
1001.04 dd
102.01 ee
102.02 ff
102.03 gg
102.04 hh
写一条查询语句,以fid为关联,但是使表table_a的fid为*.02时自动关联表table_b的fid为*.03,表table_a的fid为*.03时自动关联表table_b的fid为*.02,并取出两表所有的列。结果:
fid fid ftype fname
1001.01 1001.01 a aa
1001.03 1001.02 c bb
1001.02 1001.03 b cc
1001.04 1001.04 d dd
102.01 102.01 e ee
102.03 102.02 g ff
102.02 102.03 f gg
102.04 102.04 h hh
解决方案 »
- 存储过程问题-------------------------------------在线求解
- 二进制文档中保存在数据库中好一点,还是只放路径好一点
- 有关单号生成的问题请教。
- 如何中斷SQL程序?
- UPDATE语句,sql 语句高手请进
- 怎样实现数据库动态自动生成!!!!!!!!!!!!!
- 如何在一个视图中添加一个自动增长的编号??
- jsp(jdbc) + sql server 7.0的存储过程,是不是可以像oracle 中的 type -> table返回结果集?
- DELPHI的ADO连接SQL SERVER的问题。
- 同样一段SQL,在网页中执行的时候出错,但在查询分析器里面不出错,为什么?
- 安装MSDE2000
- 数据库问题
fid fid ftype fname
1001.01 1001.01 a aa
1001.03 1001.02 c bb
1001.02 1001.03 b cc
1001.04 1001.04 d dd
102.01 102.01 e ee
102.03 102.02 g ff
102.02 102.03 f gg
102.04 102.04 h hh
DECLARE @table_a TABLE (fid VARCHAR(8),ftype VARCHAR(2))
DECLARE @table_b TABLE (fid VARCHAR(8),fname VARCHAR(2))INSERT INTO @table_a(fid, ftype)
SELECT '1001.01','a' UNION ALL
SELECT '1001.02','b' UNION ALL
SELECT '1001.03','c' UNION ALL
SELECT '1001.04','d' UNION ALL
SELECT '102.01','e' UNION ALL
SELECT '102.02','f' UNION ALL
SELECT '102.03','g' UNION ALL
SELECT '102.04','h'INSERT INTO @table_b(fid, fname)
SELECT '1001.01','aa' UNION ALL
SELECT '1001.02','bb' UNION ALL
SELECT '1001.03','cc' UNION ALL
SELECT '1001.04','dd' UNION ALL
SELECT '102.01','ee' UNION ALL
SELECT '102.02','ff' UNION ALL
SELECT '102.03','gg' UNION ALL
SELECT '102.04','hh'SELECT a.fid,b.fid,a.ftype,b.fname FROM
@table_a a JOIN @table_b b
ON
CASE
WHEN CHARINDEX('.02',a.fid) > 0 THEN REPLACE(a.fid,'.02','.03')
WHEN CHARINDEX('.03',a.fid) > 0 THEN REPLACE(a.fid,'.03','.02')
ELSE a.fid
END = b.fidfid fid ftype fname
-------- -------- ----- -----
1001.01 1001.01 a aa
1001.02 1001.03 b cc
1001.03 1001.02 c bb
1001.04 1001.04 d dd
102.01 102.01 e ee
102.02 102.03 f gg
102.03 102.02 g ff
102.04 102.04 h hh(8 行受影响)