sql - 合并两个 View

Question

我有两个 View A 和 B 都具有相同的列名称 StudentID, FeeToBePaid, AmountPaid .在两个 View 中 StudentID是一样的。但栏目FeeToBePaid和 AmountPaid是不同的。

我想用 A 中的行替换 B 中的行 if A.FeeToBePaid <= A.AmountPaid同 studentID .

View A

SchoolStudentID     sFeeToBePaid     amountpaid
12-S-3003             1000.00         1000.00   

View B

SchoolStudentID   sFeeToBePaid   amountpaid
  12-S-3003         1000.00        0.00 
  11-F-2019         NULL           1000.00
  12-S-3009         NULL           1000.00

我需要答案
SchoolStudentID sFeeToBePaid amountpaid

  12-S-3003         1000.00        1000.00
  11-F-2019         NULL           1000.00
  12-S-3009         NULL           1000.00

Answer 1

结合两个LEFT JOIN的结果试试吧使用 UNION没有 ALL 来消除重复记录。

SELECT a.SchoolStudentID , 
       CASE WHEN a.sFeeToBePaid  <= a.amountpaid 
            THEN a.sFeeToBePaid 
            ELSE b. sFeeToBePaid 
       END AS sFeeToBePaid,
       CASE WHEN a.sFeeToBePaid  <= a.amountpaid 
            THEN a.amountpaid
            ELSE b.amountpaid 
       END AS amountpaid 
FROM   ViewA a
       LEFT JOIN ViewB b
        ON a.SchoolStudentID  = b.SchoolStudentID 
UNION
SELECT a.SchoolStudentID , 
       CASE WHEN b.sFeeToBePaid  <= b.amountpaid 
            THEN b.sFeeToBePaid 
            ELSE a. sFeeToBePaid 
       END AS sFeeToBePaid,
       CASE WHEN b.sFeeToBePaid  <= b.amountpaid 
            THEN b.amountpaid
            ELSE a.amountpaid 
       END AS amountpaid 
FROM   ViewB a
       LEFT JOIN ViewA b
        ON a.SchoolStudentID  = b.SchoolStudentID 

SQLFiddle Demo