MySQL查询仅返回值为'Yes'的州字段及用户基础信息

你的查询语句里，SELECT子句明确指定了要返回Alabama、California这些州字段，所以不管它们的值是'Yes'还是'No'，都会被包含在结果里。WHERE子句只是用来筛选哪些行符合条件，不会影响返回的列集合。

下面提供几种可行的解决方案：

方案1：动态SQL（数据库层处理） #

MySQL的静态SQL无法根据每行数据动态调整返回的列，所以需要用动态SQL生成只包含值为'Yes'的州字段的查询语句。以下是示例存储过程：

DELIMITER //
CREATE PROCEDURE GetUserWithYesStates(IN user_id INT)
BEGIN
    -- 初始化基础字段
    SET @sql = 'SELECT id, username, uemail, urole, ustatus';
    
    -- 逐个检查州字段，值为Yes则加入SELECT列表
    IF (SELECT Alabama FROM crm_users WHERE id = user_id) = 'Yes' THEN
        SET @sql = CONCAT(@sql, ', Alabama');
    END IF;
    IF (SELECT California FROM crm_users WHERE id = user_id) = 'Yes' THEN
        SET @sql = CONCAT(@sql, ', California');
    END IF;
    IF (SELECT Colorado FROM crm_users WHERE id = user_id) = 'Yes' THEN
        SET @sql = CONCAT(@sql, ', Colorado');
    END IF;
    -- 其他州字段依次类推，复制上面的IF块替换州名即可
    
    SET @sql = CONCAT(@sql, ' FROM crm_users WHERE id = ', user_id);
    
    -- 执行动态SQL
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

调用方式：

CALL GetUserWithYesStates(1);

这个存储过程会先查询指定用户的各个州字段值，只把值为'Yes'的州字段加入SELECT语句，最终返回的结果里就只有基础字段和符合条件的州字段。

方案2：应用层处理（更简单） #

如果业务允许在应用代码里处理，可以先查询所有基础字段和所有州字段，然后在应用层过滤掉值为'No'的州字段。示例伪代码：

# 先查询所有需要的字段
query = """
SELECT id, username, uemail, urole, ustatus, Alabama, California, Colorado, ... 其他州字段
FROM crm_users WHERE id = 1
"""
result = execute_query(query)

# 过滤值为'No'的州字段
filtered_result = {}
for key, value in result.items():
    if key in ['id', 'username', 'uemail', 'urole', 'ustatus'] or value == 'Yes':
        filtered_result[key] = value

print(filtered_result)

方案3：优化数据库结构（长期最优） #

当前把每个州作为独立字段的设计属于反范式，扩展性差（新增州需要加字段），查询也不灵活。建议调整为关联表设计：

1. 创建user_states关联表：

CREATE TABLE user_states (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    state_name VARCHAR(45) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES crm_users(id)
);

2. 迁移原表中值为'Yes'的州数据：

INSERT INTO user_states (user_id, state_name) VALUES
(1, 'California'),
(1, 'Colorado'),
(1, 'Florida'),
... -- 其他值为Yes的州

3. 查询时关联两张表：

SELECT u.id, u.username, u.uemail, u.urole, u.ustatus, us.state_name
FROM crm_users u
LEFT JOIN user_states us ON u.id = us.user_id
WHERE u.id = 1;

这种设计不仅解决了当前的查询问题，后续新增州也不需要修改表结构，查询和维护都更灵活。

内容的提问来源于stack exchange，提问作者wilcan