select distinct 'CSP','PU4','BOI',a.line,a.cust_model,'20061009','D',substring(c.comp_format,5,2),
count(case when a.errcode='PASS' and a.station='FFT' then a.serial_number else null end) as PassQty,
count(case when a.station='CSP'then a.serial_number else null end) as CSPQty
from pqc a,[172.26.40.20].Report.dbo.FQA_Err_Information b,comp_format c
WHERE a.transdatetime between '20061009200000' and '20061010080000' and a.cust_model=c.qci_model and c.category='MB'
group by a.line,a.cust_model,substring(c.comp_format,5,2)
order by a.line ,a.cust_model,substring(c.comp_format,5,2)
我在计算count的时候,为什么comp_format起了作用,得到了不是我想要的结果
我要在这个table里面抓东西,又不能把这个table去掉
请教高手,如何解决?
count(case when a.errcode='PASS' and a.station='FFT' then a.serial_number else null end) as PassQty,
count(case when a.station='CSP'then a.serial_number else null end) as CSPQty
from pqc a,[172.26.40.20].Report.dbo.FQA_Err_Information b,comp_format c
WHERE a.transdatetime between '20061009200000' and '20061010080000' and a.cust_model=c.qci_model and c.category='MB'
group by a.line,a.cust_model,substring(c.comp_format,5,2)
order by a.line ,a.cust_model,substring(c.comp_format,5,2)
我在计算count的时候,为什么comp_format起了作用,得到了不是我想要的结果
我要在这个table里面抓东西,又不能把这个table去掉
请教高手,如何解决?
解决方案 »
- 求一sql语句
- 怎么查询中产生固定格式的序号,在线结贴...
- 如何合并三条记录的内容成为一条记录
- SQL SERVER 和BORLAND JBUIDER 会不会冲突引起连接服务器问题
- sp_oamethod执行报错
- 请问我的access数据库,怎么老是提示我无法为更新行集定位,一些值可能已在最后读取后改变,这应该和SQL SERVER类似原理的,我以前也在SQ
- 一个有关数据库恢复的小问题
- 从EXCEL表里读入SQL中表的问题
- 存储过程中将一个“;”分隔的字符串分拆称多条数据?
- 请教Sql Server高手,如何管理SqlServer的安全问题?
- 如何给SQL SERVER 2000 备份?!~`
- 菜鸟提问,高手来啊
select 'CSP','PU4','BOI',a.line,a.cust_model,'20061009','D',substring(c.comp_format,5,2),
sum(case when a.errcode='PASS' and a.station='FFT' then 1 else 0 end) as PassQty,
sum(case when a.station='CSP' then 1 else 0 end) as CSPQty
from pqc a,[172.26.40.20].Report.dbo.FQA_Err_Information b,comp_format c
WHERE a.transdatetime between '20061009200000' and '20061010080000' and a.cust_model=c.qci_model and c.category='MB'
group by a.line,a.cust_model,substring(c.comp_format,5,2)
order by a.line ,a.cust_model,substring(c.comp_format,5,2)
count(case when a.errcode='PASS' and a.station='FFT' then a.serial_number else null end) as PassQty,
--*************************
count(case when a.station='CSP'then a.serial_number else null end) as CSPQty
这一句是不是没有统计出含null的数据。改成:
count(case when a.station='CSP'then a.serial_number else "" end) as CSPQty
试试看。
--*************************
from pqc a,[172.26.40.20].Report.dbo.FQA_Err_Information b,comp_format c
WHERE a.transdatetime between '20061009200000' and '20061010080000' and a.cust_model=c.qci_model and c.category='MB'
group by a.line,a.cust_model,substring(c.comp_format,5,2)
order by a.line ,a.cust_model,substring(c.comp_format,5,2)
count(case when a.errcode='PASS' and a.station='FFT' then a.serial_number else null end)
我没有让a和c有link关系,但c还是起作用了
在SQL Server 高手的大海中小心的行走
==================================
declare @n int,@m int
set @n=(select count(serial_number) from pqc where errcode='PASS' and station='FFT')
set @m=(select count(serial_number) from pqc where station='CSP')select distinct 'CSP','PU4','BOI',a.line,a.cust_model,'20061009','D',substring(c.comp_format,5,2),
@n as PassQty,
@m as CSPQty
from pqc a,[172.26.40.20].Report.dbo.FQA_Err_Information b,comp_format c
WHERE a.transdatetime between '20061009200000' and '20061010080000' and a.cust_model=c.qci_model and c.category='MB'
group by a.line,a.cust_model,substring(c.comp_format,5,2)
order by a.line ,a.cust_model,substring(c.comp_format,5,2)