mysql - 改进我的查询

Question

我确信有一种方法可以从 enswitch_mobile_users 中提取数据，这比我如何提取数据要好

Enswitch_User_ID, Os_Version, Phone_Type

那么我该如何改进和完善这个查询呢？

$sql = "SELECT Substring(purchase.`date`,12,8)               AS `Time`, 
                  purchase.`item`                                    AS `Package`,
                  Cast(Substring(purchase.`item`, 12) AS UNSIGNED)   AS `Credit`,
                  purchase.`user`                                    AS `Mobile_User_ID`,

                  (SELECT user_id FROM enswitch_mobile_users WHERE `Mobile_User_ID` = enswitch_mobile_users.`id`) AS `Enswitch_User_ID`,
                  (SELECT os_version FROM enswitch_mobile_users WHERE `Mobile_User_ID` = enswitch_mobile_users.`id`) AS `Os_Version`,
                  (SELECT phone_type FROM enswitch_mobile_users WHERE `Mobile_User_ID` = enswitch_mobile_users.`id`) AS `Phone_Type`,

                  (SELECT enswitch_id FROM enswitch_users WHERE `Enswitch_User_ID` = enswitch_users.`id`)  AS `Enswitch_ID`

                        FROM   (SELECT item, date, user
                                    FROM enswitch_android_purchases 
                                    UNION 
                                    SELECT item, date, user
                                    FROM enswitch_iphone_purchases) AS `purchase` 
                        WHERE Substring(purchase.`date`,1,10) = :date
                ORDER  BY `Time` DESC";

Answer 1

试试这个:

SELECT SUBSTRING(p.date,12,8) AS TIME, p.item AS Package, COUNT(1) AS Sales, 
       CAST(SUBSTRING(p.item, 12) AS UNSIGNED) AS Credit, p.user AS Mobile_User_ID, 
       e.user_id Enswitch_User_ID, e.os_version Os_Version, e.phone_type Phone_Type, 
       eu.enswitch_id Enswitch_ID
FROM (SELECT item, DATE, USER FROM enswitch_android_purchases 
      UNION
      SELECT item, DATE, USER FROM enswitch_iphone_purchases) AS p 
LEFT JOIN enswitch_mobile_users e ON p.user = e.id
LEFT JOIN enswitch_users eu ON e.user_id = eu.id
WHERE SUBSTRING(p.date,1,10) := DATE
ORDER  BY TIME DESC;