如何从键值对表中选择数据 [英] How to select data from a key value pair table

问题描述

表A

id,parentID, key, value
1, 2, name, name1
2, 2, age, 20
3, 2, place, place1

50, 7, name, namex
51, 7, age, 20
52, 7, place, place1
........
101, 5, name, namez
102, 5, age, 23
103, 5, place, place2

我需要以波纹管格式获取所有具有 plave = place1 和 age = 20 的日期

I need to get all the date having plave = place1 and age = 20 in the bellow format

parentid, name, age, place
2, name1, 20, place1
7, namex, 20, place1

如何写Mysql SQL查询请帮帮我

How to write the Mysql SQL query please help me

推荐答案

您可以使用条件聚合将 parentid 的所有信息放到一行，然后使用 where 子句获取所需的条件.

You can use conditional aggregation to get all the info for a parentid on to one row and then use a where clause for the required condition.

select * from (
select parentid
,max(case when key='name' then value end) as name
,max(case when key='age' then value end) as age
,max(case when key='place' then value end) as place
from tableA
group by parentid 
) t
where place='place1' and age=20

这假设表中每个 parentid 的每个键只有一行.

This assumes there is only one row per key per parentid in the table.

这篇关于如何从键值对表中选择数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！