Mam 3 stoły w następujący sposób. Chcę znaleźć równowagę

Table A
studentID       Name
1               Bob
2               Sam
3              Sara

Table B
id  studentID   Credit
1   1       100
2   1       150
3   2       150
4   2       150
5   3       100
6   3       200

Table C
id  studentID   Amount      Type
1   1             50        cash
2   1            120        card
3   2            100        cash
4   2            130        card
5   3            50         card
6   3            150        card

Chcę uzyskać stół wyników w następujący sposób, gdzie saldo = suma (kredyt) - suma (kwota), gdzie typ = karta.

Stół wyników

studentID       Name    Credit  Amount  Balance
1               Bob     250      120    130
2               Sam     300      130    170
3               Sara    300      200    100

Edytuj
. Według jego komentarza jest to zapytanie, które próbował

select A.studentID,
       A.Name,
       ISNULL(SUM(B.Credit),0) as [Credit], 
       ISNULL(SUM(C.Amount),0) as [Amount], 
       ISNULL(SUM(B.Credit),0) - (select ISNULL(SUM(C.Amount),0) from C Group by C.studentID having C.Type='card' and C.studentID=A.studentID) as [balance] 
from A 
  left outer join B on A.studentID = B.studentID 
  left outer join C on B.studentID = C.studentID 
group by A.studentID 
0
Vahid 5 czerwiec 2018, 12:08

5 odpowiedzi

Najlepsza odpowiedź

Obsługuje uczniów bez kredytu / kwot:

SELECT  A.STUDENTID, A.[NAME]
    , sum(B.CREDIT) AS [CREDIT]
    , sum(C.AMOUNT) AS [AMOUNT]
    , sum(B.CREDIT) - sum(C1.AMOUNT) AS [BALANCE]
FROM @TABLEA A
INNER JOIN (SELECT A.STUDENTID, isnull(SUM(B.CREDIT), 0) AS CREDIT FROM @TABLEA A LEFT JOIN @TABLEB B ON A.STUDENTID = B.STUDENTID GROUP BY A.STUDENTID) B ON A.STUDENTID = B.STUDENTID
INNER JOIN (SELECT A.STUDENTID, isnull(SUM(C.AMOUNT), 0) AS AMOUNT FROM @TABLEA A LEFT JOIN @TABLEC C ON A.STUDENTID = C.STUDENTID WHERE [TYPE] = 'CARD' GROUP BY A.STUDENTID) C ON B.STUDENTID = C.STUDENTID
INNER JOIN (SELECT A.STUDENTID, isnull(SUM(C.AMOUNT), 0) AS AMOUNT FROM @TABLEA A LEFT JOIN @TABLEC C ON A.STUDENTID = C.STUDENTID WHERE [TYPE] = 'CARD' GROUP BY A.STUDENTID) C1 ON B.STUDENTID = C1.STUDENTID
group by  A.STUDENTID, A.[NAME]
2
cloudsafe 5 czerwiec 2018, 10:11

To powinno działać dobrze

SELECT
a.studentid,
a.Name,
sum(b.Credit) AS Credit,
ISNULL(c.Amount,0) AS Amount,
sum(b.Credit) - ISNULL(c.Amount,0) AS Balance
FROM  a
INNER JOIN  b ON a.studentid = b.StudentID
LEFT JOIN (select sum(amount) amount,studentid from c where type='card' 
group by studentid) as c ON a.studentid = c.studentid
GROUP BY a.studentid, a.Name,c.Amount

SQL Fiddle Link

1
Be1ng_Kr1Sh 5 czerwiec 2018, 09:42

Nie próbowałem tego. Proszę daj mi znać.

SELECT A.studentID,A.Name,B.Credit,C.Amount,(Credit-Balance) as Amount from A, B, C where A.studentID=B.studentID=C.studentID AND B.id=C.id AND C.type="card";
0
cyperpunk 5 czerwiec 2018, 09:20

To powinno działać. Zakłada, że wszyscy uczniowie w A są w B. Lewo łączy się za fakt, że nie wszystkie mogą mieć kwotę "karty" i zwraca 0, jeśli nie (w przeciwnym razie kredyt odejmowany przez NULL powróci NULL bilans).

   SELECT
    a.StudentID,
    a.Name,
    sum(b.Credit) AS Credit,
    ISNULL(sum(c.Amount),0) AS Amount,
    sum(b.Credit) - ISNULL(sum(c.Amount),0) AS Balance
    FROM tableA as a
    INNER JOIN tableB as b ON a.StudentID = b.StudentID
    LEFT JOIN tableC as c ON a.StudientID = c.StudentID AND c.[Type] = 'card'
    GROUP BY a.StudentID, a.Name
0
TJB 5 czerwiec 2018, 09:23

To byłby mój punkt początkowy. Aby mieć pewność co do prawego Calcs. Potem zacznę refakcjonować.

 SELECT a.*, 
   (SELECT SUM(b.credit) FROM TableB b WHERE a.studentID = b.studentID) as Credit,
   (SELECT SUM(c.amount) FROM TableC c WHERE a.studentID = c.studentID) as Amount,
   ((SELECT SUM(b.credit) FROM TableB b WHERE a.studentID = b.studentID)
    - 
   (SELECT SUM(c.amount) FROM TableC c WHERE a.studentID = c.studentID)) as Balance
  FROM TableA a;

Zrobiłem sqlfiddle: http://sqlfiddle.com/#!9/b86dc/7< A >.

0
calm 5 czerwiec 2018, 09:27