漏了!应该是这样Select a.id,a.name,count(b.id),count(c.id),count(d.id) from info_Sort a
left join Info_List b CHARINDEX(', '+ CAST(a.ID AS varchar) +',',', '+ b.Sort +',') > 0
left join Info_List c CHARINDEX(', '+ CAST(a.ID AS varchar) +',',', '+ c.Sort +',') > 0 and DateDiff(day,c.AddDate,getdate())=0
left join Info_List d CHARINDEX(', '+ CAST(a.ID AS varchar) +',',', '+ d.Sort +',') > 0 and d.State=1
group by a.ID,a.Name
left join Info_List b CHARINDEX(', '+ CAST(a.ID AS varchar) +',',', '+ b.Sort +',') > 0
left join Info_List c CHARINDEX(', '+ CAST(a.ID AS varchar) +',',', '+ c.Sort +',') > 0 and DateDiff(day,c.AddDate,getdate())=0
left join Info_List d CHARINDEX(', '+ CAST(a.ID AS varchar) +',',', '+ d.Sort +',') > 0 and d.State=1
group by a.ID,a.Name
解决方案 »
- 求解一条SQL语句
- 看起来比较简单的排序问题?
- 如何将一个已有的列的值改为NULL?
- Hi, 为什么@total值不会变化, volume_percent与cumlative_percent值一模一样
- 这组语句哪错了?为啥运行不成
- 视图和表的问题 急求 请高手解救 谢谢。
- 用alter语句为表加主键.改字段的数据类型.
- 紧急求救:关于修改MSSQL7表中的数据
- bulk insert 语句
- DB2的控製中心启动時出錯"[IBM][JDBC DRIVER]CLI0616E開啟SOCKET時發生錯誤SQL STATE=08S01",請問如何解決???
- 求sql句子,谢谢
- delete语句里怎样嵌套select???
select a.id,a.name
,记录总数=count(*)
,当天记录数=sum(case datediff(day,b.AddDate,getdate()) when 0 then 1 else 0 end)
,[State=1的记录数]=sum(case b.state when 1 then 1 else 0 end)
from info_Sort a left join Info_List b
on charindex(','+cast(a.id as varchar)+','
,','+b.sort+',')>0
group by a.id,a.name
order by a.id,a.name
create table info_Sort(id int,name varchar(10))
insert info_Sort select 1,'分类一'
union all select 2,'分类二'
union all select 3,'分类三'
union all select 4,'分类四'create table Info_List(id int,sort varchar(10),AddDate datetime,state bit)
insert Info_List select 1,'1,4' ,'2004-7-13 09:18:00',1
union all select 2,'2' ,'2004-7-13 09:18:00',0
union all select 3,'3,4' ,'2004-7-12 09:18:00',0
union all select 4,'1,2' ,'2004-7-11 09:18:00',1
union all select 5,'1,4' ,'2004-7-13 09:18:00',0
union all select 6,'2,3,4','2004-7-13 09:18:00',1
go--查询
select a.id,a.name
,记录总数=count(*)
,当天记录数=sum(case datediff(day,b.AddDate,getdate()) when 0 then 1 else 0 end)
,[State=1的记录数]=sum(case b.state when 1 then 1 else 0 end)
from info_Sort a left join Info_List b
on charindex(','+cast(a.id as varchar)+','
,','+b.sort+',')>0
group by a.id,a.name
order by a.id,a.name
go--删除测试
drop table info_Sort,Info_List/*--测试结果id name 记录总数 当天记录数 State=1的记录数
----------- ---------- ----------- ----------- -----------
1 分类一 3 0 2
2 分类二 3 0 2
3 分类三 2 0 1
4 分类四 4 0 2(所影响的行数为 4 行)--*/