说明:一个签到系统,2张表1个为user表,另一个为sign表
user (id,username,password,name,power)
sign (id, userid,signTime)
签一次到向sign表插入一条数据
我的问题:
我想查询 2007-07-02 到 2007-07-30 签到记录
条件是,
按每一天来分组显示,每一组把user表所有的用户都列出来(有的用户可能有一天没签到 但也要显示出username的值,只是不显示signtime列的值)
请大侠们指教!!
user (id,username,password,name,power)
sign (id, userid,signTime)
签一次到向sign表插入一条数据
我的问题:
我想查询 2007-07-02 到 2007-07-30 签到记录
条件是,
按每一天来分组显示,每一组把user表所有的用户都列出来(有的用户可能有一天没签到 但也要显示出username的值,只是不显示signtime列的值)
请大侠们指教!!
按每一天来分组显示,每一组把user表所有的用户和用户签到信息都列出来,显示这个用户的用户名,这一天的签到时间(有的用户可能有一天没签到 但也要显示出username,只是不显示signtime的值)
select t1.signTime,t2.name from sign t1,user t2
where t1.userid(+)=t2.id
and t1.signTime between to_date('2007-07-02','yyyy-mm-dd') and to_date('2007-07-30','yyyy-mm-dd')
order by t1.signTime desc
然后:
SELECT searchDates.dt,USER.id, USER.username,USER.passowrd, USER.NAME,USER.Power,Sign.userid,sing.signTime
FROM USER, Sign, searchDates
WHERE sign.id = user.id(+)
AND searchDates.dt = Sign.signTime(+)
GROUP BY searchDates.dt我相信是这意思
user
1,a,password
2,b,password
3,c,passwordsign
1,aid,2007-07-03
3,cid,2007-07-05结果
2007-07-02,1,a,null
2007-07-02,2,b,null
2007-07-02,3,c,null
2007-07-03,1,a,2007-07-03
2007-07-03,2,b,null
2007-07-03,3,c,null
2007-07-04,1,a,null
...
2007-07-05,1,a,null
2007-07-05,2,b,null
2007-07-05,3,c,2007-07-05
...
然后:
SELECT searchDates.dt,USER.id, USER.username,USER.passowrd, USER.NAME,USER.Power,Sign.userid,sing.signTime
FROM USER, Sign, searchDates
WHERE user right join sing on sign.id = user.id
AND sign right join searchDates on searchDates.dt = Sign.signTime
GROUP BY searchDates.dt我也不知道我这思路对不对,看来不像 是满足你的意思要是对的话语法哪有错自己改一下吧,毕竟还是要自己学学的
饭来张口衣来伸手的行为
String sql="SELECT searchDates.dt,USER.id, USER.username,USER.passowrd, USER.NAME,USER.Power,Sign.userid,sing.signTime
FROM USER, Sign, searchDates
WHERE user right join sing on sign.id = user.id
AND sign right join searchDates on searchDates.dt = Sign.signTime
GROUP BY searchDates.dt";
可我想在JAVA的rs = stmt.executeQuery(sql)来得到数据 把生成临时表的语句也要放到sql中吗
user
1,a,password
2,b,password
3,c,passwordsign
1,aid,2007-07-03
3,cid,2007-07-05结果
2007-07-02,1,a,null
2007-07-02,2,b,null
2007-07-02,3,c,null
2007-07-03,1,a,2007-07-03
2007-07-03,2,b,null
2007-07-03,3,c,null
2007-07-04,1,a,null
...
2007-07-05,1,a,null
2007-07-05,2,b,null
2007-07-05,3,c,2007-07-05但不用临时表 有好的SQL语句吗?
FROM
(SELECT * FROM
(SELECT DISTINCT CONVERT(CHAR(10), signTime, 111) AS D FROM sign) A
CROSS JOIN (SELECT id FROM user) B) C
INNER JOIN user E ON C.id = E.id
LEFT OUTER JOIN(
SELECT U.id, U.username, S.signTime
FROM user U
LEFT JOIN sign S ON U.id = S.userid)D ON C.id = D.id AND C.D = CONVERT(CHAR(10), D.signTime, 111)
ORDER BY C.D, C.id
(
T1 varchar(10) not Null
)
declare @A DATETIME,
@end DATETIME
SELECT @A= DATEADD(yy, DATEDIFF(yy,0,GETDATE()), 0)
SELECT @end = DATEADD(ms,-3,DATEADD(yy, DATEDIFF(yy,0,GETDATE())+1, 0))
WHILE(@A< @end)
BEGIN
INSERT INTO TB
VALUES(CONVERT(VARCHAR(10), @A, 112))
SELECT @A =DATEADD(Day , 1, @A)
END
select * from TB1
SELECT a.T1,user.id,user.username,sign.signTime
from (select T1 from TB where T1 >='20070702' and T1 <='20070730' )a,user
left join user.id = sign.userid
group by a.T1,user.id,user.username,sign.signTime
order by a.T1,user.id,user.username,sign.signTimedrop table TB
sign right join user on sign.userid=user.id
where
sign.signTime between '2007-07-02' and '2007-07-30'
order by sign.signTime desc
我认为应该是用他们的SQL文 把所有的数据查出来 按时间排好 ,然后去RS里循环取,放到临时的数组里去处理
---------------------------
在那个帖子里不是已经把方法都帖出来了么,我都验证过了。。晕~~---sunshinestation(阳光驿站) 的方法:
SELECT C.D, C.id, E.username, D.signTime
FROM
(SELECT * FROM
(SELECT DISTINCT CONVERT(CHAR(10), signTime, 111) AS D FROM sign) A
CROSS JOIN (SELECT id FROM user) B) C
INNER JOIN user E ON C.id = E.id
LEFT OUTER JOIN(
SELECT U.id, U.username, S.signTime
FROM user U
LEFT JOIN sign S ON U.id = S.userid)D ON C.id = D.id AND C.D = CONVERT(CHAR(10), D.signTime, 111)
ORDER BY C.D, C.id
>_<
create table [user](id int,username varchar(20),password varchar(10),name varchar(10), [power] int)insert into [user] values(1,'a','','a',1);
insert into [user] values(2,'b','','a',1);
insert into [user] values(3,'c','','a',1);
insert into [user] values(4,'d','','a',1);create table [sign](id int,userid int,signtime datetime)
insert into [sign] values(1,1,cast('2007-07-01' as datetime));
insert into [sign] values(1,2,cast('2007-07-02' as datetime));
insert into [sign] values(1,2,cast('2007-07-03' as datetime));
insert into [sign] values(1,3,cast('2007-07-31' as datetime));
select a.* from [user] a left join [sign] b on a.id=b.userid where b.signtime>='2007-07-02' and b.signtime<='2007-07-30' group by convert(varchar(10),b.signtime,20),a.id,a.username,a.password,a.name,a.power
设个变量time 初始化为 2007-07-02
直到2007-07-30
具体怎么写记不清了
查询语句下面这个意思估计可以
select time,userid,username,signTime
from user u left join sign s on u.id=s.userid
where signTime=time
楼上的这么多答案你到底试了没有啊?!?!
别太让人BS了,自己动脑子研究一下,
原理、方法都在那,细节上修改成自己所需要不就行了------非得让别人“帮”你写成现成的(事实上按你描述的,已经是现成的了)自己ctrl+c --->ctrl+v 就完事了??为LZ这样的人汗颜!!
-_-: