两表查询其中一个表T_DIC_SUBSITE有SITE_ID,SITE_NAME,另外一个表T_DATA_FIX_VALUE有 SITE_ID,SAMP_TIME,T_DATA_FIX_VALUE 表是个流水表
表T_DIC_SUBSITE有SITE_ID,SITE_NAME记录如下
31088 1号烟道
31089 2号烟道
T_DATA_FIX_VALUE 表记录如下31088 2008-6-11 2:00:00
31088 2008-6-11 3:00:00
31088 2008-6-11 4:00:00
31088 2008-6-11 5:00:00
31089 2008-6-11 2:00:00
31089 2008-6-11 3:00:00
31089 2008-6-11 4:00:00我想得到的结果是 时间的最后那条数据31088 1号烟道 2008-6-11 5:00:0031089 2号烟道 2008-6-11 4:00:00这样的结果 该怎么查询
表T_DIC_SUBSITE有SITE_ID,SITE_NAME记录如下
31088 1号烟道
31089 2号烟道
T_DATA_FIX_VALUE 表记录如下31088 2008-6-11 2:00:00
31088 2008-6-11 3:00:00
31088 2008-6-11 4:00:00
31088 2008-6-11 5:00:00
31089 2008-6-11 2:00:00
31089 2008-6-11 3:00:00
31089 2008-6-11 4:00:00我想得到的结果是 时间的最后那条数据31088 1号烟道 2008-6-11 5:00:0031089 2号烟道 2008-6-11 4:00:00这样的结果 该怎么查询
http://topic.csdn.net/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html
a.SITE_ID,a.SITE_NAME,max(SAMP_TIME)SAMP_TIME--只顯示一列最大
from
T_DIC_SUBSITE a
join
T_DATA_FIX_VALUE b on a.SITE_ID=b.SITE_ID
group by a.SITE_ID,a.SITE_NAME
a.SITE_ID,a.SITE_NAME,B.SAMP_TIME
from
T_DIC_SUBSITE a
join
T_DATA_FIX_VALUE b on a.SITE_ID=b.SITE_ID
WHERE
NOT EXISTS(SELECT 1 FROM T_DATA_FIX_VALUE WHERE SITE_ID=B.SITE_ID AND SAMP_TIME>B.SAMP_TIME)
INNER JOIN @TB B
ON A.ID=B.ID
GROUP BY A.ID,A.NAME
INSERT INTO @TA SELECT 31088,'1号烟道'
UNION ALL SELECT 31089,'2号烟道'
DECLARE @TB TABLE(ID INT ,TIME DATETIME)
INSERT INTO @TB SELECT 31088,'2008-6-11 2:00:00'
UNION ALL SELECT 31088,'2008-6-11 3:00:00'
UNION ALL SELECT 31088,'2008-6-11 4:00:00'
UNION ALL SELECT 31088,'2008-6-11 5:00:00'
UNION ALL SELECT 31089,'2008-6-11 2:00:00'
UNION ALL SELECT 31089,'2008-6-11 3:00:00'
UNION ALL SELECT 31089,'2008-6-11 4:00:00' SELECT A.ID,A.NAME,MAX(B.TIME) AS TIME FROM @TA A
INNER JOIN @TB B
ON A.ID=B.ID
GROUP BY A.ID,A.NAME
/*
ID NAME TIME
----------- -------------------- ------------------------------------------------------
31088 1号烟道 2008-06-11 05:00:00.000
31089 2号烟道 2008-06-11 04:00:00.000(所影响的行数为 2 行)
*/
from t_dic_subsite a
from t_dic_subsite a----------------------
SELECT a.SITE_ID, a.SITE_NAME, MAX(b.SAMP_TIME) AS SAMP_TIME FROM T_DIC_SUBSITE a INNER JOIN T_DATA_FIX_VALUE b ON a.SITE_ID = b.SITE_ID GROUP BY a.SITE_ID, a.SITE_NAME
这两种方法效率高点 因为T_DATA_FIX_VALUE 数据量比较大