解决方案 »
- sysname是什么类型呀,许多系统表的字段就是这个类型的,我如何看这个类型的定义呢?
- 初学者救助 用sql sever 2000 编写一个触发器的定义
- 这个问题如何解决?谢谢各位大哥
- 关系表ID值更改问题
- SSL加密SQL Server 时,重启服务器无法启动?
- SQL 判断输出图片
- 请高手们帮忙看看这个存储过程我该怎么更改。
- 通过查询语句 显示自增加列
- 为什么查询分析器的结果和程序中查询结果不一样。
- 一个让我苦恼不已的问题:用VB写成的数据库程序,在我的机器里运行很好,但是在客户机器里却出现不支持ODBC属性的错误,那位大虾遇到过类似的情况?又是如何解决的?哎,真是郁闷死了
- sqlserver 全文索引
- 一个让人感到困惑的问题,数据库里的数据表的数据出现不一致的情况.(建议高手看看!)
select SUM(n) from tableB where [time] between '' and '' and ID =(select id from tablea where stationname='')
from A join B on A.id=b.id
where time between '' and ''
group by stationname
SELECT B.ID, SUM(B.N), MAX(B.TIME), MIN(B.TIME)
FROM 表A A LEFT JOIN 表B B ON A.ID = B.ID
WHERE A.区域 = '区域1'
GROUP BY B.ID
--测试数据
create table ta (id int,stationname nvarchar(20),area nvarchar(20))
insert into ta
select 1,'站点1','区域1' union all
select 2,'站点2','区域1' union all
select 3,'站点3','区域1' union all
select 4,'站点4','区域1' union all
select 5,'站点5','区域1' union all
select 6,'站点6','区域2' union all
select 7,'站点7','区域2' union all
select 8,'站点8','区域2' union all
select 9,'站点9','区域2' union all
select 10,'站点10','区域2'create table tb (id int,time datetime,n int)
insert into tb
select 1,'2013-1-11',10 union all
select 1,'2013-1-2',12 union all
select 1,'2013-1-3',15 union all
select 1,'2013-1-4',18 union all
select 1,'2013-1-8',25 union all
select 1,'2013-1-9',30 union all
select 2,'2013-1-1',2 union all
select 2,'2013-1-2',9 union all
select 2,'2013-1-3',15 union all
select 2,'2013-1-4',16 union all
select 2,'2013-1-5',19 union all
select 2,'2013-1-6',25 union all
select 2,'2013-1-7',30 union all
select 8,'2013-1-1',50 --结果
select SUM(n) as total,tt.id,MAX(tt.time)as '最新',MIN(tt.time) as'最旧' from (select ta.id,ta.stationname,tb.time,tb.n from ta inner join tb on ta.id=tb.id and ta.area='区域1' )tt where tt.time between '2013-1-2' and '2013-1-7' group by tt.id
SELECT t.stationname ,t.id ,t.bigTime , bb.n ,t.minTime , cc.n
FROM ( SELECT MAX(TIME) bigTime , MIN(TIME) minTime , a.id , a.stationname
FROM ta a INNER JOIN TB b ON a.id = b.id AND a.area = '区域1'
WHERE b.TIME BETWEEN '2013-01-02' AND '2013-01-07'
GROUP BY a.stationname ,a.id
) t
LEFT JOIN TB bb ON t.bigTime = bb.TIME AND t.id = bb.id
LEFT JOIN TB cc ON t.minTime = cc.TIME AND t.id = cc.id结果:
create table A (id int,stationname nvarchar(10),area nvarchar(10))
insert into A
select 1,'站点1','区域1' UNION ALL
select 2,'站点2','区域1' UNION ALL
select 3,'站点3','区域1' UNION ALL
select 4,'站点4','区域1' UNION ALL
select 5,'站点5','区域1' UNION ALL
select 6,'站点6','区域2' UNION ALL
select 7,'站点7','区域2' UNION ALL
select 8,'站点8','区域2' UNION ALL
select 9,'站点9','区域2' UNION ALL
select 10,'站点10','区域2'CREATE TABLE B (id int,[time] datetime,n int)
INSERT INTO B
SELECT 1,'2013-1-1',10 UNION ALL
SELECT 1,'2013-1-2',12 UNION ALL
SELECT 1,'2013-1-3',15 UNION ALL
SELECT 1,'2013-1-1',2 UNION ALL
SELECT 2,'2013-1-2',9 UNION ALL
SELECT 2,'2013-1-3',15 UNION ALL
SELECT 2,'2013-1-4',16
--语句SELECT C.stationname AS '站点名',C.最旧日期,BB.n AS '最旧度',C.最新日期,CC.n AS '最新度'
FROM
(
SELECT A.stationname,A.id,MIN(B.time) AS '最旧日期',MAX(B.time) AS '最新日期'
FROM A
LEFT JOIN B
ON A.id=B.id
WHERE A.area='区域1'
AND B.time BETWEEN '2013-1-2' AND '2013-1-7'
GROUP BY A.stationname,A.id
) AS C
LEFT JOIN B AS BB
ON BB.id=C.id AND BB.time=C.最旧日期
LEFT JOIN B AS CC
ON CC.id=C.id AND CC.time=C.最新日期--结果
站点名 最旧日期 最旧度 最新日期 最新度
---------- ----------------------- ----------- ----------------------- -----------
站点1 2013-01-02 00:00:00.000 12 2013-01-03 00:00:00.000 15
站点2 2013-01-02 00:00:00.000 9 2013-01-04 00:00:00.000 16
select a.id,b.N as max_val,case when b.n is null then null else b.max_time end max_time,
d.n as min_val,case when d.n is null then null else b.min_time end min_time
from A a left join B b on a.id=b.id
left join (select id,max(time) max_time,min(time) min_time
from B where time between '20130102' and '20130107'
group by id )c on b.id=c.id and b.time=c.max_time
left join B d on c.id=d.id and c.min_time=d.time
where a.area='區域1'