Sql按多個子句分組

我有這個資料

UserId   EntryDate       OutDate
100       1.1.2022      3.1.2022
100       2.1.2022      4.1.2022  
101       3.1.2022         -
101       4.1.2022      4.1.2022  
101       5.1.2022      5.1.2022  

我需要這個輸出

DateInfo     EntryCount     OutCount
 01.2022         1             0
 02.2022         1             0
 03.2022         1             1
 04.2022         1             2 
 05.2022         1             1

我使用 MM.dd.yyyy 格式作為默認的 mssql 格式。我用多列分組，但結果不正確。你能幫我了解一下sql查詢嗎

uj5u.com熱心網友回復：

您需要一個日歷表來獲取每個時期的統計資訊。然后，您可以使用條件聚合。

為 100 個月條目創建示例日歷表

create table calendar (
   d       date not null,
   d_year  int  not null,
   d_month int  not null);
with t0(n) as (
 select n 
 from (
    values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
    ) t(n)
),ns as(
   select row_number() over(order by t1.n) - 1 n
   from t0 t1, t0 t2
)
insert calendar(d, d_year, d_month)
select DATEADD(month,n,'2022-01-01' ), year(DATEADD(month,n,'2022-01-01')), month( DATEADD(month,n,'2022-01-01'))
from ns;

查詢，假設EntryDate和OutDate列是date型別

select d_year, d_month,
    count(case when year(EntryDate) = d_year and month(EntryDate) = d_month then 1 end) EntryCnt,
    count(case when year(OutDate) = d_year and month(OutDate) = d_month then 1 end) OutCnt
from calendar
left join tbl on year(EntryDate) = d_year and month(EntryDate) = d_month 
               or year(OutDate) = d_year and month(OutDate) = d_month
group by d_year, d_month
order by d_year, d_month;

uj5u.com熱心網友回復：

我們可以使用 2 個查詢UNION ALL來提取傳入和去向，然后COUNT()呈現GROUP BY 資訊。

create table EntryOut(
UserId  int, 
EntryDate  varchar(10),     
OutDate varchar(10));

insert into EntryOut values
(100,'1.1.2022','3.1.2022'),
(100,'2.1.2022','4.1.2022'),
(101,'3.1.2022',null),  
(101,'4.1.2022','4.1.2022'),  
(101,'5.1.2022','5.1.2022');  

with movements as
(select EntryDate, UserID IDin, null IDout from EntryOut 
union all 
select OutDate, null, UserID from EntryOut )
select
  EntryDate,
  count(IDin) as In_,
  count(Idout) as Out_
from movements
where EntryDate is not null
group by EntryDate;

入職日期 | 在_ | 出去_
:-------- | --: | ---:
1.1.2022 | 1 | 0
2.1.2022 | 1 | 0
3.1.2022 | 1 | 1
4.1.2022 | 1 | 2
5.1.2022 | 1 | 1

db<>在這里擺弄

uj5u.com熱心網友回復：

首先，正如我們許多人所說，您的日期格式不正確。其次，您需要每月匯總以檢查是否有條目。最后，你可以總結一下。

嘗試這個：

WITH tab123
     AS (SELECT entrydate,
                outdate,
                CASE
                  WHEN entrydate IS NOT NULL THEN Dense_rank()
                                                    OVER(
                                                      partition BY entrydate
                                                      ORDER BY entrydate DESC)
                  ELSE NULL
                END e,
                CASE
                  WHEN outdate IS NOT NULL THEN Dense_rank()
                                                  OVER(
                                                    partition BY outdate
                                                    ORDER BY outdate DESC)
                  ELSE NULL
                END o
         FROM   test),
     entrytab
     AS (SELECT Concat(Concat(Month(entrydate), '.'), Year(entrydate)) tab1,
                Sum(e)                                                 e
         FROM   tab123
         GROUP  BY Concat(Concat(Month(entrydate), '.'), Year(entrydate))),
     outtab
     AS (SELECT Concat(Concat(Month(outdate), '.'), Year(outdate)) tab2,
                Sum(o)                                             o
         FROM   tab123
         GROUP  BY Concat(Concat(Month(outdate), '.'), Year(outdate)))
SELECT e.tab1,
       e.e,
       o.o
FROM   entrytab e
       FULL OUTER JOIN outtab o
                    ON e.tab1 = o.tab2
ORDER  BY 1;

DB小提琴：

uj5u.com熱心網友回復：

我認為以下查詢可能會導致您想要做的事情。

with table1 as (select UserID,EntryDate,OutDate from EntryTable),
table2 as (select count(UserID) as EntryCount, EntryDate  from table1 group 
by EntryDate),
table3 as (select count(UserID) as OutCount, EntryDate from table1 group by 
OutCount)
select table1.EntryDate,table2.EntryCount,table3.OutCount from table1
left join table2 on table2.EntryDate = table1.EntryDate
left join table3 on table3.EntryDate = table1.EntryDate

標籤：sql sql服务器 分组

上一篇：SQL-按事件/日期在同一個表中查找/匹配“對”

下一篇：在新列中顯示包含資訊的列