我需要創建一個表格，該表格是每個日期期間客戶組的結果。他們沒有任何關系。
所以我有 36 個月和 10 個客戶。我想要的輸出是每個客戶 36 個月的單個表，這意味著我最終應該有 360 行和兩列。
-- CREATE A TABLE of DATES - will produce 36 rows x 1 col
DECLARE @StartDate DATE = '20210701',
@EndDate DATE = '20240630';
WITH cte
AS (SELECT @StartDate AS myDate
UNION ALL
SELECT Dateadd(month, 1, mydate) AS myDate
FROM cte
WHERE Dateadd(month, 1, mydate) <= @EndDate),
dates
AS (SELECT Eomonth(mydate) mydate
FROM cte),
activities
AS (SELECT 'whatever' as Whatever)
SELECT
mydate
FROM dates
獲取客戶串列。
-- this will produce 1 column of 10 rows.
select CustomerName from Customers where Region =3;
我需要的最終結果
| 我的約會 | 顧客 |
|---|---|
| 2022-04-30 | 客戶 1 |
| 2022-05-31 | 客戶 1 |
| 2022-06-30 | 客戶 1 |
| ...... | ………… |
| 2022-04-30 | 客戶 2 |
| 2022-05-31 | 客戶 2 |
| 2022-06-30 | 客戶 2 |
如何在 SQL Server v11 上執行此操作？
uj5u.com熱心網友回復：
交叉連接匹配來自兩個輸入的所有行，從而得到所有行的乘積，例如
select *
from dates
cross join (
select 'customer1' Customer union all
select 'customer2' Customer union all
select 'customer3' -- etc
)Customers
order by customer;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/441796.html
標籤：sql服务器
上一篇：如何在SQL中獲取可用時間串列？
下一篇：while回圈函式聯合函式結束