我现在有两个表: table1: (用户列表) UserID(int) 人员ID
UserName(nverchar) 姓名 UserID UserName
1 张三
2 李四
3 曹五
4 赵六
5 侯七
table2:(登陆情况) UserID(int) 人员ID
UserType(int) 人员类别ID UserID UserType
2 1
3 2
5 1
2 3我想根据上面的两个表信息,动态生成一个记录集(UserID(int) IsExist(bool))
要求UserType=1 UserID IsExist
1 false
2 true
3 false
4 false
5 true 应该怎样写?我昨天请教,得到一条语句如下:
select a.UserID,(case when b.UserType=1 then 'true' else 'false' end)as IsExist from table1 a left join table2 b on a.UserID =b.UserID
可输出的结果是:
UserID IsExist
1 false
2 true
2 false
3 false
4 false
5 true 显然多出了一条(2 false),请问如何更改上述语句才能得到正确结果?
UserName(nverchar) 姓名 UserID UserName
1 张三
2 李四
3 曹五
4 赵六
5 侯七
table2:(登陆情况) UserID(int) 人员ID
UserType(int) 人员类别ID UserID UserType
2 1
3 2
5 1
2 3我想根据上面的两个表信息,动态生成一个记录集(UserID(int) IsExist(bool))
要求UserType=1 UserID IsExist
1 false
2 true
3 false
4 false
5 true 应该怎样写?我昨天请教,得到一条语句如下:
select a.UserID,(case when b.UserType=1 then 'true' else 'false' end)as IsExist from table1 a left join table2 b on a.UserID =b.UserID
可输出的结果是:
UserID IsExist
1 false
2 true
2 false
3 false
4 false
5 true 显然多出了一条(2 false),请问如何更改上述语句才能得到正确结果?
go
create table tb1([UserID] int,[UserName] varchar(10))
insert tb1 select 1,'张三'
union all select 2,'李四'
union all select 3,'曹五'
union all select 4,'赵六'
union all select 5,'侯七'
go
if object_id('tb2') is not null drop table tb2
go
create table tb2([UserID] int,[UserType] int)
insert tb2 select 2,1
union all select 3,2
union all select 5,1
union all select 2,3
go
select t.UserID,isnull((select 'true' from tb2 where [UserID]=t.[UserID] and UserType=1),'flase') as IsExist from tb1 t
/*
UserID IsExist
----------- -------
1 flas
2 true
3 flas
4 flas
5 true(5 行受影响)
*/
go
create table tb1([UserID] int,[UserName] varchar(10))
insert tb1 select 1,'张三'
union all select 2,'李四'
union all select 3,'曹五'
union all select 4,'赵六'
union all select 5,'侯七'
go
if object_id('tb2') is not null drop table tb2
go
create table tb2([UserID] int,[UserType] int)
insert tb2 select 2,1
union all select 3,2
union all select 5,1
union all select 2,3
go
select t.UserID,isnull((select cast('true' as varchar(10)) from tb2 where [UserID]=t.[UserID] and UserType=1),'false') as IsExist from tb1 t
/*
UserID IsExist
----------- -------
1 flas
2 true
3 flas
4 flas
5 true(5 行受影响)
*/modify
go
create table tb1([UserID] int,[UserName] varchar(10))
insert tb1 select 1,'张三'
union all select 2,'李四'
union all select 3,'曹五'
union all select 4,'赵六'
union all select 5,'侯七'
go
if object_id('tb2') is not null drop table tb2
go
create table tb2([UserID] int,[UserType] int)
insert tb2 select 2,1
union all select 3,2
union all select 5,1
union all select 2,3
go
select a.UserID,case b.[UserType] when 1 then 'true' else 'false' end as IsExist
from tb1 a
left join (select * from tb2 where [UserType]=1) b
on a.userid=b.userid或者这样.
-->> Author: 让你望见影子的墙(HEROWANG)生成测试数据 Date:2009-04-24 08:21:14
IF OBJECT_ID('tb1') IS NOT NULL
DROP TABLE tb1
Go
CREATE TABLE tb1(UserID INT,UserName NVARCHAR(2))
Go
INSERT INTO tb1
SELECT 1,'张三' UNION ALL
SELECT 2,'李四' UNION ALL
SELECT 3,'曹五' UNION ALL
SELECT 4,'赵六' UNION ALL
SELECT 5,'侯七'
GOSELECT * FROM TB1-->> Author: 让你望见影子的墙(HEROWANG)生成测试数据 Date:2009-04-24 08:21:32
IF OBJECT_ID('tb2') IS NOT NULL
DROP TABLE tb2
Go
CREATE TABLE tb2(UserID INT,UserType INT)
Go
INSERT INTO tb2
SELECT 2,1 UNION ALL
SELECT 3,2 UNION ALL
SELECT 5,1 UNION ALL
SELECT 2,3
GOSELECT * FROM TB2select tb1.userid,case when tb2.userid is null then 'false' else 'true' end
from tb1 left join tb2 on tb1.UserID=tb2.UserID and usertype=1userid (无列名)
1 false
2 true
3 false
4 false
5 true
from (select A.UserId,B.UserType from table1 A left outer join table2 B on A.UserId = B.UserId and B.UserType = 1)