不使用列和不使用pivot的内部联接？
不使用列和不使用pivot的内部联接？
提问于 2021-08-19 15:09:19
在下面的查询中，我有三个cte，每个总是显示一行。
有没有一种简单的方法可以水平显示每个值，而不必为inner join使用group by weeknum
我也不喜欢使用pivot运算符或任何其他会使简单查询变得不必要地复杂的操作。
;with cteSales as
(
select count(*) as 'Total' from sales where weeknum = 33
),
cteVendors as
(
select count(*) as 'vendors' from Vendors where weeknum = 33
),
cteClients as
(
select count(*) as 'clients' from Clients where weeknum = 33
)
select total, vendors, clients from
cteSales inner join cteVendors on ??
inner join cteClients on ??回答 1
Stack Overflow用户
回答已采纳
发布于 2021-08-19 15:12:02
你可以只使用3个子查询：
SELECT (SELECT COUNT(*) FROM dbo.sales WHERE weeknum = 33) AS Total,
(SELECT COUNT(*) FROM dbo.Vendors WHERE weeknum = 33) AS Vendors,
(SELECT COUNT(*) FROM dbo.Clients WHERE weeknum = 33) AS Clients;如果必须使用CTE，请使用CROSS JOIN
--; is a statement TERMINATOR, it goes at the end of ALL your statements,
-- not statement that require the previous statement to be properly terminated.
WITH cteSales AS
(SELECT COUNT(*) AS Total
FROM sales
WHERE weeknum = 33),
cteVendors AS
(SELECT COUNT(*) AS vendors
FROM Vendors
WHERE weeknum = 33),
cteClients AS
(SELECT COUNT(*) AS clients
FROM Clients
WHERE weeknum = 33)
SELECT S.Total,
V.vendors,
C.clients
FROM cteSales S
CROSS JOIN cteVendors V
CROSS JOIN cteClients C;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/68850369
复制相关文章
相似问题
腾讯云开发者
