mysql - 如何简化以下 SQL 查询?
问题描述
我有一个查询可以完成我需要做的所有事情,但它感觉很脆弱,而且确实非常复杂,我很想简化它。我认为这很复杂,因为需要将行转换为元数据的列。
SELECT
O.order_item_id,
O.order_item_name,
MAX(CASE WHEN OIM.meta_key = 'class' THEN OIM.meta_value END) AS 'class',
MAX(CASE WHEN OIM.meta_key = 'fee-type' THEN OIM.meta_value END) AS 'fees',
MAX(CASE WHEN OIM.meta_key = 'enrolment_id' THEN (
SELECT firstname FROM wp_enrolments E WHERE OIM.meta_value=E.id) END) as 'name',
MAX(CASE WHEN OIM.meta_key = 'enrolment_id' THEN (
SELECT surname FROM wp_enrolments E WHERE OIM.meta_value=E.id) END) as 'surname',
MAX(CASE WHEN OIM.meta_key = 'enrolment_Id' THEN (
SELECT UM.meta_value
FROM wp_usermeta UM, wp_enrolments E
WHERE UM.meta_key='_user_phone'
AND UM.user_id=E.memberid
AND E.memberid=OIM.meta_value
AND OIM.meta_key='enrolment_id' LIMIT 1) END) as 'phone',
MAX(CASE WHEN OIM.meta_key = 'enrolment_id' THEN (
SELECT U.user_email
FROM wp_users U, wp_enrolments E
WHERE U.id=E.memberid
AND E.memberid=OIM.meta_value
AND OIM.meta_key='enrolment_id' LIMIT 1) END) as 'email',
FROM wp_woocommerce_order_items O
INNER JOIN wp_woocommerce_order_itemmeta OIM ON O.order_item_id = OIM.order_item_id
GROUP BY O.order_item_id
解决方案
考虑LEFT JOIN
所有子查询表,然后在CASE
语句中使用这些列。注意:没有数据,这是未经测试的,可能需要调整,但可以帮助您重构 SQL 查询。由于不同的连接字段(id和memberid ),需要两次wp_enrolments连接:
SELECT
O.order_item_id,
O.order_item_name,
MAX(CASE WHEN OIM.meta_key = 'class' THEN OIM.meta_value END) AS `class`,
MAX(CASE WHEN OIM.meta_key = 'fee-type' THEN OIM.meta_value END) AS `fees`,
MAX(CASE WHEN OIM.meta_key = 'enrolment_id' THEN idE.firstname END) as `name`,
MAX(CASE WHEN OIM.meta_key = 'enrolment_id' THEN idE.surname END) as `surname`,
MAX(CASE WHEN OIM.meta_key = 'enrolment_Id' AND UM.meta_key='_user_phone' THEN UM.meta_value END) as `phone`,
MAX(CASE WHEN OIM.meta_key = 'enrolment_id' THEN U.user_email END) as `email`
FROM wp_woocommerce_order_items O
INNER JOIN wp_woocommerce_order_itemmeta OIM
ON O.order_item_id = OIM.order_item_id
LEFT JOIN wp_enrolments idE
ON OIM.meta_value = idE.id
LEFT JOIN wp_enrolments memE
ON OIM.meta_value = memE.memberid
AND OIM.meta_key = 'enrolment_id'
LEFT JOIN wp_users U
ON U.id = memE.memberid
LEFT JOIN wp_usermeta UM
ON UM.user_id = memE.memberid
GROUP BY O.order_item_id,
O.order_item_name
推荐阅读
- c - 是“int *ptr = *( ( &a ) + 1 );” 其中“a”是标准明确定义的 int[5]?
- javascript - React Native 无法将 Firebase 密钥传递给组件
- android - Android Wear - 在环境中显示表盘,在活动时显示应用
- python - stdout/stderr - ValueError: 要解压的值太多(预期为 2)
- java - 当我使用 DASH 时,我的 Exoplayer 播放器给我一个错误
- java - java.lang.IllegalArgumentException:URI 不是绝对的 - FileInputStream
- c# - 知道何时以及如何旋转对象
- javascript - 多维数组正确但返回值未定义?
- vbscript - VBScript 在计划时无法正常工作
- html - 将 Ionic 页面放到网站上