select count(*) as '共发出批量招聘信息',
case when DATEDIFF(DAY,ADD_DATE,GETDATE())<=AV_DAYS then count(*) end as '正在批量招聘信息',
case when DATEDIFF(DAY,ADD_DATE,GETDATE())>AV_DAYS then count(*) end as '停止批量招聘信息'
from Co_Invite where co_usename='$004'
yyhyy23(yyhyy23) 出错哦~~~~
服务器: 消息 8118,级别 16,状态 1,行 1
列 'Co_Invite.ADD_DATE' 在选择列表中无效,因为该列未包含在聚合函数中,并且没有 GROUP BY 子句。
服务器: 消息 8118,级别 16,状态 1,行 1
列 'Co_Invite.AV_DAYS' 在选择列表中无效,因为该列未包含在聚合函数中,并且没有 GROUP BY 子句。
服务器: 消息 8118,级别 16,状态 1,行 1
列 'Co_Invite.ADD_DATE' 在选择列表中无效,因为该列未包含在聚合函数中,并且没有 GROUP BY 子句。
服务器: 消息 8118,级别 16,状态 1,行 1
列 'Co_Invite.AV_DAYS' 在选择列表中无效,因为该列未包含在聚合函数中,并且没有 GROUP BY 子句。
select count(*) as '共发出批量招聘信息',
case when DATEDIFF(DAY,ADD_DATE,GETDATE())<=AV_DAYS then count(*) end as '正在批量招聘信息',
case when DATEDIFF(DAY,ADD_DATE,GETDATE())>AV_DAYS then count(*) end as '停止批量招聘信息'
from Co_Invite where co_usename='$004'
select count(*) as '共发出批量招聘信息',
when case DATEDIFF(DAY,ADD_DATE,GETDATE())<=AV_DAYS then count(*) end as '正在批量招聘信息',
when case DATEDIFF(DAY,ADD_DATE,GETDATE())>AV_DAYS then count(*) end as '停止批量招聘信息'
from Co_Invite where co_usename='$004'
我这样写
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server 不存在或访问被拒绝
服务器: 消息 53,级别 16,状态 1,行 0
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
select case typeid when 0 then count(*) end as '共发出批量招聘信息',
case typeid when 1 then count(*) end as '正在批量招聘信息',
case typeid when 2 then count(*) end as '停止批量招聘信息'
from yourtable group by typeid
一条记录返回三个结果
select case when typeid=0 then count(*) else 0 end as 共发出批量招聘信息,
when typeid=1 then count(*) else 0 end as 正在批量招聘信息,
when typeid=2 then count(*) else 0 end as 停止批量招聘信息
from YourTable group by typeid
如果要返回三个结果, 这里可能存在一个问题, 如果 "共发出批量招聘信息" 或 "正在批量招聘信息" 或 "停止批量招聘信息"其中有一个没有记录的话, 就不能返回三个记录结果, 但可以通过typeid知道是哪个类型的记录结果:
select typeid,
case when typeid=0 then count(*)
when typeid=1 then count(*)
when typeid=2 then count(*)
end as RecordCount
from YourTable group by typeid
select case typeid when 0 then num end as '共发出批量招聘信息',
case typeid when 1 then num end as '正在批量招聘信息',
case typeid when 2 then num end as '停止批量招聘信息'
from yourtable
select (case typeid when 0 then num end) as '共发出批量招聘信息',(case typeid when 1 then num end) as '正在批量招聘信息',(case typeid when 2 then num end) as '停止批量招聘信息' from (select typeid,count(*) as num from yourtable group by typeid) a
select case when typeid=0 then count(*) else 0 end as TypeID1,
when typeid=1 then count(*) else 0 end as TypeID2,
when typeid=2 then count(*) else 0 end as TypeID3
from YourTable group by typeid
不好意思, 考虑不周, 修改如下:
select case when typeid=0 then count(*) else 0 end as TypeID1,
when typeid=1 then count(*) else 0 end as TypeID2,
when typeid=2 then count(*) else 0 end as TypeID3
from YourTable
SELECT COUNT(*) AS '共发出批量招聘信息',(SELECT COUNT(*) FROM Co_Invite WHERE DATEDIFF(DAY, ADD_DATE, GETDATE()) <= AV_DAYS) AS '正在批量招聘信息',(SELECT COUNT(*) FROM Co_Invite WHERE DATEDIFF(DAY, ADD_DATE, GETDATE()) > AV_DAYS) AS '停止批量招聘信息' FROM Co_Invite