mysql - 获取表的所有列并用 CASE 语句中的值替换一列

Question

DB-小提琴：

CREATE TABLE operations (
    id int auto_increment primary key,
    campaign VARCHAR(255),
    product VARCHAR(255),
    supplier VARCHAR(255),
    customer VARCHAR(255),
    quantity INT
);

INSERT INTO operations
(campaign, product, supplier, customer, quantity
)
VALUES 
("C001", "Product_A", "Supplier_01", "Customer_AY", "100"),
("C001", "Product_A", "Supplier_02", "Customer_BY", "300"),
("C001", "Product_B", "Supplier_01", "Customer_CY", "700"),

("C002", "Product_A", "Supplier_01", "Customer_DY", "500"),
("C002", "Product_B", "Supplier_01", "Customer_BY", "900"),

("C003", "Product_A", "Supplier_01", "Customer_AY", "600"),
("C003", "Product_B", "Supplier_01", "Customer_BY", "150"),
("C003", "Product_B", "Supplier_02", "Customer_DY", "850"),
("C003", "Product_C", "Supplier_01", "Customer_DY", "550");

---

在我的原始database文件中，我有一个operations包含大量列的表。
我目前正在使用*符号来避免列出查询中的所有列：

SELECT
*,
(CASE WHEN supplier = "Supplier_01" THEN "good" ELSE "bad" END) AS supplier_rating
FROM operations
GROUP BY 1,2,3;

columns通过此查询，我将获得column所有CASE Statement.
但是，我希望将 columnsupplier替换为 column supplier_rating。

因此，我想知道是否可以
a) 获取表中的所有列而不单独列出每个列，因为它是通过*查询中的符号完成的
b)仅将列supplier替换supplier_rating为CASE statement？

标签： mysqlsql

Answer 1

你可以试试这个：

SET @sql = CONCAT('SELECT (CASE WHEN supplier = "Supplier_01" THEN "good" ELSE "bad" END) AS supplier_rating', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), 'supplier,', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'operations' AND TABLE_SCHEMA = 'mydb'), ' FROM operations');
PREPARE newquery FROM @sql;
EXECUTE newquery;

快照：