不带列且不使用枢轴的内连接?

Question

在下面的查询中，我有三个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 ??

Answer 1

您可以使用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;