检索时出现SQL QUERY错误 [英] SQL QUERY Error while retrieving

问题描述

select

tbl_login.userid,
tbl_login.name as Employee,

(Case  when Exists
(Select * from tbl_attendance where tbl_attendance.userid=tbl_login.userid and tbl_attendance.firstlogin is not null  and tbl_attendance.date='2011/03/25') then 'Present' else 'Absent' end) as Status,

(select top 1 coalesce(tbl_timesheet.description,null) as Filename,coalesce(LTRIM(Right(Convert(varchar(19),tbl_timesheet.starttime,100),7)),null) as StartTime from tbl_timesheet where tbl_timesheet.userid=tbl_login.userid and date='2011/03/25' and stoptime is null)

from tbl_login where tbl_login.privilege<>'ADMIN'

这是我的查询，显示错误

This is my query,its showing an error

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Cna任何人都可以帮助我

Cna anyone help me

推荐答案

select

tbl_login.userid,
tbl_login.name as Employee,

(Case  when Exists
(Select * from tbl_attendance where tbl_attendance.userid=tbl_login.userid is not null and tbl_attendance.firstlogin is not null  and tbl_attendance.date=''2011/03/25'') then ''Present'' else ''Absent'' end) as Status,
(select top 1 coalesce(tbl_timesheet.description,null) as Filename,coalesce(LTRIM(Right(Convert(varchar(19),tbl_timesheet.starttime,100),7)),null) as StartTime from tbl_timesheet where tbl_timesheet.userid=tbl_login.userid and date=''2011/03/25'' and stoptime is null)

from tbl_login where tbl_login.privilege<>''ADMIN''

以此替换您的代码，它应该可以工作.

尝试让我知道.

Replace your code with this and it should work.

Try and let me know.

阅读:
MSDN:子查询规则 [使用示例进行解释 [

Read this:
MSDN: Subquery Rules[^]

For full detail of the error:
Explained with example[^]

尝试

select  tbl_login.userid,
        tbl_login.name as Employee,
        (
        Case
            when Exists (
                        Select  *
                        from    tbl_attendance
                        where   tbl_attendance.userid=tbl_login.userid
                                and
                                tbl_attendance.firstlogin is not null
                                and
                                tbl_attendance.date='2011/03/25'
                        )
            then 'Present'
            else 'Absent'
        end
        ) as Status,
        (
        select  top 1
                coalesce(tbl_timesheet.description,null)
        from    tbl_timesheet
        where   tbl_timesheet.userid=tbl_login.userid
                and
                date='2011/03/25'
                and
                stoptime is null
        ) as Filename,
        (
        select  top 1
                coalesce(LTRIM(Right(Convert(varchar(19),tbl_timesheet.starttime,100),7)),null)
        from    tbl_timesheet
        where   tbl_timesheet.userid=tbl_login.userid
                and
                date='2011/03/25'
                and
                stoptime is null
        ) as StartTime
from    tbl_login
where   tbl_login.privilege<>'ADMIN'

请注意(select top 1 coalesce(tbl_timesheet.description,null) ...分为两部分.

Note the (select top 1 coalesce(tbl_timesheet.description,null) ... is broken into two.

这篇关于检索时出现SQL QUERY错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！