mysql - 按程序列出用户

Question

有这样的表:

用户:

CREATE TABLE `affiliate__model__user_node` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `user_id` bigint(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB

程序:

CREATE TABLE `affiliate__model__program` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 PRIMARY KEY (`id`),
) ENGINE=InnoDB

使用程序的用户:

CREATE TABLE `affiliate__model__user_program` (
 `user_id` bigint(20) NOT NULL DEFAULT '0',
 `program_id` bigint(20) NOT NULL DEFAULT '0',
 `username` varchar(255) NOT NULL,
 PRIMARY KEY (`user_id`,`program_id`)
) 

如何有效地列出属于特定程序的用户，就像这样？

   user_id    | program 1 | program 2 | program 3 | program N ....
--------------+-----------+-----------+-----------+-----------           
         1    |     Y     |      N    |      N    |     Y
         3    |     N     |      N    |      N    |     N
         7    |     N     |      Y    |      N    |     N
         12   |     Y     |      Y    |      Y    |     Y
         n    |     N     |      N    |      N    |     Y

(节目数量可能会根据program表数据改变，但限制在~20个)

Answer 1

您可以尝试使用以下查询:

SELECT
    user.user_id,
    IF(program.name = 'program 1', 'Y', 'N') AS 'program 1'
    IF(program.name = 'program 2', 'Y', 'N') AS 'program 2'
    IF(program.name = 'program 3', 'Y', 'N') AS 'program 3'
    ...
FROM affiliate__model__user_node user
INNER JOIN affiliate__model__user_program model
ON user.id = model.user_id
INNER JOIN affiliate__model__program program
ON model.program_id = program.id

对于大约 20 个程序列，这不会因为是最漂亮的查询而获奖，但它应该完成工作。