这样试一下: 月初: select top 1 bcds from yourtable where month(cbrq)=month('03/12/2000') order by cbrq 月末: select top 1 bcds from yourtable where month(cbrq)=month('03/12/2000') order by cbrq desc 费用: select sum(fy) from yourtable where month(cbrq)=month('03/12/2000')条件有必要的话加上year(cbrq)=year('03/12/2000')
这个数据库设计不好如果时间字段是时间戳的话 月初读数用 select bcds from yourtable where cbrq=(select min(cbrq) from yourtable where cbrq between 月初时间戳 and 月末时间戳) 取 月末读数用 select bcds from yourtable where cbrq=(select max(cbrq) from yourtable where cbrq between 月初时间戳 and 月末时间戳) 取
问题是数据库中不止一块表,数据库建的没错,是他们读数是按天读的,我的做一个查询,选择某月,把所有表的月初数和月末数给显出来!!!月初时间戳 and 月末时间戳放在另一个数据表中,可以查出来,他们的月不是自然月。
试试下面的语句吧。(SQL server) ycds 月初读数。 ymds 月末读数。select bh,ycds=(select bcds from your_table where cbrq=(select min(cbrq) from your_table where month(cbrq)="Query_month" )),ymds=(select bcds from your_table where cbrq=(select max(cbrq) from your_table where month(cbrq)="Query_month" )) 费用一项,我不知道你的计算规则,所以,不知如何写。
sorry,写错了select bh,ycds=(select bcds from your_table where cbrq=(select min(cbrq) from your_table where month(cbrq)="Query_month" )),ymds=(select bcds from your_table where cbrq=(select max(cbrq) from your_table where month(cbrq)="Query_month" ))from your_table group by bh
因为抄表日期是不同的,而且是根据时间的先后顺序写的,所以你可这样写: index on cbrq to lsb(临时表) go top lsb表中的第一条数据就是月初的数据.
CREATE VIEW dbo.VIEW1 AS SELECT b.YYYYMM, b.bh, b.ycds, b.ydds, b.fy FROM ( SELECT a.YYYYMM, a.bh, MIN(a.bcds) AS ycds, MAX(a.bcds) AS ydds, SUM(a.fy) AS fy FROM ( SELECT LEFT(CONVERT(char(8), cbrq, 112), 6) AS YYYYMM, bh, scds, bcds, cbrq, fy FROM tablename ) AS a GROUP BY YYYYMM, bh) AS b 試試
用下面一条 SQL 语句可以实现你的查询, 不好用包换: SELECT bh, min(scds) AS scds, max(bcds) AS bcds, sum(fy) AS fy FROM TableName WHERE (cbrq >= '月初日期') AND (abc.cbrq <= '月末日期') GROUP BY bh ORDER BY bh
月初:
select top 1 bcds from yourtable
where month(cbrq)=month('03/12/2000')
order by cbrq
月末:
select top 1 bcds from yourtable
where month(cbrq)=month('03/12/2000')
order by cbrq desc
费用:
select sum(fy) from yourtable
where month(cbrq)=month('03/12/2000')条件有必要的话加上year(cbrq)=year('03/12/2000')
月初读数用
select bcds from yourtable where cbrq=(select min(cbrq) from yourtable where
cbrq between 月初时间戳 and 月末时间戳)
取
月末读数用
select bcds from yourtable where cbrq=(select max(cbrq) from yourtable where
cbrq between 月初时间戳 and 月末时间戳)
取
ycds 月初读数。
ymds 月末读数。select bh,ycds=(select bcds from your_table where cbrq=(select min(cbrq) from your_table where month(cbrq)="Query_month" )),ymds=(select bcds from your_table where cbrq=(select max(cbrq) from your_table where month(cbrq)="Query_month" ))
费用一项,我不知道你的计算规则,所以,不知如何写。
index on cbrq to lsb(临时表)
go top
lsb表中的第一条数据就是月初的数据.
AS
SELECT b.YYYYMM, b.bh, b.ycds, b.ydds, b.fy
FROM ( SELECT a.YYYYMM,
a.bh,
MIN(a.bcds) AS ycds,
MAX(a.bcds) AS ydds,
SUM(a.fy) AS fy
FROM ( SELECT LEFT(CONVERT(char(8), cbrq, 112), 6) AS YYYYMM,
bh,
scds,
bcds,
cbrq,
fy
FROM tablename ) AS a
GROUP BY YYYYMM, bh) AS b
試試
select * from view1 where yyyymm='200011'
如果表數出現大-〉小的循環,先用MIN()/MAX()求出最早/最晚的日期,再按求出的日期連接得到最早/最晚時的表數。再
SELECT bh, min(scds) AS scds, max(bcds) AS bcds, sum(fy) AS fy
FROM TableName
WHERE (cbrq >= '月初日期') AND (abc.cbrq <= '月末日期')
GROUP BY bh
ORDER BY bh