我现在有两个表:table1: (用户列表)UserID(int),UserName(nverchar)UserID UserName
1 张三
2 李四
3 曹五
4 赵六table2:(登陆情况) UserID(int),LonginTime(datetime)UserID LonginTime
2 2009-1-1 12:12:12
3 2009-1-2 13:12:12我想根据上面的两个表信息,动态生成一个记录集,如下:
UserID(int) IsLongin(bool)UserID IsLongin
1 NO
2 YES
3 YES
4 NO应该怎样写?
1 张三
2 李四
3 曹五
4 赵六table2:(登陆情况) UserID(int),LonginTime(datetime)UserID LonginTime
2 2009-1-1 12:12:12
3 2009-1-2 13:12:12我想根据上面的两个表信息,动态生成一个记录集,如下:
UserID(int) IsLongin(bool)UserID IsLongin
1 NO
2 YES
3 YES
4 NO应该怎样写?
解决方案 »
- sql2000中同一用户,每一个连接是不是独立的一个会话?
- 请教一下查询
- 50万的数据量
- 急!!!求一个有关SQL语句中数据格式的问题
- 利用OpenFileDialog打开SQL数据库即mdf文件时,提示该文件正在使用,该怎么解决?
- 怎样用另一数据库的表更新当前数据库的表
- SQL问题....
- 如何在一个新的数据库中加入已有数据库的表和视图
- 不同数据库之间的表数据复制
- 给介绍一本SQL 比较好的书集,要针对入门者的哦!!赠分!
- 用sql 2005的managerment studio可以管理sql server 2000 吗,我发现同样可以管理,不知有没有问题。
- select cast(shengri as char(10)) from userinfo shengri是datetime类型,怎么不行
from tb1 left join tb2 on tb1.userid=tb2.userid
a.userid,
CASE WHEN B.userid IS NULL THEN 'NO' ELSE 'YES' END AS IsLogin
FROM table1 AS A
LEFT JOIN table2 AS B
ON A.userid=b.userid
UserID,
IsLongin=case when exists(select 1 from table2 where userid=t.userid)
then 'YES'
else 'NO'
end
from table1 t
1 false
2 true
3 true
4 false
UserID,
IsLongin=case when exists(select 1 from table2 where userid=t.userid)
then true
else false
end
from table1 t
将NO改为FALSE,YES --TRUE
select UserID,case when exists(select 1 from table2 where userid=t.userid) then true else false end AS IsLongin from table1 t
select
UserID,
IsLongin=case when exists(select 1 from table2 where userid=t.userid)
then 'true'
else 'false'
end
from table1 t
CREATE TABLE TBTEST1( UserID INT,UserName VARCHAR(10) )
INSERT TBTEST1SELECT 1, '张三'UNION
SELECT 2, '李四'UNION
SELECT 3, '曹五'UNION
SELECT 4, '赵六' CREATE TABLE TBTEST2(UserID inT ,LonginTime datetime )INSERT TBTEST2
SELECT 2 ,'2009-1-1 12:12:12' UNION
SELECT 3 ,'2009-1-2 13:12:12' --SELECT * FROM TBTEST1
--SELECT * FROM TBTEST2SELECT USERID,(CASE WHEN LonginTime IS NULL THEN 'NO' ELSE 'YES' END )AS ISLONGIN FROM (SELECT T1.USERID,T2.LonginTime FROM TBTEST1 T1 LEFT JOIN TBTEST2 T2 ON T1.USERID=T2.USERID) AS T3
1 NO
2 YES
3 YES
4 NO
CREATE TABLE T1( UserID INT,UserName VARCHAR(10) )
INSERT T1
SELECT 1, '张三'UNION
SELECT 2, '李四'UNION
SELECT 3, '曹五'UNION
SELECT 4, '赵六' CREATE TABLE T2(UserID inT ,LoginTime datetime )
INSERT T2
SELECT 2 ,'2009-1-1 12:12:12' UNION
SELECT 3 ,'2009-1-2 13:12:12' SELECT T1.UserID, case when T2.LoginTime is null then 'false' else 'true' end as IsLongin
FROM T1 LEFT OUTER JOIN
T2 ON T1.UserID = T2.UserIDdrop table T1
drop table T2
select c.UserID,case when c.LonginTime is null then 'NO'
else 'YES' as IsLongin
from (select a.UserID,b.LonginTime from table1 as a left outer join table2 as b on a.UserID=b.UserID ) as c