现有一张表A,里面有每天的数据,例如:a0(日期),a1(公里),a2(米),我想实现日期之间的对比,
例如:
a0 a1 a2
2008-10-15 1488 100 (表示2008-10-15日1488公里内500米)
2008-10-15 1489 250
2008-10-15 1490 120
2008-10-15 1491 300
2008-10-15 1492 250
2008-9-15 1488 80 (表示2008-9-15日1488公里内80米)
2008-9-15 1489 230
2008-9-15 1490 200
2008-9-15 1491 250
2008-9-15 1492 220
现在要实现
2008-9-15和2008-10-15之间的数据对比,并且把a2字段相差20米的记录列出来,我想写个SQL如何写啊,请各位高手指点,谢谢!!!
例如:
a0 a1 a2
2008-10-15 1488 100 (表示2008-10-15日1488公里内500米)
2008-10-15 1489 250
2008-10-15 1490 120
2008-10-15 1491 300
2008-10-15 1492 250
2008-9-15 1488 80 (表示2008-9-15日1488公里内80米)
2008-9-15 1489 230
2008-9-15 1490 200
2008-9-15 1491 250
2008-9-15 1492 220
现在要实现
2008-9-15和2008-10-15之间的数据对比,并且把a2字段相差20米的记录列出来,我想写个SQL如何写啊,请各位高手指点,谢谢!!!
解决方案 »
- 数字显示的问题,请求帮助
- sql语句转换 从sqlsever到access中也能使用?
- 假如供应商在2005-3-8 12:10:45这个时间签回了一张采购单给采购厂家,然后如果厂家在2005-3-8 12:10:45开始后的24小时内修改过这张采
- 有这样的需求 这样设计数据库对不对
- 谁有随机取记录的例程,或是给出编程思路也好。
- 在线求left join的用法,谢谢
- 奇怪问题:为什么将插入记录的日期列的值设置为空时,日期却有显示为“1900-1-1”?下附测试代码。
- 巨难问题,非高手不能解决!
- 一个比较难的问题,数据排序问题!
- 高分问题:将数据库另存为一个备份的代码怎么写?
- 求一个sql语句写法
- 大家都是如何使用帮助的,比如说有了个想法,如何更快找到需要的类、函数、存储过程等?在帮助系统中大海捞针吗?还是...
(
SELECT a1,a2*-1 as a2
FROM A
WHERE a0='2008-10-15'
UNION ALL
SELECT a1,a2*
FROM A
WHERE a0='2008-09-15'
)tmp
GROUP BY a1
HAVING ABS(SUM(a2))=20
SELECT a1,ABS(SUM(a2)) as Diffs
(
SELECT a1,a2*-1 as a2
FROM A
WHERE a0='2008-10-15'
UNION ALL
SELECT a1,a2
FROM A
WHERE a0='2008-09-15'
)tmp
GROUP BY a1
HAVING ABS(SUM(a2))=20
when max(a2)-min(a2)>20
then datepart(y,a0),datepart(d,a0),a1
end
from A
group by
datepart(y,a0),datepart(d,a0),a1
declare @s table (a0 datetime,a1 int,a2 int)
insert into @s
select '2008-10-15',1488,100 union all
select '2008-10-15',1489,250 union all
select '2008-10-15',1490,120 union all
select '2008-10-15',1491,300 union all
select '2008-10-15',1492,250 union all
select '2008-9-15',1488,80 union all
select '2008-9-15',1489,230 union all
select '2008-9-15',1490,200 union all
select '2008-9-15',1491,250 union all
select '2008-9-15',1492,220
select * from @s a,@s b where datediff(mm,a.a0,b.a0)=1 and a.a1=b.a1 and abs(a.a2-b.a2)=20
insert into @tb
select '2008-10-15', 1488 , 100 union all
select '2008-10-15', 1489 , 250 union all
select '2008-10-15', 1490 , 120 union all
select '2008-10-15', 1491 , 300 union all
select '2008-10-15', 1492 , 250 union all
select '2008-9-15', 1488 , 80 union all
select '2008-9-15', 1489 , 230 union all
select '2008-9-15', 1490 , 200 union all
select '2008-9-15', 1491 , 250 union all
select '2008-9-15', 1492 , 220
--select * from @tbselect a.*,b.a0 as a00,b.a1 as a11,b.a2 as a22 into #a from
(select * from @tb where a0='2008-10-15') a,
(select * from @tb where a0='2008-9-15') b
where a.a1=b.a1select * from #a
select * from #a where abs(a22-a2)=20
a0 a1 a2 a00 a11 a22
-------------------- ----------- ----------- -------------------- ----------- -----------
2008-10-15 1488 100 2008-9-15 1488 80
2008-10-15 1489 250 2008-9-15 1489 230
2008-10-15 1490 120 2008-9-15 1490 200
2008-10-15 1491 300 2008-9-15 1491 250
2008-10-15 1492 250 2008-9-15 1492 220(所影响的行数为 5 行)a0 a1 a2 a00 a11 a22
-------------------- ----------- ----------- -------------------- ----------- -----------
2008-10-15 1488 100 2008-9-15 1488 80
2008-10-15 1489 250 2008-9-15 1489 230(所影响的行数为 2 行)