fork download
copy 
  1. CREATE TABLE t1 (
  2. key TEXT PRIMARY KEY NOT NULL,
  3. name TEXT NOT NULL UNIQUE
  4. );
  5. INSERT INTO t1 (key, name) VALUES ('t1_key1', 't1_name1');
  6. INSERT INTO t1 (key, name) VALUES ('t1_key2', 't1_name2');
  7.  
  8. CREATE TABLE t2 (
  9. key TEXT PRIMARY KEY NOT NULL,
  10. name TEXT NOT NULL UNIQUE
  11. );
  12. INSERT INTO t2 (key, name) VALUES ('t2_key1', 't2_name1');
  13. INSERT INTO t2 (key, name) VALUES ('t2_key2', 't2_name2');
  14.  
  15. CREATE TABLE t3 (
  16. key TEXT PRIMARY KEY NOT NULL,
  17. name TEXT NOT NULL UNIQUE
  18. );
  19. INSERT INTO t3 (key, name) VALUES ('t3_key1', 't3_name1');
  20. INSERT INTO t3 (key, name) VALUES ('t3_key2', 't3_name2');
  21.  
  22. CREATE TABLE t4 (
  23. year TEXT NOT NULL,
  24. t1_key TEXT NOT NULL,
  25. t3_key TEXT NOT NULL,
  26. percent INTEGER NOT NULL,
  27. UNIQUE(year, t1_key, t3_key),
  28. FOREIGN KEY(t1_key) REFERENCES t1(key) ON DELETE CASCADE,
  29. FOREIGN KEY(t3_key) REFERENCES t3(key) ON DELETE CASCADE
  30. );
  31. /*
  32. INSERT INTO t4 (year, t1_key, t3_key, percent) VALUES ('2011', 't1_key1', 't3_key1', 10);
  33. INSERT INTO t4 (year, t1_key, t3_key, percent) VALUES ('2012', 't1_key2', 't3_key2', 50);
  34. INSERT INTO t4 (year, t1_key, t3_key, percent) VALUES ('2012', 't1_key2', 't3_key1', 100);
  35. */
  36. --テーブル「t4」
  37. INSERT INTO t4 (year, t1_key, t3_key, percent) VALUES ('2011', 't1_key1', 't3_key1', 10);-- テーブル「t5」の'2011-01-01'の行がpercent「10」を利用する
  38. INSERT INTO t4 (year, t1_key, t3_key, percent) VALUES ('2011', 't1_key1', 't3_key2', 100);-- テーブル「t5」の'2011-02-02'と'2011-02-02'の２つの行がpercent「100」を利用する
  39. INSERT INTO t4 (year, t1_key, t3_key, percent) VALUES ('2011', 't1_key2', 't3_key1', 30);
  40. INSERT INTO t4 (year, t1_key, t3_key, percent) VALUES ('2012', 't1_key2', 't3_key2', 50);
  41. INSERT INTO t4 (year, t1_key, t3_key, percent) VALUES ('2012', 't1_key2', 't3_key1', 100);
  42.  
  43. CREATE TABLE t5 (
  44. no INTEGER PRIMARY KEY AUTOINCREMENT, 
  45. date TEXT NOT NULL,
  46. t2_key TEXT NOT NULL,
  47. t3_key TEXT NOT NULL,
  48. amount INTEGER NOT NULL,
  49. FOREIGN KEY(t2_key) REFERENCES t2(key) ON DELETE CASCADE,
  50. FOREIGN KEY(t3_key) REFERENCES t3(key) ON DELETE CASCADE
  51. );
  52. /*
  53. INSERT INTO t5 (date, t2_key, t3_key, amount) VALUES ('2011-01-01', 't2_key1', 't3_key1', 3000);
  54. INSERT INTO t5 (date, t2_key, t3_key, amount) VALUES ('2011-02-02', 't2_key1', 't3_key2', 2000);
  55. INSERT INTO t5 (date, t2_key, t3_key, amount) VALUES ('2012-01-01', 't2_key1', 't3_key1', 5000);
  56. */
  57. --テーブル「t5」
  58. INSERT INTO t5 (date, t2_key, t3_key, amount) VALUES ('2011-01-01', 't2_key1', 't3_key1', 3000);-- '2011'と't2_key1'でとりあえず条件を満たす
  59. /*INSERT INTO t5 (date, t2_key, t3_key, amount) VALUES ('2011-02-02', 't2_key1', 't3_key1', 2000);*/
  60. INSERT INTO t5 (date, t2_key, t3_key, amount) VALUES ('2011-02-02', 't2_key1', 't3_key2', 2000);-- '2011'と't2_key1'でとりあえず条件を満たす
  61. INSERT INTO t5 (date, t2_key, t3_key, amount) VALUES ('2011-03-03', 't2_key2', 't3_key2', 1000);
  62. INSERT INTO t5 (date, t2_key, t3_key, amount) VALUES ('2011-04-04', 't2_key1', 't3_key2', 10000);-- '2011'と't2_key1'でとりあえず条件を満たす
  63. INSERT INTO t5 (date, t2_key, t3_key, amount) VALUES ('2012-01-01', 't2_key1', 't3_key1', 5000);
Success #stdin #stdout 0s 2964KB
comments (?)
stdin
copy 
select '-- t1 --';
select * from t1;

select '-- t2 --';
select * from t2;

select '-- t3 --';
select * from t3;

select '-- t4 --';
select * from t4;

select '-- t5--';
select * from t5;

select '-- 結果 --';
select sum(t5.amount * (t4.percent / 100.0)) result from 
(
 (
  select strftime('%Y', date) year, t2_key, t3_key, amount 
  from t5
  where strftime('%Y', date) = '2011'
 ) t5
 inner join t2 on (t2.key = t5.t2_key and t2.name = 't2_name1')
 inner join
  (
   select t4.* from t4 
   inner join t1 on (t4.t1_key = t1.key and t1.name = 't1_name1')
  ) t4
 on (t5.t3_key = t4.t3_key and t5.year = t4.year)
);
stdout
copy 
-- t1 --
t1_key1|t1_name1
t1_key2|t1_name2
-- t2 --
t2_key1|t2_name1
t2_key2|t2_name2
-- t3 --
t3_key1|t3_name1
t3_key2|t3_name2
-- t4 --
2011|t1_key1|t3_key1|10
2011|t1_key1|t3_key2|100
2011|t1_key2|t3_key1|30
2012|t1_key2|t3_key2|50
2012|t1_key2|t3_key1|100
-- t5--
1|2011-01-01|t2_key1|t3_key1|3000
2|2011-02-02|t2_key1|t3_key2|2000
3|2011-03-03|t2_key2|t3_key2|1000
4|2011-04-04|t2_key1|t3_key2|10000
5|2012-01-01|t2_key1|t3_key1|5000
-- 結果 --
12300.0
https://ideone.com/86dQY
language:
SQLite (sqlite 3.27.2)
created:
11 years ago
visibility:
public

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!