Server 2005中连接2条select count语句的语法
Server 2005中连接2条select count语句的语法
提问于 2013-12-19 22:53:36
对于另一个select查询中的子查询，对同一个表有两个计数查询。我想做的是这样的：
SELECT
Stuff ,
((SELECT CAST(COUNT(*) from cTable WHERE Water = 'wet')) AS VARCHAR) +'/'+
((SELECT CAST(COUNT(*) from cTable WHERE sky = 'blue')) AS VARCHAR) AS StockCount
FROM aTable
WHERE MyData = 'Rocks'并为aTable中的每一项输出大约4/5。
我的困难在于所需嵌套的语法。
首先，我有两个整数，应该从int转换为字符串，然后进行连接，然后为了使事情更简单，当组合查询是子Selects时，as关键字必须在组合查询之外，而Sub-Selects必须在括号中。在重写了10次这份声明之后，我就快疯了
这是我的两个问题。
SELECT
COUNT(*)
FROM
Process_PartsIssued
WHERE
(Process_PartsIssued.OEMPartCode = Part_Catalog.ID
AND TechStockFlag = 'True'
AND PartReturned = 'False'
AND UsedByUser IS NULL)
SELECT
COUNT(*)
FROM
Process_PartsIssued
WHERE
(Process_PartsIssued.OEMPartCode = Part_Catalog.ID
AND PartReturned = 'False'
AND UsedByUser IS NULL)回答 3
Stack Overflow用户
回答已采纳
发布于 2013-12-19 23:06:46
首先，总是在varchar()中使用长度。
第二，您可以使用条件聚合使用单个查询来完成此操作：
SELECT Stuff,
(select (cast(sum(case when water = 'wet' then 1 else 0 end) as varchar(255)) + '/' +
cast(sum(case when sky = 'blue' then 1 else 0 end) as varchar(255))
) as StockCount
FROM aTable
WHERE MyData = 'Rocks';编辑：
对于您的特定查询：
SELECT stuff,
(SELECT cast(sum(case when TechStockFlag = 'True' AND PartReturned = 'False' AND UsedByUser IS NULL then 1 else 0 end) as varchar(255)) + '/' +
cast(sum(case when PartReturned = 'False' AND UsedByUser IS NULL then 1 else 0 end) as varchar(255))
FROM Process_PartsIssue
WHERE Process_PartsIssued.OEMPartCode = Part_Catalog.ID
) as StockCount
. . .Stack Overflow用户
发布于 2013-12-19 23:07:01
你有三个不同的问题：
1)如何根据不按或不按分组进行筛选的标准聚合行？
COUNT(CASE WHEN TechStockFlag = 'True' THEN 1 END)2)如何将数字转换为字符串以进行字符串连接？
LTRIM(STR(number))3)如何引用子查询之外的内容？
SELECT
x,
(SELECT .... FROM TableB WHERE ... = A.y)
FROM TableA A或
SELECT
A.x,
B.y
FROM TableA A
CROSS APPLY (SELECT .... FROM TableB WHERE ... = A.y) B把它放在一起：
SELECT Stuff, Partcount
FROM Part_Catalog
CROSS APPLY (
SELECT
LTRIM(STR(COUNT(CASE WHEN TechStockFlag = 'True' THEN 1 END)))
+ '/'
+ LTRIM(STR(COUNT(*))) PartCount
FROM Process_PartsIssued
WHERE Process_PartsIssued.OEMPartCode = Part_Catalog.ID
AND PartReturned = 'False'
AND UsedByUser IS NULL
) BStack Overflow用户
发布于 2013-12-19 23:07:41
根据您的2个查询，您可以创建如下内容：
SELECT
CAST(SUM(CASE WHEN TechStockFlag = 'True' AND PartReturned = 'False' AND UsedByUser IS NULL THEN 1 ELSE 0 END) AS NVARCHAR(5)) + '/' +
CAST(SUM(CASE WHEN PartReturned = 'False' AND UsedByUser IS NULL THEN 1 ELSE 0 END) AS NVARCHAR(5)) AS 'StockCount'
FROM Process_PartsIssued
INNER JOIN Part_Catalog
ON Process_PartsIssued.OEMPartCode = Part_Catalog.ID页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/20693284
复制相关文章
相似问题
腾讯云开发者
