declare @tb table( BarCode varchar (100), Smodel varchar (100), SproUnit varchar (100), SproName varchar (100), SexaTime varchar (100), SexaQuality varchar (100), SquaQuantity varchar (100), SsexaPassrate varchar (100), S varchar (100), SendName varchar (100), SendAamin varchar (100), SendTime varchar (100), IsExam int, IsToData int, DataMessage varchar (100), OverlayMessage varchar (100), ExamTime varchar (100), Re varchar (100) ) insert into @tb select 'Sqlserver','sser','12','13','2011-10-16','1','1','1','1','乱001 ','老谢','2011-01-2',0,0,'0','0','1990-10-11','11' insert into @tb select '天1','001','11','11','2011-10-15','1','1','1','1','乱001 ','老谢','2011-01-2',0,0,'0','0','1990-10-11','11' insert into @tb select 'Sqlserver2','sser2','12','13','2011-10-16','1','1','1','1','乱001 ','老谢','2011-01-2','0','0','0','0','1990-10-11','11' insert into @tb select '天2','002','11','11','2011-10-15','1','1','1','1','乱001 ','老谢','2011-01-2','1','0','0','0','1990-10-11','11' select * from @tbselect sendName,sendAamin,SendTime,count(*) '报送条数',sum(IsExam) IsExam,sum(IsToData) IsToData from @tb group by sendName,sendAamin,SendTime
非哥,太谢谢你了你的这条sql很不错,请看一下我写的sql语句 select t1.sendName,t1.sendAamin,t1.sendTime,t1.isExam,t2.isToData,t3.countlm from (select sendName,sendAamin,sendTime,count(*) isExam from TempIMcpjyql where isExam=1 group by sendName,sendAamin,sendTime) t1 full join (select sendName,sendAamin,sendTime,count(*) isToData from TempIMcpjyql where isToData=1 group by sendName,sendAamin,sendTime) t2 on t1.sendName=t2.sendName and t1.sendAamin=t2.sendAamin and t1.sendTime = t2.sendTime full join (select sendName,sendAamin,sendTime,count(*) countlm from TempIMcpjyql group by sendName,sendAamin,sendTime) t3 on t1.sendName=t3.sendName and t1.sendAamin=t3.sendAamin and t1.sendTime = t3.sendTime 这条sql语句当IsExam【审核】全为0的时候 查出来sendName,sendAamin,SendTime,IsExam,IsToData,count(*)都为空, 怎样解决IsExam【审核】全为0或不全是0和IsToData【入库】全为0或不全是0都能查出来这些值【sendName,sendAamin,SendTime,IsExam,IsToData,count(*)】呢? 谢谢了
where isExam=1 where isToData=1 你已经限定条件了
如题://SELECT COUNT(*)AS COUNTID_1,报送名称,报送人员 FROM TABEL_NAME WHERE '已审核' GROUP BY COUNTID,报送名称,报送人员;
然后统计出这一组中的报送名称,报送人员,时间,报送条数(这一组有多少条count(*))
审核条数(isExam),入库条数(isToData)
像这些数据中的[乱001 ','老谢','2011-01-2']相同我就想把这多条数据【归成一组】 同时把这一组中的【报送名称,报送人员,时间,报送条数(这一组有多少条count(*))
审核条数(isExam),入库条数(isToData)】查出来
对了:这几个字段中的两个0【'乱001 ','老谢','2011-01-2',0,0】
第一个0是【审核】,第二个0是【入库】
请教了
SELECT COUNT(*) FROM 表 WHERE 审核=0 Group by 时间 --就可以了吧
S,SendName,SendAamin,SendTime,IsExam,IsToData,DataMessage,OverlayMessage,ExamTime,Re
就这些
declare @tb table(
BarCode varchar (100),
Smodel varchar (100),
SproUnit varchar (100),
SproName varchar (100),
SexaTime varchar (100),
SexaQuality varchar (100),
SquaQuantity varchar (100),
SsexaPassrate varchar (100),
S varchar (100),
SendName varchar (100),
SendAamin varchar (100),
SendTime varchar (100),
IsExam int,
IsToData int,
DataMessage varchar (100),
OverlayMessage varchar (100),
ExamTime varchar (100),
Re varchar (100)
)
insert into @tb select 'Sqlserver','sser','12','13','2011-10-16','1','1','1','1','乱001 ','老谢','2011-01-2',0,0,'0','0','1990-10-11','11' insert into @tb select '天1','001','11','11','2011-10-15','1','1','1','1','乱001 ','老谢','2011-01-2',0,0,'0','0','1990-10-11','11' insert into @tb select 'Sqlserver2','sser2','12','13','2011-10-16','1','1','1','1','乱001 ','老谢','2011-01-2','0','0','0','0','1990-10-11','11' insert into @tb select '天2','002','11','11','2011-10-15','1','1','1','1','乱001 ','老谢','2011-01-2','1','0','0','0','1990-10-11','11' select * from @tbselect sendName,sendAamin,SendTime,count(*) '报送条数',sum(IsExam) IsExam,sum(IsToData) IsToData from @tb group by sendName,sendAamin,SendTime
select t1.sendName,t1.sendAamin,t1.sendTime,t1.isExam,t2.isToData,t3.countlm from
(select sendName,sendAamin,sendTime,count(*) isExam from TempIMcpjyql where isExam=1
group by sendName,sendAamin,sendTime) t1
full join
(select sendName,sendAamin,sendTime,count(*) isToData from TempIMcpjyql where isToData=1
group by sendName,sendAamin,sendTime) t2
on t1.sendName=t2.sendName and t1.sendAamin=t2.sendAamin and t1.sendTime = t2.sendTime
full join
(select sendName,sendAamin,sendTime,count(*) countlm from TempIMcpjyql
group by sendName,sendAamin,sendTime) t3
on t1.sendName=t3.sendName and t1.sendAamin=t3.sendAamin and t1.sendTime = t3.sendTime
这条sql语句当IsExam【审核】全为0的时候
查出来sendName,sendAamin,SendTime,IsExam,IsToData,count(*)都为空,
怎样解决IsExam【审核】全为0或不全是0和IsToData【入库】全为0或不全是0都能查出来这些值【sendName,sendAamin,SendTime,IsExam,IsToData,count(*)】呢?
谢谢了
如题://SELECT COUNT(*)AS COUNTID_1,报送名称,报送人员 FROM TABEL_NAME WHERE '已审核' GROUP BY COUNTID,报送名称,报送人员;