select b.pk_deptdoc,
a.year,
b.costbasis,
b.expenseratio,
(select vysname from tru_bdgzt_b where pk_bdgzt_b = b.pk_deptdoc) doaname
from tru_budget_deptfee a
inner join tru_budget_deptfee_b b on a.pk_tru_budget_deptfee =
b.pk_tru_budget_deptfee
where (nvl(a.dr, 0) = 0)
and (nvl(b.dr, 0) = 0)
and a.year = '2009'
and a.query = (select pk_defdoc from bd_defdoc where docname = '差旅费')
select b.pk_deptdoc,
a.year,
b.costbasis,
b.expenseratio,
(select vysname from tru_bdgzt_b where pk_bdgzt_b = b.pk_deptdoc) doaname
from tru_budget_deptfee a
inner join tru_budget_deptfee_b b on a.pk_tru_budget_deptfee =
b.pk_tru_budget_deptfee
where (nvl(a.dr, 0) = 0)
and (nvl(b.dr, 0) = 0)
and a.year = '2009'
and a.query = (select pk_defdoc from bd_defdoc where docname = '招待费')
请高手帮我将这两张表,合为一个视图。
谢谢
a.year,
b.costbasis,
b.expenseratio,
(select vysname from tru_bdgzt_b where pk_bdgzt_b = b.pk_deptdoc) doaname
from tru_budget_deptfee a
inner join tru_budget_deptfee_b b on a.pk_tru_budget_deptfee =
b.pk_tru_budget_deptfee
where (nvl(a.dr, 0) = 0)
and (nvl(b.dr, 0) = 0)
and a.year = '2009'
and a.query = (select pk_defdoc from bd_defdoc where docname = '差旅费')
select b.pk_deptdoc,
a.year,
b.costbasis,
b.expenseratio,
(select vysname from tru_bdgzt_b where pk_bdgzt_b = b.pk_deptdoc) doaname
from tru_budget_deptfee a
inner join tru_budget_deptfee_b b on a.pk_tru_budget_deptfee =
b.pk_tru_budget_deptfee
where (nvl(a.dr, 0) = 0)
and (nvl(b.dr, 0) = 0)
and a.year = '2009'
and a.query = (select pk_defdoc from bd_defdoc where docname = '招待费')
请高手帮我将这两张表,合为一个视图。
谢谢
第一种:
SELECT b.pk_deptdoc, a.YEAR, b.costbasis, b.expenseratio,
(SELECT vysname
FROM tru_bdgzt_b
WHERE pk_bdgzt_b = b.pk_deptdoc) doaname
FROM tru_budget_deptfee a INNER JOIN tru_budget_deptfee_b b
ON a.pk_tru_budget_deptfee = b.pk_tru_budget_deptfee
WHERE (NVL (a.dr, 0) = 0)
AND (NVL (b.dr, 0) = 0)
AND a.YEAR = '2009'
AND a.QUERY = (SELECT pk_defdoc
FROM bd_defdoc
WHERE (docname = '差旅费' OR docname = '差旅费'))第二种:
SELECT b.pk_deptdoc, a.YEAR, b.costbasis, b.expenseratio,
(SELECT vysname
FROM tru_bdgzt_b
WHERE pk_bdgzt_b = b.pk_deptdoc) doaname
FROM tru_budget_deptfee a INNER JOIN tru_budget_deptfee_b b
ON a.pk_tru_budget_deptfee = b.pk_tru_budget_deptfee
WHERE (NVL (a.dr, 0) = 0)
AND (NVL (b.dr, 0) = 0)
AND a.YEAR = '2009'
AND a.QUERY = (SELECT pk_defdoc
FROM bd_defdoc
WHERE docname = '差旅费')
UNION ALL
SELECT b.pk_deptdoc, a.YEAR, b.costbasis, b.expenseratio,
(SELECT vysname
FROM tru_bdgzt_b
WHERE pk_bdgzt_b = b.pk_deptdoc) doaname
FROM tru_budget_deptfee a INNER JOIN tru_budget_deptfee_b b
ON a.pk_tru_budget_deptfee = b.pk_tru_budget_deptfee
WHERE (NVL (a.dr, 0) = 0)
AND (NVL (b.dr, 0) = 0)
AND a.YEAR = '2009'
AND a.QUERY = (SELECT pk_defdoc
FROM bd_defdoc
WHERE docname = '招待费')