UserAnswer中ID userID Type UserMark
————————————————————————————————
1 101 A null
2 101 A 2
3 102 A 3
4 102 A 2
————————————————————————————————
想要得到的结果:如果useID的UserMark中只要有一个为null,则State为未评阅' ,怎么写啊?我的sql:
SELECT DISTINCT
userID,
Type,
'State' = CASE WHEN UserMark is NULL THEN '未评阅' ELSE '已评阅' END
FROM
UserAnswer
WHERE
Type='A'
-----------------------
此时得到3条数据,
101 A 未评阅
101 A 已评阅 //这条数据不我不想要,该怎么处理?
102 A 已评阅
————————————————————————————————
1 101 A null
2 101 A 2
3 102 A 3
4 102 A 2
————————————————————————————————
想要得到的结果:如果useID的UserMark中只要有一个为null,则State为未评阅' ,怎么写啊?我的sql:
SELECT DISTINCT
userID,
Type,
'State' = CASE WHEN UserMark is NULL THEN '未评阅' ELSE '已评阅' END
FROM
UserAnswer
WHERE
Type='A'
-----------------------
此时得到3条数据,
101 A 未评阅
101 A 已评阅 //这条数据不我不想要,该怎么处理?
102 A 已评阅
解决方案 »
- 一星期没解决的问题.怎么使用公式关联查询?
- microsoft visual foxpro sql中如何写case when?
- mysql 怎么建索引优化
- 这个SQL语句错在哪里
- 请问大家 一个sql server 2000企业版的表最多能插入多少行的数据??up送分 不够在加
- 快来拿分一个SQL SERVER2000 标准版的安装问题!、、、、、100分
- 如何让重复记录只显示一条,并把重复记录的一个字段相加
- 我装上sqlsever2000为什么连不上本地机
- oracle中用pl/sql实现文件的上传和下载问题!
- 要实现如下的功能,如何编写SQL语句
- 数据库转换问题
- 新手上路,求一个SELECT语句
userID,
[Type],
State = CASE WHEN exists(select 1 from UserAnswer b where b.userID = a.userID and UserMark is null) THEN '未评阅' ELSE '已评阅' END
FROM UserAnswer a
WHERE Type='A'
userID,
Type,
'State' = CASE when (select COUNT(*) from UserAnswer where userID=a.userID and UserMark IS null )>0 THEN '未评阅' ELSE '已评阅' END
FROM
UserAnswer a
WHERE
Type='A'
select distinct userID , Type , State = case when useID is null or UserMark is null then '未评阅' else '已评阅' end from UserAnswer
select '1','101','A',null union all
select '2','101','A',2 union all
select '3','102','A',3 union all
select '4','102','A',2 --code
SELECT
userID,
Type,
'State' = min(CASE WHEN UserMark is NULL THEN '未评阅' ELSE '已评阅' END)
FROM
@UserAnswer
WHERE
Type='A'
group by userID,Type
insert into tb values(1 , 101 , 'A' , null)
insert into tb values(2 , 101 , 'A' , 2 )
insert into tb values(3 , 102 , 'A' , 3 )
insert into tb values(4 , 102 , 'A' , 2 )
goselect distinct userID , Type , State = '未评阅' from tb WHERE Type='A' and (userID is null or UserMark is null)
union all
select distinct userID , Type , State = '已评阅' from tb WHERE Type='A' and (userID is not null and UserMark is not null) and userid not in (select distinct userID from tb WHERE Type='A' and (userID is null or UserMark is null))
order by useriddrop table tb/*
userID Type State
----------- ---------- ------
101 A 未评阅
102 A 已评阅(所影响的行数为 2 行)*/
userID,
Type,
'State' = CASE WHEN (UserMark is NULL or uerid is null) THEN '未评阅' ELSE '已评阅' END
FROM
UserAnswer
WHERE
Type='A'