SQL Server...两个IF条件的Union all

Question

我有两个表值函数，我需要做一些选择...我试着在那个选择上做联合，但我不能对我的IF做“联合所有”...它应该返回一行和两列，每个if条件...此代码应返回：

September | 50

October   | 33

我怎么能这样做呢?！

这是我的代码：

IF (select TotalTreinos from dbo.tbl_CALCULA_TREINOS_MES('20160901','20160930',2,5)) = (SELECT Presencas FROM DBO.tbl_CALCULA_PRESENCAS('Setembro',9,2)) 
    SELECT 'Setembro' as Mes, 100 AS PercentPresencas
else
    select 'Setembro' as Mes, (((SELECT Presencas FROM DBO.tbl_CALCULA_PRESENCAS('Setembro',9,2)) * 100) / (select TotalTreinos from dbo.tbl_CALCULA_TREINOS_MES('20160901','20160930',2,5))) as PercentPresencas

union all

IF (select TotalTreinos from dbo.tbl_CALCULA_TREINOS_MES('20161001','20161031',2,5)) = (SELECT Presencas FROM DBO.tbl_CALCULA_PRESENCAS('Outubro',10,2)) 
    SELECT 'Outubro' as Mes, 100 AS PercentPresencas
else
    select 'Outubro' as Mes,(((SELECT Presencas FROM DBO.tbl_CALCULA_PRESENCAS('Outubro',10,2)) * 100) / (select TotalTreinos from dbo.tbl_CALCULA_TREINOS_MES('20161001','20161031',2,5))) as PercentPresencas

Answer 1

T-SQL中的IF语句是一条命令性语句，而不是SQL语言的一部分。您想要做的是将IF语句移到查询中，在这里您应该改用CASE：

SELECT 
  'Setembro' AS Mes, 
  CASE WHEN (SELECT TotalTreinos FROM dbo.tbl_CALCULA_TREINOS_MES('20160901','20160930',2,5)) 
          = (SELECT Presencas FROM DBO.tbl_CALCULA_PRESENCAS('Setembro',9,2)) 
       THEN 100
       ELSE (((SELECT Presencas FROM DBO.tbl_CALCULA_PRESENCAS('Setembro',9,2)) * 100) 
            / (SELECT TotalTreinos FROM dbo.tbl_CALCULA_TREINOS_MES('20160901','20160930',2,5)))
  END AS PercentPresencas
UNION ALL
SELECT 
  'Outubro' AS Mes, 
  CASE WHEN (SELECT TotalTreinos FROM dbo.tbl_CALCULA_TREINOS_MES('20161001','20161031',2,5)) 
          = (SELECT Presencas FROM DBO.tbl_CALCULA_PRESENCAS('Outubro',10,2)) 
       THEN 100
       ELSE (((SELECT Presencas FROM DBO.tbl_CALCULA_PRESENCAS('Outubro',10,2)) * 100) 
            / (SELECT TotalTreinos FROM dbo.tbl_CALCULA_TREINOS_MES('20161001','20161031',2,5))) 
  END AS PercentPresencas

通过分解常见表达式进一步改进CTE：

WITH 
  t1 (Mes, StartDate, EndDate) AS (
    -- Of course, these could be calculated also automatically, but you get the idea...
    SELECT 'Setembro', '20160901', '20160930'
    UNION ALL
    SELECT 'Outubro', '20161001', '20161031'
  ), 
  t2 (Mes, TotalTreinos, Presencas) AS (
    SELECT
      Mes,
      (SELECT TotalTreinos FROM dbo.tbl_CALCULA_TREINOS_MES(StartDate,EndDate,2,5)),
      (SELECT Presencas FROM DBO.tbl_CALCULA_PRESENCAS(Mes,10,2))
    FROM t1
  )
SELECT 
  Mes, 
  CASE WHEN TotalTreinos = Presencas
       THEN 100
       ELSE Presencas * 100 / TotalTreinos
  END AS PercentPresencas
FROM t2