回傳缺少對應型別的0個值

我需要一個選擇查詢來獲取訪問量、訪問型別以對其進行排序以及發生日期。

我的問題是在沒有訪問的時候我沒有得到 0。

我正在使用這段代碼：

        SELECT COUNT(*) AS numberOfVisits,
               CONVERT(DATE, CONVERT(VARCHAR(10), CAST(dateOfVisit AS DATE), 121)) AS typeOfVisit,
               typeOfVisit
        FROM WEB
        WHERE dateOfVisit >= '1-1-2021'
        AND dateOfVisit <= '1-31-2021'
        GROUP BY CAST(dateOfVisit AS DATE), typeOfVisit
        ORDER BY CAST(dateOfVisit AS DATE)

/*i am converting and casting the date for other reasons that i need in 
my API/frontend and i also get the condition of date from api 1-1-2021 
and 1-31-2021 are just for sample*/

有了這個我得到以下結果：https : //imgur.com/a/o3c9A2p

列如下：numberOfVisits | 訪問日期 | 訪問型別

如您所見，我有 4 種型別。前 3 行包含型別 2,3,4 但不是 1；我想在這里有一個額外的行，值為 0，相同的日期和型別 1。

實際值 0 也沒有寫入資料庫，也不是 null

期望的結果是這樣的：

0   2021-01-04  1
1   2021-01-04  2
10  2021-01-04  3
2   2021-01-04  4

如果結果只顯示有型別 2 的訪問，那么它將是這樣的：

0   2021-01-04  1
1   2021-01-04  2
0   2021-01-04  3
0   2021-01-04  4

uj5u.com熱心網友回復：

如果沒有日期的表記錄，則無法計算。您需要有每個日期的日歷表才能開始選擇，然后離開加入您的訪問表并計數。

它會是這樣的：

if OBJECT_ID('tempdb..#Calendar') is not null drop table #Calendar

create table #Calendar (adDate date)

declare @dDate date = '2021-01-01'
while @dDate <= '2021-01-31'
begin
    insert into #Calendar (adDate)
    select @dDate

    set @dDate = dateadd(DAY, 1, @dDate)
end

select
    c.adDate,
    w.typeOfVisit,
    COUNT(w.visit) as VisitCount

from #Calendar c
left join web w on w.dateOfVisit = c.adDate
group by 
    c.adDate,
    w.typeOfVisit
order by
    c.adDate

如果您還想按型別進行 0 次訪問，而不僅僅是按天訪問，那么您可以這樣做：

if OBJECT_ID('tempdb..#Calendar') is not null drop table #Calendar

create table #Calendar (adDate date, anTypeOfVisit int)

declare 
    @dDate date = '2021-01-01',
    @nTypeOfVisit int
while @dDate <= '2021-01-31'
begin
    set @nTypeOfVisit = 1
    while @nTypeOfVisit <=4
    begin
        insert into #Calendar (adDate, anTypeOfVisit)
        select @dDate, @nTypeOfVisit

        set @nTypeOfVisit = @nTypeOfVisit   1
    end

    set @dDate = dateadd(DAY, 1, @dDate)
end

select
    c.adDate,
    c.anTypeOfVisit,
    COUNT(w.dateofvisit) as VisitCount

from #Calendar c
left join web w on w.dateOfVisit = c.adDate and w.typeofvisit = c.anTypeOfVisit

group by 
    c.adDate,
    c.anTypeOfVisit
order by
    c.adDate,
    c.anTypeOfVisit

uj5u.com熱心網友回復：

您需要為每個日期的每個訪問型別創建一個包含一行的表。然后進行左連接以從您的實際資料中引入計數

DROP TABLE IF EXISTS #Visit 
CREATE TABLE #Visit (ID INT IDENTITY(1,1) ,DateOfVisit Date,TypeOfVisit int)
INSERT INTO #Visit
VALUES ('2021-01-04',2),('2021-01-04',3),('2021-01-04',3),('2021-01-04',4);

WITH cte_DistinctVisitDate AS (
    SELECT DateOfVisit
    FROM #Visit
    WHERE DateOfVisit BETWEEN '2020-01-04' AND '2021-01-04' /*Always use YYYY-MM-DD for params as it is culture-agnostic*/
    GROUP BY DateOfVisit
),
cte_TypePerDate AS (
    /*One row for each type on each visit date*/
    SELECT DateOfVisit,TypeOfVisit
    FROM cte_DistinctVisitDate
    CROSS JOIN (VALUES (1),(2),(3),(4)) AS B(TypeOfVisit) /*I am hard coding raw data, but you should use lookup table that contains each visit type*/
),
cte_VisitCount  AS (
    /*Actual counts, but only lists visit types with visits*/
    SELECT DateOfVisit,TypeOfVisit,VisitCnt = COUNT(*)
    FROM #Visit
    GROUP BY DateOfVisit,TypeOfVisit
)

SELECT A.DateOfVisit,A.TypeOfVisit,VisitCnt = ISNULL(B.VisitCnt,0)
FROM cte_TypePerDate AS A
LEFT JOIN cte_VisitCount AS B
    ON A.DateOfVisit = B.DateOfVisit
    AND A.TypeOfVisit = B.TypeOfVisit

標籤：

上一篇：請幫忙除錯C程式

下一篇：用于window.matchMedia更改事件物件的Typescript型別