有表table1里面有井号,日期,有功电能,无功电能四列(表里的数据都是隔一段十几分钟自动插入的)
现在是我需要在网上上展示出来
问题是:我需要每天进入table1的第一条数据要保留起来,这一天中的后面所有数据的有功电能,和无功电能分别减去第一条数据的有功电能,和无功电能,保留起来。(他们相减我可以在网页中实现这个地方不用管它)
现在单口井能实现,但是如果我选着全部井的数据该怎样显示呀?
下面是单井的做法,求全部井的应该怎样做呀?
SELECT DianJi.油井, Oil_well.油井类型A, DianJi.日期,
(SELECT TOP 1 有功电能
FROM dianji
WHERE 油井 = '滨18X1 ' AND 日期 > '2010-4-23 00:00:00' AND
日期 <= '2010-4-24 23:58:59'
ORDER BY 日期) AS 有功电量2, DianJi.有功电能 AS 有功电量1,
(SELECT TOP 1 无功电能
FROM dianji, oil_well
WHERE 油井 = '滨18X1 ' AND 日期 > '2010-4-23 00:00:00' AND
日期 <= '2010-4-24 23:58:59'
ORDER BY 日期) AS 无功电量2, DianJi.无功电能 AS 无功电量1
FROM DianJi 关键的难点是我怎样求出每一天的最新一条数据保留起来????
现在是我需要在网上上展示出来
问题是:我需要每天进入table1的第一条数据要保留起来,这一天中的后面所有数据的有功电能,和无功电能分别减去第一条数据的有功电能,和无功电能,保留起来。(他们相减我可以在网页中实现这个地方不用管它)
现在单口井能实现,但是如果我选着全部井的数据该怎样显示呀?
下面是单井的做法,求全部井的应该怎样做呀?
SELECT DianJi.油井, Oil_well.油井类型A, DianJi.日期,
(SELECT TOP 1 有功电能
FROM dianji
WHERE 油井 = '滨18X1 ' AND 日期 > '2010-4-23 00:00:00' AND
日期 <= '2010-4-24 23:58:59'
ORDER BY 日期) AS 有功电量2, DianJi.有功电能 AS 有功电量1,
(SELECT TOP 1 无功电能
FROM dianji, oil_well
WHERE 油井 = '滨18X1 ' AND 日期 > '2010-4-23 00:00:00' AND
日期 <= '2010-4-24 23:58:59'
ORDER BY 日期) AS 无功电量2, DianJi.无功电能 AS 无功电量1
FROM DianJi 关键的难点是我怎样求出每一天的最新一条数据保留起来????
(SELECT TOP 1 有功电能
FROM dianji
WHERE 日期 > '2010-4-23 00:00:00' AND
日期 <= '2010-4-24 23:58:59'
ORDER BY 日期) AS 有功电量2, DianJi.有功电能 AS 有功电量1,
(SELECT TOP 1 无功电能
FROM dianji, oil_well
WHERE 日期 > '2010-4-23 00:00:00' AND
日期 <= '2010-4-24 23:58:59'
ORDER BY 日期) AS 无功电量2, DianJi.无功电能 AS 无功电量1
FROM DianJi TRY
(SELECT TOP 1 有功电能
FROM dianji
WHERE 油井 = DianJi.油井 AND 日期 > '2010-4-23 00:00:00' AND
日期 <= '2010-4-24 23:58:59' and convert(varchar(10),日期,120) = convert(varchar(10),DianJi.日期,120)
ORDER BY 日期 desc) AS 有功电量2, DianJi.有功电能 AS 有功电量1,
(SELECT TOP 1 无功电能
FROM dianji, oil_well
WHERE 油井 = DianJi.油井 AND 日期 > '2010-4-23 00:00:00' AND
日期 <= '2010-4-24 23:58:59' and convert(varchar(10),日期,120) = convert(varchar(10),DianJi.日期,120)
ORDER BY 日期 desc) AS 无功电量2, DianJi.无功电能 AS 无功电量1
FROM DianJi
--按某一字段分组取最大(小)值所在行的数据
--(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-10-23于浙江杭州)
/*
数据如下:
name val memo
a 2 a2(a的第二个值)
a 1 a1--a的第一个值
a 3 a3:a的第三个值
b 1 b1--b的第一个值
b 3 b3:b的第三个值
b 2 b2b2b2b2
b 4 b4b4
b 5 b5b5b5b5b5
*/
--创建表并插入数据:
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a', 2, 'a2(a的第二个值)')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('b', 1, 'b1--b的第一个值')
insert into tb values('b', 3, 'b3:b的第三个值')
insert into tb values('b', 2, 'b2b2b2b2')
insert into tb values('b', 4, 'b4b4')
insert into tb values('b', 5, 'b5b5b5b5b5')
go--一、按name分组取val最大的值所在行的数据。
--方法1:
select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
--方法2:
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
--方法3:
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4:
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name
/*
name val memo
---------- ----------- --------------------
a 3 a3:a的第三个值
b 5 b5b5b5b5b5
*/--二、按name分组取val最小的值所在行的数据。
--方法1:
select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name
--方法2:
select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val)
--方法3:
select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4:
select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 1 b1--b的第一个值
*/--三、按name分组取第一次出现的行所在的数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name
/*
name val memo
---------- ----------- --------------------
a 2 a2(a的第二个值)
b 1 b1--b的第一个值
*/--四、按name分组随机取一条数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name order by newid()) order by a.name
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 5 b5b5b5b5b5
*/--五、按name分组取最小的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val < a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val < a.val having Count(*) < 2) order by a.name,a.val
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
a 2 a2(a的第二个值)
b 1 b1--b的第一个值
b 2 b2b2b2b2
*/--六、按name分组取最大的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val desc) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val > a.val having Count(*) < 2) order by a.name , a.val
/*
name val memo
---------- ----------- --------------------
a 2 a2(a的第二个值)
a 3 a3:a的第三个值
b 4 b4b4
b 5 b5b5b5b5b5
*/
--七,如果整行数据有重复,所有的列都相同。
/*
数据如下:
name val memo
a 2 a2(a的第二个值)
a 1 a1--a的第一个值
a 1 a1--a的第一个值
a 3 a3:a的第三个值
a 3 a3:a的第三个值
b 1 b1--b的第一个值
b 3 b3:b的第三个值
b 2 b2b2b2b2
b 4 b4b4
b 5 b5b5b5b5b5
*/
--在sql server 2000中只能用一个临时表来解决,生成一个自增列,先对val取最大或最小,然后再通过自增列来取数据。
--创建表并插入数据:
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a', 2, 'a2(a的第二个值)')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('b', 1, 'b1--b的第一个值')
insert into tb values('b', 3, 'b3:b的第三个值')
insert into tb values('b', 2, 'b2b2b2b2')
insert into tb values('b', 4, 'b4b4')
insert into tb values('b', 5, 'b5b5b5b5b5')
goselect * , px = identity(int,1,1) into tmp from tbselect m.name,m.val,m.memo from
(
select t.* from tmp t where val = (select min(val) from tmp where name = t.name)
) m where px = (select min(px) from
(
select t.* from tmp t where val = (select min(val) from tmp where name = t.name)
) n where n.name = m.name)drop table tb,tmp/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 1 b1--b的第一个值(2 行受影响)
*/
--在sql server 2005中可以使用row_number函数,不需要使用临时表。
--创建表并插入数据:
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a', 2, 'a2(a的第二个值)')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('b', 1, 'b1--b的第一个值')
insert into tb values('b', 3, 'b3:b的第三个值')
insert into tb values('b', 2, 'b2b2b2b2')
insert into tb values('b', 4, 'b4b4')
insert into tb values('b', 5, 'b5b5b5b5b5')
goselect m.name,m.val,m.memo from
(
select * , px = row_number() over(order by name , val) from tb
) m where px = (select min(px) from
(
select * , px = row_number() over(order by name , val) from tb
) n where n.name = m.name)drop table tb/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 1 b1--b的第一个值(2 行受影响)
*/
井号 日期 有功电能 无功电能
港320-2 2010-4-23 09:20:00 116312 103795
港320-2 2010-4-23 09:00:00 116314 103793
港320-2 2010-4-23 08:40:00 116317 103790
港320-2 2010-4-23 08:20:00 116319 103785
港320-2 2010-4-23 08:00:00 116322 103780
港320-2 2010-4-23 07:40:00 116324 103776
房36-38 2010-4-23 09:20:00 4531188 4213417
房36-38 2010-4-23 09:00:00 4531188 4213417
房36-38 2010-4-23 08:40:00 4531188 4213417
房36-38 2010-4-23 08:20:00 4531188 4213417
房36-38 2010-4-23 08:00:00 4531188 4213417
房36-38 2010-4-23 07:40:00 4531188 4213417
中7-73-1 2010-4-23 09:27:00 44125 43469
中7-73-1 2010-4-23 09:06:57 44125 43469
中7-73-1 2010-4-23 08:46:54 44125 43469
中7-73-1 2010-4-23 08:26:51 44125 43469
中7-73-1 2010-4-23 08:06:47 44125 43469
中7-73-1 2010-4-23 07:47:39 44125 43469
中7-73-1 2010-4-23 09:27:00 44125 43469
红16-30 2010-4-23 09:26:50 88528 263248
滨18X1 2010-4-23 09:20:00 213132 426538
东4-8 2010-4-23 09:20:00 102017 206283
东7-26 2010-4-23 09:20:00 133074 9437592
房32-22 2010-4-23 09:20:00 95791 367972
房36-26 2010-4-23 09:20:00 12403 575
房36-38 2010-4-23 09:20:00 4531188 4213417
港1-57-2 2010-4-23 09:20:00 62285 99994
港2-55-1k 2010-4-23 09:20:00 2861 1497
港317-1H 2010-4-23 09:20:00 121145 46381
港320-2 2010-4-23 09:20:00 116312 103795
港3-39-1 2010-4-23 09:20:00 187450 11262
港359-1H 2010-4-23 09:20:00 106588 226502
港4-35-2 2010-4-23 09:20:00 11938 1604
求出所有井的最新一条数据,然后后面一天中的所有的数据都要和第一条数据相减,最有在网页上显示所有井的相减结果
Oil_well.油井类型A,
DianJi.日期,
b.有功电能 AS 有功电量2,
DianJi.有功电能 AS 有功电量1,
b.无功电能 AS 无功电量2,
DianJi.无功电能 AS 无功电量1
FROM DianJi a,
DianJi b
WHERE a.油井 = b.油井
AND a.日期 BETWEEN '2010-4-23 00:00:00' AND '2010-4-24 23:59:59'
AND b.日期 BETWEEN '2010-4-23 00:00:00' AND '2010-4-24 23:59:59'
AND NOT EXISTS(
SELECT 1
FROM DianJi c
WHERE c.日期 BETWEEN '2010-4-23 00:00:00' AND
'2010-4-24 23:59:59'
AND c.油井 = b.油井
AND c.日期 < b.日期
)
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-23 09:37:24
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)
-- May 26 2009 14:24:20
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([井号] varchar(9),[日期] datetime,[有功电能] int,[无功电能] int)
insert [tb]
select '港320-2','2010-4-23 09:20:00',116312,103795 union all
select '港320-2','2010-4-23 09:00:00',116314,103793 union all
select '港320-2','2010-4-23 08:40:00',116317,103790 union all
select '港320-2','2010-4-23 08:20:00',116319,103785 union all
select '港320-2','2010-4-23 08:00:00',116322,103780 union all
select '港320-2','2010-4-23 07:40:00',116324,103776 union all
select '房36-38','2010-4-23 09:20:00',4531188,4213417 union all
select '房36-38','2010-4-23 09:00:00',4531188,4213417 union all
select '房36-38','2010-4-23 08:40:00',4531188,4213417 union all
select '房36-38','2010-4-23 08:20:00',4531188,4213417 union all
select '房36-38','2010-4-23 08:00:00',4531188,4213417 union all
select '房36-38','2010-4-23 07:40:00',4531188,4213417 union all
select '中7-73-1','2010-4-23 09:27:00',44125,43469 union all
select '中7-73-1','2010-4-23 09:06:57',44125,43469 union all
select '中7-73-1','2010-4-23 08:46:54',44125,43469 union all
select '中7-73-1','2010-4-23 08:26:51',44125,43469 union all
select '中7-73-1','2010-4-23 08:06:47',44125,43469 union all
select '中7-73-1','2010-4-23 07:47:39',44125,43469 union all
select '中7-73-1','2010-4-23 09:27:00',44125,43469 union all
select '红16-30','2010-4-23 09:26:50',88528,263248 union all
select '滨18X1','2010-4-23 09:20:00',213132,426538 union all
select '东4-8','2010-4-23 09:20:00',102017,206283 union all
select '东7-26','2010-4-23 09:20:00',133074,9437592 union all
select '房32-22','2010-4-23 09:20:00',95791,367972 union all
select '房36-26','2010-4-23 09:20:00',12403,575 union all
select '房36-38','2010-4-23 09:20:00',4531188,4213417 union all
select '港1-57-2','2010-4-23 09:20:00',62285,99994 union all
select '港2-55-1k','2010-4-23 09:20:00',2861,1497 union all
select '港317-1H','2010-4-23 09:20:00',121145,46381 union all
select '港320-2','2010-4-23 09:20:00',116312,103795 union all
select '港3-39-1','2010-4-23 09:20:00',187450,11262 union all
select '港359-1H','2010-4-23 09:20:00',106588,226502 union all
select '港4-35-2','2010-4-23 09:20:00',11938,1604
--------------开始查询--------------------------
select
*
from
tb t
where
日期=(select min(日期) from tb where 井号=t.井号 and convert(varchar(10),日期,120)=convert(varchar(10),t.日期,120))
----------------结果----------------------------
/* 井号 日期 有功电能 无功电能
--------- ----------------------- ----------- -----------
港320-2 2010-04-23 07:40:00.000 116324 103776
房36-38 2010-04-23 07:40:00.000 4531188 4213417
中7-73-1 2010-04-23 07:47:39.000 44125 43469
红16-30 2010-04-23 09:26:50.000 88528 263248
滨18X1 2010-04-23 09:20:00.000 213132 426538
东4-8 2010-04-23 09:20:00.000 102017 206283
东7-26 2010-04-23 09:20:00.000 133074 9437592
房32-22 2010-04-23 09:20:00.000 95791 367972
房36-26 2010-04-23 09:20:00.000 12403 575
港1-57-2 2010-04-23 09:20:00.000 62285 99994
港2-55-1k 2010-04-23 09:20:00.000 2861 1497
港317-1H 2010-04-23 09:20:00.000 121145 46381
港3-39-1 2010-04-23 09:20:00.000 187450 11262
港359-1H 2010-04-23 09:20:00.000 106588 226502
港4-35-2 2010-04-23 09:20:00.000 11938 1604(15 行受影响)*/