SELECT a.SITE_ID, a.SITE_NAME, b.SAMPLING_DATETIME, COUNT(b.SITE_ID)
AS countID
SELECT a.SITE_ID, a.SITE_NAME, b.SAMPLING_DATETIME, COUNT(b.SITE_ID)
AS countID
FROM T_DIC_SUBSITE a LEFT OUTER JOIN
FIXDATA b ON a.SITE_ID = b.SITE_ID AND
b.SAMPLING_DATETIME >= '2006-05-08' AND
b.SAMPLING_DATETIME < '2006-05-09'
GROUP BY a.SITE_ID, a.SITE_NAME, b.SAMPLING_DATETIME
ORDER BY b.SAMPLING_DATETIME DESC
FIXDATA 表是这样的
SITE_ID SAMPLING_DATETIME
0101 2006-8-16 17:00:00
0101 2006-8-16 16:50:00
0101 2006-8-16 16:30:00
0101 2006-8-15 14:40:00
T_DIC_SUBSITE 表是这样的
SITE_ID SITE_NAME我现在的结果是
SITE_ID SAMPLING_DATETIME countID
0101 2006-8-16 17:00:00 5
0101 2006-8-16 16:50:00 6
0101 2006-8-16 16:30:00 7
0101 2006-8-15 14:40:00 8我想得到的是
SITE_ID SAMPLING_DATETIME countID
0101 2006-8-16 17:00:00 18
0101 2006-8-15 14:40:00 8这样的结果该怎么做
AS countID
SELECT a.SITE_ID, a.SITE_NAME, b.SAMPLING_DATETIME, COUNT(b.SITE_ID)
AS countID
FROM T_DIC_SUBSITE a LEFT OUTER JOIN
FIXDATA b ON a.SITE_ID = b.SITE_ID AND
b.SAMPLING_DATETIME >= '2006-05-08' AND
b.SAMPLING_DATETIME < '2006-05-09'
GROUP BY a.SITE_ID, a.SITE_NAME, b.SAMPLING_DATETIME
ORDER BY b.SAMPLING_DATETIME DESC
FIXDATA 表是这样的
SITE_ID SAMPLING_DATETIME
0101 2006-8-16 17:00:00
0101 2006-8-16 16:50:00
0101 2006-8-16 16:30:00
0101 2006-8-15 14:40:00
T_DIC_SUBSITE 表是这样的
SITE_ID SITE_NAME我现在的结果是
SITE_ID SAMPLING_DATETIME countID
0101 2006-8-16 17:00:00 5
0101 2006-8-16 16:50:00 6
0101 2006-8-16 16:30:00 7
0101 2006-8-15 14:40:00 8我想得到的是
SITE_ID SAMPLING_DATETIME countID
0101 2006-8-16 17:00:00 18
0101 2006-8-15 14:40:00 8这样的结果该怎么做
COUNT(b.SITE_ID) AS countID
FROM T_DIC_SUBSITE a LEFT OUTER JOIN
FIXDATA b ON a.SITE_ID = b.SITE_ID AND
b.SAMPLING_DATETIME >= '2006-05-08' AND
b.SAMPLING_DATETIME < '2006-05-09'
GROUP BY a.SITE_ID, a.SITE_NAME, convert(char(10),b.SAMPLING_DATETIME,120)
ORDER BY SAMPLING_DATETIME DESC
AS countID
FROM T_DIC_SUBSITE a LEFT OUTER JOIN
FIXDATA b ON a.SITE_ID = b.SITE_ID AND
b.SAMPLING_DATETIME >= '2006-05-08' AND
b.SAMPLING_DATETIME < '2006-05-09'
GROUP BY a.SITE_ID, a.SITE_NAME, convert(char(10),b.SAMPLING_DATETIME,120)
ORDER BY b.SAMPLING_DATETIME DESC