求助sql 联合查询分组
SELECT userid,CONVERT(VARCHAR(10),checktime,120) AS 'date',MIN(checktime) AS 't1' FROM checkinout where (checktime>'2013-6-1 1:00:00' and checktime<'2013-6-30 23:50:00') GROUP BY CONVERT(VARCHAR(10),checktime,120),userid order by userid,'date'
这个查询用户每天最早的checktime，
SELECT userid,,CONVERT(VARCHAR(10),checktime,120) AS 'date',min(checktime) as t2 from checkinout where datename(hh,checktime)>12 and (checktime>'2013-6-1 1:00:00' and checktime<'2013-6-30 23:50:00') group by CONVERT(VARCHAR(10),checktime,120),userid order by userid
这个查询用户每天下午最早的checktime， 数据都在一个表，怎么把2个结果连接起来
[解决办法]
SELECT userid,CONVERT(VARCHAR(10),checktime,120) AS 'date',MIN(checktime) AS 't1' FROM checkinout where (checktime>'2013-6-1 1:00:00' and checktime<'2013-6-30 23:50:00') GROUP BY CONVERT(VARCHAR(10),checktime,120),userid order by userid,'date'
union all
SELECT userid,,CONVERT(VARCHAR(10),checktime,120) AS 'date',min(checktime) as t2 from checkinout where datename(hh,checktime)>12 and (checktime>'2013-6-1 1:00:00' and checktime<'2013-6-30 23:50:00') group by CONVERT(VARCHAR(10),checktime,120),userid order by userid
SELECT userid,
CONVERT(VARCHAR(10), checktime, 120) AS 'date',
Min(checktime) AS 't1',
NULL 't2'
FROM checkinout
WHERE ( checktime > '2013-6-1 1:00:00'
AND checktime < '2013-6-30 23:50:00' )
GROUP BY CONVERT(VARCHAR(10), checktime, 120),
userid
UNION
SELECT userid,
CONVERT(VARCHAR(10), checktime, 120) AS 'date',
NULL,
Min(checktime) AS t2
FROM checkinout
WHERE Datename(hh, checktime) > 12
AND ( checktime > '2013-6-1 1:00:00'
AND checktime < '2013-6-30 23:50:00' )
GROUP BY CONVERT(VARCHAR(10), checktime, 120),
userid
ORDER BY userid
--横向连接
SELECT a.userid, a.[date], a.t1, b.t2
FROM
(
SELECT userid ,
CONVERT(VARCHAR(10), checktime, 120) AS [date] ,
MIN(checktime) AS [t1]
FROM checkinout
WHERE ( checktime > '2013-6-1 1:00:00'
AND checktime < '2013-6-30 23:50:00' )
GROUP BY CONVERT(VARCHAR(10), checktime, 120) , userid
) M
LEFT JOIN
(
SELECT userid ,
CONVERT(VARCHAR(10), checktime, 120) AS [date] ,
MIN(checktime) AS t2
FROM checkinout
WHERE DATENAME(hh, checktime) > 12
AND ( checktime > '2013-6-1 1:00:00'
AND checktime < '2013-6-30 23:50:00' )
GROUP BY CONVERT(VARCHAR(10), checktime, 120) , userid
) N
ON m.Userid = n.Userid
AND m.[date] = n.[date]
--纵向连接
SELECT orderfile = 1,
userid ,
CONVERT(VARCHAR(10), checktime, 120) AS 'date' ,
MIN(checktime) AS [t1]
FROM checkinout
WHERE ( checktime > '2013-6-1 1:00:00'
AND checktime < '2013-6-30 23:50:00'
)
GROUP BY CONVERT(VARCHAR(10), checktime, 120) ,
userid
UNION ALL
SELECT orderfile = 2,
userid ,
CONVERT(VARCHAR(10), checktime, 120) AS 'date' ,
MIN(checktime) AS t2
FROM checkinout
WHERE DATENAME(hh, checktime) > 12
AND ( checktime > '2013-6-1 1:00:00'
AND checktime < '2013-6-30 23:50:00'
)
GROUP BY CONVERT(VARCHAR(10), checktime, 120) ,
userid
ORDER BY orderfile, userid, [date]
