我现在有两张表格
icPageInfo_%s_%s和icCarInfo
语句如下:
SELECT COUNT(*) AS Expr1 FROM icPageInfo_%s_%s a LEFT OUTER JOIN icCarInfo b ON a.InfoID = b.InfoID WHERE (b.CarNo = '%s') AND (b.ProductName = '%s') AND (b.ProcedureName = '职称') AND (a.DefectCode = 'A')",cs_proceN,ProductCo,ProductCo,cs_proceN)
注意:cs_proceN和ProductCo是变量赋值。
我现在实现目的查找A出现的次数,依次类推我需要查询相似A,B,C,D.......共32种类型出现次数。
服务器在远端,我该怎么写程序,总不能一次一次查找32次吧
icPageInfo_%s_%s和icCarInfo
语句如下:
SELECT COUNT(*) AS Expr1 FROM icPageInfo_%s_%s a LEFT OUTER JOIN icCarInfo b ON a.InfoID = b.InfoID WHERE (b.CarNo = '%s') AND (b.ProductName = '%s') AND (b.ProcedureName = '职称') AND (a.DefectCode = 'A')",cs_proceN,ProductCo,ProductCo,cs_proceN)
注意:cs_proceN和ProductCo是变量赋值。
我现在实现目的查找A出现的次数,依次类推我需要查询相似A,B,C,D.......共32种类型出现次数。
服务器在远端,我该怎么写程序,总不能一次一次查找32次吧
SELECT
sum(case when a.DefectCode = 'A' then 1 else 0 end)A,
sum(case when a.DefectCode = 'B' then 1 else 0 end)B,
sum(case when a.DefectCode = 'C' then 1 else 0 end)C,
sum(case when a.DefectCode = 'D' then 1 else 0 end)D,
COUNT(*) AS Expr1 FROM icPageInfo_%s_%s a LEFT OUTER JOIN icCarInfo b ON a.InfoID = b.InfoID WHERE (b.CarNo = '%s') AND (b.ProductName = '%s') AND (b.ProcedureName = '职称'),cs_proceN,ProductCo,ProductCo,cs_proceN)
--貌似你还要加上 group by 子句
ON a.InfoID = b.InfoID
WHERE (b.CarNo = '%s') AND (b.ProductName = '%s')
AND (b.ProcedureName = '职称') AND (a.DefectCode in('A','B','C','D')",
cs_proceN,ProductCo,ProductCo,cs_proceN)
group by a.DefectCodein里面写32个字母就可以了
A 20
B 30
C 25
........
这样的格式