SQL案例输出

Question

select to_char(T_12895_DET_ENTERED_DATE,'yyyy-mm') as entered_date, 
CASE
    when T_12916_VIA = 'E'   then 'Internet'
    when T_12916_VIA = 'R'   then 'Store'
    when (T_12916_VIA in ('M','F','P') or T_12916_VIA is null) then 'All Others'
end as VIA_CODE,
count(*)
from cmlbrc.applicants
where to_char(T_12895_DET_ENTERED_DATE,'yyyy') >= '2010'
group by to_char(T_12895_DET_ENTERED_DATE,'yyyy-mm'), T_12916_VIA
order by 1,2;

上面的代码给了我多行作为yyyy-mm的输出。为什么“所有其他”组不排成一行? 2010-05所有其他2782010-05所有其他9752010-05所有其他2232010-05互联网51242010-05商店19641

谢谢，丹

Answer 1

您可以将您的CASE语句移动到您的GROUP BY中，这样可以删除重复的内容：

select to_char(T_12895_DET_ENTERED_DATE,'yyyy-mm') as entered_date, 
   CASE
       when T_12916_VIA = 'E'   then 'Internet'
       when T_12916_VIA = 'R'   then 'Store'
       when (T_12916_VIA in ('M','F','P') or T_12916_VIA is null) then 'All Others'
   end as VIA_CODE,
   count(*)
from cmlbrc.applicants
where to_char(T_12895_DET_ENTERED_DATE,'yyyy') >= '2010'
group by to_char(T_12895_DET_ENTERED_DATE,'yyyy-mm'), 
   CASE
       when T_12916_VIA = 'E'   then 'Internet'
       when T_12916_VIA = 'R'   then 'Store'
       when (T_12916_VIA in ('M','F','P') or T_12916_VIA is null) then 'All Others'
   end
order by 1,2;

Answer 2

请参考以下脚本：

还需要在group by子句中指定case块。

Create Table #Temp1(T_12895_DET_ENTERED_DATE smalldatetime,T_12916_VIA char(1))

insert into #Temp1 ( T_12895_DET_ENTERED_DATE,T_12916_VIA )
Select dateadd( d,id,getdate()), case When a.ID <= 10 Then 'E'
When a.ID <= 20 Then 'R'
When a.ID > 20 Then 'M' End
  from Tally As a
Where a.ID < 30
Order by a.ID 

Select * from #Temp1

select Year(T_12895_DET_ENTERED_DATE) as entered_date, 
CASE
    when T_12916_VIA = 'E'   then 'Internet'
    when T_12916_VIA = 'R'   then 'Store'
    when (T_12916_VIA in ('M','F','P') or T_12916_VIA is null) then 'All Others'
end as VIA_CODE,
count(*)
from #Temp1
group by Year(T_12895_DET_ENTERED_DATE) , 
CASE
    when T_12916_VIA = 'E'   then 'Internet'
    when T_12916_VIA = 'R'   then 'Store'
    when (T_12916_VIA in ('M','F','P') or T_12916_VIA is null) then 'All Others'
end 
order by 1,2