最近做水司网站,有一个是水费明细,请大家给个数据库的设计思路,
一个用户可能有多个水表,
要求页面实现的功能,
用户登录以后可以看到有几个水表,比如我是邮政局,登录后可以看到如下图:点击查看详情超链接分别可查看每个水表的用水情况:重点就是这里记录每月止码和上月止码的数据表怎么设计,请大神指点
一个用户可能有多个水表,
要求页面实现的功能,
用户登录以后可以看到有几个水表,比如我是邮政局,登录后可以看到如下图:点击查看详情超链接分别可查看每个水表的用水情况:重点就是这里记录每月止码和上月止码的数据表怎么设计,请大神指点
水表设备表(水表ID,位置,用户ID,。)
抄表记录(水表ID,抄表时间,读数,)
SELECT
T1.id AS '水表号', T1.dt AS '抄表日期', T1.yr AS '年份',
T1.mh AS '月份', IFNULL(T2.cnt, 0) AS '上月止码',
T1.cnt AS '本月抄表', (T1.cnt - IFNULL(T2.cnt, 0)) AS '用水量'
FROM
(SELECT id, dt, cnt, YEAR(dt) AS yr, MONTH(dt) AS mh FROM tbl03) AS T1
LEFT JOIN
(SELECT
id, YEAR(DATE_ADD(dt, INTERVAL 1 MONTH)) AS yr,
MONTH(DATE_ADD(dt, INTERVAL 1 MONTH)) AS mh, cnt AS cnt FROM tbl03
) AS T2
ON T1.yr = T2.yr AND T1.mh = T2.mh
SELECT
T2.id AS '水表号', T2.dt AS '抄表日期', YEAR(T2.dt) AS '年份',
MONTH(T2.dt) AS '月份', IFNULL(T3.cnt, 0) AS '上月止码',
T2.cnt AS '本月抄表', (T2.cnt - IFNULL(T3.cnt, 0)) AS '用水量'
FROM
(
SELECT tt2.autoid AS autoid1, YEAR(tt2.dt) AS yr, MONTH(tt2.dt) AS mh,
(SELECT MAX(autoid) FROM (SELECT * FROM tbl03 WHERE id ='表编号')
AS tt1 WHERE tt1.autoid < tt2.autoid) AS autoid2
FROM tbl03 tt2
) AS T1
LEFT JOIN (SELECT * FROM tbl03) AS T2
ON T1.autoid1 = T2.autoid
LEFT JOIN (SELECT * FROM tbl03) AS T3
ON T1.autoid2 = T3.autoid
WHERE T2.id ='表编号'