TGDZcalc by SQL (45th)

一直以来想试试用SQL查询语言来表达一下TGDZcalc,研究后发现它其实不是一个完整的编程语言,连基本的变量声明都没有得到普遍的支持.因此使用它来计算,就有点像用EXCEL表格中的内置函数(不用VBA编程)来算一样勉为其难.
通过多次重复子查询,还是可以作一点计算的.

点击查看sQL代码

//前提是在SQL中建立了一个表格,有ID,TG,DZ三个字段,其有12条记录,ID为0..11, TG依次是甲..癸,DZ依次是子..亥. 
//表格结构如下：
sqlite> pragma table_info(main);
0|ID|INT|1||1
1|TG|CHAR(1)|0||0
2|DZ|CHAR(1)|0||0
3|memo|char(5)|0||0
4|ti|INTEGER|0||0
5|di|INTEGER|0||0
6|m|INTEGER|0||0
7|baseYear|INTEGER|0||0
8|CurYear|INTEGER|0||0

//数据记录如下.
sqlite> select * from main;
0|甲|子||2|4|10|1924|1980
1|乙|丑||||||
2|丙|寅||||||
3|丁|卯| |||||
4|戊|辰| |||||
5|己|巳| |||||
6|庚|午| |||||
7|辛|未| |||||
8|壬|申| |||||
9|癸|酉| |||||
10||戌| |||||
11||亥| |||||

执行以下SQL语句可以查询.
//年份→干支
 select (select TG from main where id=(select mod(1976-if(1976>0,4,3),10))) || (select DZ from main where id=(select mod(1976-if(1976>0,4,3),12))); 

//干支→年份,需要手工将天干,地支拆分一下,以简化语句.
select if((select id from main where TG='丙')>(select id from main where DZ='辰'), (select id from main where TG='丙')-(select id from main where DZ='辰'), 12+(select id from main where TG='丙')-(select id from main where DZ='辰'))*5 + 1924 +  (select id from main where TG='丙');

当然,也可以用其它方法来变通,比如用数据库的记录来存储变量的值,

点击查看SQL代码

//利用数据表的某记录作为临时变量来进行计算.
//将变量ti,di,变量m存入表格第一个记录(id=0的记录).这表格的信息见上例的pragma信息.
update main set ti=(select id from main where TG='丙'), di=(select id  as di from main where DZ='辰') where id=0;
update main set m=(select if((select id from main where TG='丙')>(select id from main where DZ='辰'), (select id from main where TG='丙')-(select id from main where DZ='辰'), 12+(select id from main where TG='丙')-(select id from main where DZ='辰'))) where id=0;
update main set baseYear=1924;
//计算结果
select (select m from main where id=0)*5 + (select baseYear from main where id=0) + (select ti from main where  id=0);

遗憾的是,暂时没有找到好的算baseYear的方法.

运行界面,基于SQLite3.