MySQL动态交叉表查询:选择子记录作为其他列 [英] MySQL dynamic crosstab query: Selecting child records as additional columns
问题描述
我在MySQL数据库中有三个表:
I have three tables in a MySQL database:
users(user_settings,setting_types)
setting_types(id,name)
user_settings(value,user_id,setting_type_id)
我想对它们进行规范化处理，以便在查询数据库时得到如下结果:
I would like to denormalize these so that when I query the DB I get a result like this:
User.id User.username setting_name_1 setting_name_2 etc...
1 Admin true false
2 User false false
当前的问题是设置类型系统是可扩展的:我不一定提前知道用户将要拥有哪些设置，因此我无法将它们直接硬编码到查询中.
The issue at hand is that the setting type system is extensible: I don't necessarily know which settings a user is going to have ahead of time, so I can't hardcode them directly into the query.
是否可以通过以下方式查询此查询，即查询返回由setting_type为每个子user_setting创建的带有附加列的用户记录?还是超越MySQL?
Is it possible to query this in such a way that the query returns user records with additional columns for each child user_setting by setting_type? Or is this beyond MySQL?
推荐答案
计划
- 编写透视源查询以获取透视所需的所有信息
- 生成动态sql以重复连接和透视源
- write pivot source query to get all information required for pivot
- generate dynamic sql to repeatedly join and pivot source
设置
create table users
(
id integer primary key not null,
username varchar(23) not null
-- some user data..
);
create table setting_types
(
id integer primary key not null,
name varchar(23) not null
);
create table user_settings
(
id integer primary key not null,
user_id integer not null,
setting_type_id integer not null,
value varchar(13) not null,
foreign key ( user_id ) references users( id ),
foreign key ( setting_type_id ) references setting_types ( id )
);
insert into users
( id, username )
values
( 1, 'Admin' ),
( 2, 'heresjonny' )
;
insert into setting_types
( id, name )
values
( 1, 'setting_type_1' ),
( 2, 'setting_type_2' ),
( 3, 'setting_type_3' ),
( 4, 'setting_type_4' ),
( 5, 'setting_type_5' ),
( 6, 'setting_type_6' ),
( 7, 'setting_type_7' ),
( 8, 'setting_type_8' )
;
insert into user_settings
( id, user_id, setting_type_id, value )
values
( 1, 1, 1, 'true' ),
( 2, 1, 2, 'false' ),
( 3, 1, 3, 'false' ),
( 4, 1, 4, 'false' ),
( 5, 2, 3, 'true' ),
( 6, 2, 4, 'true' ),
( 7, 2, 5, 'false' ),
( 8, 2, 6, 'true' ),
( 9, 2, 7, 'true' ),
( 10, 2, 8, 'true' )
;
枢轴
set @pivot_source = '(
select st.id as setting_id, st.name, users.id as user_id, users.username, coalesce(us.value, ''false'') as value
from setting_types st
cross join
(
select id, username
from users
) users
left join user_settings us
on users.id = us.user_id
and st.id = us.setting_type_id
)';
set @pivot_sql := replace('
select user_id, username,
#setting_aliases#
from
(
select #first_user_dets#,
#settings_fields#
from
#pivot_source# #first_alias#
inner join
#all_joins#
) q
order by user_id
;', '#pivot_source#', @pivot_source);
set @pivot_block := replace('
#pivot_source# #alias#
on #last_alias#.user_id = #alias#.user_id
and #last_alias#.setting_id < #alias#.setting_id
inner join #all_joins#', '#pivot_source#', @pivot_source)
;
select count(*) into @ignore
from
(
select
@pivot_sql := replace(@pivot_sql, '#all_joins#', replace(replace(@pivot_block, '#alias#', concat('sett', right_id)), '#last_alias#', concat('sett', left_id)))
from
(
select `left`.id as left_id, min(`right`.id) as right_id
from setting_types `left`
inner join setting_types `right`
on `left`.id < `right`.id
group by 1
) t
order by left_id
) `ignore`
;
select concat('sett', id) into @first_alias
from setting_types
order by id
limit 1
;
select concat(@first_alias, '.user_id,',@first_alias,'.username') into @first_user_dets;
select group_concat(concat('sett', id, '.value ', name) SEPARATOR ',') into @settings_fields
from setting_types
;
select group_concat(name SEPARATOR ',') into @setting_aliases
from setting_types
;
select count(*) into @ignore
from
(
select
@pivot_sql := replace(@pivot_sql, '#first_user_dets#', @first_user_dets),
@pivot_sql := replace(@pivot_sql, '#settings_fields#', @settings_fields),
@pivot_sql := replace(@pivot_sql, '#setting_aliases#', @setting_aliases),
@pivot_sql := replace(@pivot_sql, '#first_alias#', @first_alias),
@pivot_sql := replace(@pivot_sql, 'inner join #all_joins#', '')
) `ignore`
;
select @pivot_sql;
prepare pivot_sql from @pivot_sql;
EXECUTE pivot_sql;
deallocate prepare pivot_sql;
输出
+---------+------------+----------------+----------------+----------------+----------------+----------------+----------------+----------------+----------------+
| user_id | username | setting_type_1 | setting_type_2 | setting_type_3 | setting_type_4 | setting_type_5 | setting_type_6 | setting_type_7 | setting_type_8 |
+---------+------------+----------------+----------------+----------------+----------------+----------------+----------------+----------------+----------------+
| 1 | Admin | true | false | false | false | false | false | false | false |
| 2 | heresjonny | false | false | true | true | false | true | true | true |
+---------+------------+----------------+----------------+----------------+----------------+----------------+----------------+----------------+----------------+
sqlfiddle
注释
在应用程序代码中进行数据透视更常见.如果您这样做的原因是为了提高性能，则应将其与php中的类似透视图进行比较，以测试其是否明显更好.
its more common to do this pivoting in application code. if your reason for doing this is for performance, should benchmark this against analogous pivoting in php to test if is actually significantly better..
可能会在枢纽上找到我的较早答案带有动态列对开发您的php代码进行基准性能测试很有用
might find my earlier answer on pivoting with dynamic columns useful for developing your php code for benchmarking performance
这篇关于MySQL动态交叉表查询:选择子记录作为其他列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
