SQL

Question

最終残高を求めるSQLを作成したいです。
もっとスッキリさせるにはどのようにすればよいでしょうか。

SELECT CUSTOMERS.NAME AS 名前

,CASE CUSTOMERS.SEX
 WHEN '1' THEN '男'
 WHEN '0' THEN '女'
 END AS 性

,YOKIN.ZAN AS 残高

FROM YOKIN INNER JOIN CUSTOMERS
ON YOKIN.ID = CUSTOMERS.ID

WHERE (CUSTOMERS.NAME, CUSTOMERS.SEX, YOKIN.DAY)
IN (SELECT CUSTOMERS.NAME, CUSTOMERS.SEX, MAX(YOKIN.DAY)
FROM YOKIN WHERE YOKIN.ID = 001 OR YOKIN.ID = 002 GROUP BY YOKIN.ID)

-- 預金テーブル
CREATE TABLE YOKIN (
ID CHAR(3) NOT NULL, -- 顧客ID
DAY CHAR(8) NOT NULL, -- 日付
ZAN NUMBER(10) NOT NULL, -- 残高

PRIMARY KEY (ID, DAY)

);

-- 顧客テーブル
CREATE TABLE CUSTOMERS (
ID CHAR(3) NOT NULL, -- 顧客ID
NAME VARCHAR2(20) NOT NULL, -- 顧客名
SEX CHAR(1) NOT NULL -- 性別(0:女、1:男)
);

YOKIN
ID DAY ZAN

001 20090101 10000
001 20090102 50
001 20090125 10050
001 20090126 5600
001 20090222 30
001 20090225 16030
001 20090226 3000
002 20090101 10000
002 20090125 18000
002 20090225 30000

CUSTOMERS
ID NAME SEX

001 OTOKO 1
002 ONNA 0

INSERT INTO YOKIN VALUES ('001', '20090101', 10000);
INSERT INTO YOKIN VALUES ('001', '20090102', 50);
INSERT INTO YOKIN VALUES ('001', '20090125', 10050);
INSERT INTO YOKIN VALUES ('001', '20090126', 5600);
INSERT INTO YOKIN VALUES ('001', '20090222', 30);
INSERT INTO YOKIN VALUES ('001', '20090225', 16030);
INSERT INTO YOKIN VALUES ('001', '20090226', 3000);
INSERT INTO YOKIN VALUES ('002', '20090101', 10000);
INSERT INTO YOKIN VALUES ('002', '20090125', 18000);
INSERT INTO YOKIN VALUES ('002', '20090225', 30000);

INSERT INTO CUSTOMERSVALUES ('001', 'OTOKO', '1');
INSERT INTO CUSTOMERS VALUES ('002', 'ONNA', '0');

Answer 1

すでに解決済みのようですが、ご参考までに分析関数を使った例を挙げます。
こちらもOracleです。

SELECT C.NAME 名前, 
CASE C.SEX 
  WHEN '1' THEN '男' 
  WHEN '0' THEN '女' 
END AS 性,
ZAN AS 残高
FROM (SELECT ID, ZAN, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DAY DESC) ROW# FROM YOKIN) Y
INNER JOIN CUSTOMERS C ON Y.ID = C.ID AND Y.ROW# = 1
ORDER BY Y.ID

Answer 2

こんにちは。
最終残高って直近の残高ってことでいいですよね？
ご提示頂いているSQLと結果が違うのが気になりますが、
こちらでいかがでしょうか？
Oracleです。

SELECT C.NAME 名前, 
CASE C.SEX 
  WHEN '1' THEN '男' 
  WHEN '0' THEN '女' 
END AS 性,
ZAN AS 残高
FROM YOKIN Y 
INNER JOIN (SELECT ID, TO_CHAR(MAX(TO_DATE(DAY, 'YYYYMMDD')), 'YYYYMMDD') DAY FROM YOKIN GROUP BY ID) T ON Y.ID = T.ID AND Y.DAY = T.DAY
INNER JOIN CUSTOMERS C ON Y.ID = C.ID
ORDER BY Y.ID