Question

我选择如下:

SELECT
  COUNT(a.int_re_usu) AS qtd,
  a.txt_resposta,
  b.txt_marca,
  (
    SELECT
      CASE
        WHEN a.txt_resposta IS NULL
        THEN
          CASE
            WHEN a.bit_opcao = 1
            THEN  Sim 
            ELSE  Não 
          END
        ELSE a.txt_resposta
      END
  ) AS Answer
  FROM tb_questionario_voar_resposta a
  INNER JOIN tb_questionario_voar b ON a.int_id_questionario = b.int_id_questionario
  GROUP BY b.txt_marca, Answer

如你所看到的那样,选定一个涉及两栏的个案。

When i try to run this query i get an error saying: Invalid column name Answer .

My question is how to run this query group by the "Select case" column named "Answer".

Answer 1

更可读的版本:

SELECT 
    COUNT(a.int_re_usu) AS qtd, 
    b.txt_marca, 
    (SELECT CASE 
        WHEN a.txt_resposta IS NULL THEN 
            CASE 
                WHEN a.bit_opcao = 1 THEN  Sim  
                ELSE  Não  
            END 
        ELSE a.txt_resposta 
     END) AS answer, 
    a.txt_resposta 
FROM   
    tb_questionario_voar_resposta a 
    INNER JOIN tb_questionario_voar b 
        ON a.int_id_questionario = b.int_id_questionario 
GROUP BY 
    b.txt_marca, 
    answer

The first problem is that the GROUP BY doesn t include txt_resposta.

第二个问题是,由于答案不是在源表中,而是计算,你可以提出“按回答分列的清单”。如上所示,你最好把分局分成单独的条款,但如果你真的想要在一刀切的情况下这样做,那就好像(更确切地说)这样:

SELECT 
    COUNT(a.int_re_usu) AS qtd, 
    b.txt_marca, 
    (SELECT CASE 
        WHEN a.txt_resposta IS NULL THEN 
            CASE 
                WHEN a.bit_opcao = 1 THEN  Sim  
                ELSE  Não  
            END 
        ELSE a.txt_resposta 
     END) AS answer, 
    a.txt_resposta 
FROM   
    tb_questionario_voar_resposta a 
    INNER JOIN tb_questionario_voar b 
        ON a.int_id_questionario = b.int_id_questionario 
GROUP BY 
    b.txt_marca, 
    (SELECT CASE 
        WHEN a.txt_resposta IS NULL THEN 
            CASE 
                WHEN a.bit_opcao = 1 THEN  Sim  
                ELSE  Não  
            END 
        ELSE a.txt_resposta 
     END),
     a.txt_resposta

或更可读的是,有一条规定:

WITH temp(usu, txt_marca, answer, txt_resposta) AS (
    SELECT 
        a.int_re_usu,
        b.txt_marca, 
        (SELECT CASE 
            WHEN a.txt_resposta IS NULL THEN 
                CASE 
                    WHEN a.bit_opcao = 1 THEN  Sim  
                    ELSE  Não  
                END 
            ELSE a.txt_resposta 
         END), 
        a.txt_resposta 
)
SELECT
    COUNT(usu) as qtd,
    txt_marca,
    answer,
    txt_resposta
FROM
    temp
GROUP BY
    txt_marca,
    answer,
    txt_resposta

Answer 2

您必须按询问内容在甄选和小组中说明同一案件。

Answer 3

请查看Anwser的拼写,你在法典中称Awnser。

Answer 4

你可以尝试:

select  count(int_re_usu) as qtd, 
    txt_marca, Answer. txt_resposta 
From 
    (select  
        a.int_re_usu, 
        b.txt_marca, 
        case when a.txt_resposta is null 
        then  
        case when a.bit_opcao = 1 
        then 
         Sim  
        else 
         Não  
        end 
        else 
        a.txt_resposta 
        end as Answer, 
        a.txt_resposta 
    from  
    tb_questionario_voar_resposta a 
    inner join 
    tb_questionario_voar b 
    on 
    a.int_id_questionario 
    = 
    b.int_id_questionario ) c
group by 
txt_marca, 
Answer,
txt_resposta

Answer 5

You may want to consider creating a view which performs the sub-query case statement. Then select from the view with grouping.

友情链接