动态情况时

Question

     SELECT
     COUNT(id), AgeRange
     FROM
     (
     select
       id,
      case
        when age < 0 then 'less than 0'
        when age >= 0 and age <=30 then '0-30'
        when age >= 31 and age <=60 then '31-60'
        when age >= 61 and age <=90 then '61-90'
        when age >= 91 then '91+'
         when age = null then 'NO INFORMATION'
      else 'no catagory'
    end AS AgeRange
 from queue 
 where DATE between '01-Apr-2011' and '05-May-2011'
 ) T
GROUP BY
    AgeRange;

我希望这些年龄范围（0-30、31-60、61-90）是动态的。这意味着这些值应该来自表（因为这些值是由用户设置的）。用户可以设置任意数量的值以获得结果。我怎样才能做到这一点？

Answer 1

假设您有第二个表，例如：

ASSIST_TABLE
FromAge|ToAge|Text
0|30|'0-30'

您可以执行类似的操作。

 SELECT
 COUNT(id),
 FROM
 (
 select
   id,
   ISNULL(select text from ASSIST_TABLE
     where Age between FromAge andToAge),'NO CATEGORY') AS AGERANGE
from queue 
where DATE between '01-Apr-2011' and '05-May-2011'
 ) T
GROUP BY
AgeRange;

如果你想坚持你的案例陈述，你就必须让陈述充满活力。这意味着首先生成查询，将其存储到变量中，最后执行它。那是更多的工作！

Answer 2

这是另一个尝试：动态 SQL，它不是最终的答案，但您已经了解了。

CREATE PROCEDURE sp_generate_valid_choices (IN p_request_id Bigint)
BEGIN
 DECLARE num_rows INT DEFAULT 0;
 DECLARE no_more_rows BINARY;
 DECLARE no_more_subrows BINARY;
 DECLARE loop_cntr INT DEFAULT 0;
 DECLARE var_choice_group BIGINT DEFAULT 0;

 -- Declare Cursor for the loop through the constraint_groups
 DECLARE cur_constraint_group CURSOR FOR
 SELECT distinct choice_constraint_group FROM aip_choice_constraint
    WHERE choice_id_rule_parameter IN (SELECT choice_id FROM aip_request_detail
                                        where request_id = p_request_id);
 -- DECLARE 'handlers' for exceptions
 DECLARE CONTINUE HANDLER FOR NOT FOUND
 SET no_more_rows := TRUE;

-- OPEN CURSOR AN PROCESS CONSTRAINT_GROUPS
OPEN cur_constraint_group;
SELECT FOUND_ROWS() INTO num_rows;

choice_group_loop: LOOP
    FETCH cur_constraint_group
    INTO var_choice_group;

IF no_more_rows THEN
    CLOSE cur_constraint_group;
    LEAVE choice_group_loop;
END IF;
-- PAYLOAD
-- INSERT THE VALID CHOCIES INTO tmp_aip_valid_choices 
 SELECT @var_sql_query := CONCAT('INSERT INTO tmp_aip_valid_choices ','SELECT ',p_request_id,' as request_id,  `aip_choice_constraint`.`choice_constraint_id`,`aip_choice_constraint`.`choice_constraint_group`
    ,AVG(IF (`aip_request_detail`.`choice_varchar_value`', `aip_choice_constraint`.`choice_constraint_operator`, '\'',`aip_choice_constraint`.`choice_constraint_value`, '\'',',1,0 )) AS VALID
FROM `aip_choice_constraint`
LEFT JOIN `aip_request_detail` ON `aip_request_detail`.`choice_id` = `aip_choice_constraint`.`choice_id_rule_parameter`
WHERE `aip_choice_constraint`.choice_constraint_group =' , var_choice_group,
' GROUP BY `aip_choice_constraint`.choice_constraint_group')
FROM `aip_choice_constraint` WHERE `aip_choice_constraint`.choice_constraint_group = var_choice_group;
PREPARE SQL_STATEMENT FROM @var_sql_query;
EXECUTE SQL_STATEMENT;
-- INCREMENT THE COUNTER
SET loop_cntr = loop_cntr + 1;

END LOOP choice_group_loop;

INSERT INTO tmp_aip_valid_choices_for_request
(request_id, choice_id)
SELECT DISTINCT p_request_id, choice_id FROM aip_choice ac
-- RULE 1 ALL CHOICES WITHOUT CONSTRAINTS
WHERE ac.choice_id NOT IN (SELECT choice_id_rule_target FROM aip_choice_constraint)
-- RULE 2 ALL CHOICES WITH CONSTRAINTS, THAT ARE NOT YET ANSWERED
OR ac.choice_id NOT IN (SELECT choice_id_rule_target FROM aip_choice_constraint
WHERE choice_id_rule_parameter IN (SELECT choice_id FROM aip_request_detail WHERE request_id = p_request_id))
-- RULE 3 ALL CHOICES WITH CONSTRAINTS, THAT ARE TRUE
OR ac.choice_id IN (SELECT choice_id_rule_target FROM aip_choice_constraint
WHERE choice_constraint_group IN (SELECT choice_constraint_group FROM tmp_aip_valid_choices WHERE request_id = p_request_id AND VALID = 1));

END //
Delimiter ;

该代码是MySQL的，所以你不能复制它。但想法是一样的。将语句准备为字符串，然后执行它。