s_p的组合3查询

Question

select Month(user_lastlogin) as Month,year(user_lastlogin) as Year,
count(*) as 'Total             Reg' from bb_user 
group by Month(user_lastlogin),year(user_lastlogin)
order by  Month desc

select count(*) as 'LR Reg' from bb_user 
where  user_regtype ='LR'
group by Month(user_lastlogin)
order by  Month desc

select count(*) as 'BBR Reg' from bb_user 
where  user_regtype is null OR user_regtype = 'BBR'
group by Month(user_lastlogin)
order by  Month desc

我想显示为月/年/总注册/LR Reg/BBR Reg

我在三个不同的查询中删除结果，但我想在一个查询中编写存储过程....means，我想在第一个查询中添加第二个和第三个查询。

user_lastlogin = RegistrationDatetime

Answer 1

你在找这样的东西吗？

SELECT MONTH(user_lastlogin) AS Month,
       YEAR(user_lastlogin) AS Year,
       COUNT(*) AS 'Total Reg',
       SUM(CASE WHEN user_regtype = 'LR' THEN 1 ELSE 0 END) AS 'LR Reg',
       SUM(CASE WHEN IS NULL OR user_regtype = 'BBR' THEN 1 ELSE 0 END) AS 'BBR Reg'
  FROM bb_user 
GROUP BY MONTH(user_lastlogin), YEAR(user_lastlogin)
ORDER BY Year DESC, Month DESC

Answer 2

你的解释很不清楚，但我认为这是你实际上想要的。

select
  Month(user_lastlogin) as Month,
  year(user_lastlogin) as Year,
  count(*) as [Total Reg],
  SUM(CASE WHEN user_regtype ='LR' THEN 1 END) [LR Reg],
  SUM(CASE WHEN user_regtype is null OR user_regtype = 'BBR THEN 1 END) [BBR Reg]
from bb_user 
group by
  Month(user_lastlogin),
  year(user_lastlogin)
order by  Month desc