APIJSON支持left join 、union all、子查询等其他复杂sql吗 #98

Activity

1. TommyLemon commented on Jul 8, 2019

   

   TommyLemon

   on Jul 8, 2019

   Last edited by TommyLemon

   Member

   More actions

   JOIN 和 子查询 见 3.2 功能符
   https://github.com/APIJSON/APIJSON/blob/master/Document.md#3.2

   目前还不支持 UNION ALL，可以用主副表条件 OR 连接的 INNER JOIN 替代（"join": "/ViceTable/refKey@"）
   也可以在建议收集箱加一个新增功能的建议
   #37
2. TommyLemon

   added

   Enhancement 增强增强功能、提高性能等增强功能、提高性能等

   on Aug 6, 2019
3. gsqatm commented on May 6, 2020

   

   gsqatm

   on May 6, 2020

   Last edited by TommyLemon

   More actions

   JOIN和子查询见3.2功能符
   https://github.com/APIJSON/APIJSON/blob/master/Document.md#3.2

   目前还不支持UNION ALL，可以用主副表条件或连接的INNER JOIN替代（“ join”：“ / ViceTable / refKey @”）
   也可以在建议收集箱加一个补充功能的建议
   ＃37

   LEFT JOIN中，除了主表，其他表为何要先建立临时表?
   例如:
   {
   "[]": {
   "join": "</User/id@,</Comment/momentId@",
   "Moment": {},
   "User": {
   "name~": "t",
   "id@": "/Moment/userId"
   },
   "Comment": {
   "momentId@": "/Moment/id"
   }
   },
   "@Explain": true
   }

   SQL:
   SELECT Moment.*, User.*, Comment.* FROM apijson_demo.Moment AS Moment LEFT JOIN ( SELECT * FROM apijson_demo.apijson_user WHERE ( (name REGEXP BINARY 't') ) ) AS User ON User.id = Moment.userId LEFT JOIN ( SELECT * FROM apijson_demo.Comment ) AS Comment ON Comment.momentId = Moment.id LIMIT 10 OFFSET 0
4. TommyLemon commented on Dec 17, 2020

   

   TommyLemon

   on Dec 17, 2020

   Last edited by TommyLemon

   Member

   More actions

   JOIN和子查询见3.2功能符
   https://github.com/APIJSON/APIJSON/blob/master/Document.md#3.2
   目前还不支持UNION ALL，可以用主副表条件或连接的INNER JOIN替代（“ join”：“ / ViceTable / refKey @”）
   也可以在建议收集箱加一个补充功能的建议
   ＃37

   LEFT JOIN中，除了主表，其他表为何要先建立临时表?
   例如:
   {
   "[]": {
   "join": "</User/id@,</Comment/momentId@",
   "Moment": {},
   "User": {
   "name~": "t",
   "id@": "/Moment/userId"
   },
   "Comment": {
   "momentId@": "/Moment/id"
   }
   },
   "@Explain": true
   }

   SQL:
   SELECT Moment.*, User.*, Comment.* FROM apijson_demo.Moment AS Moment LEFT JOIN ( SELECT * FROM apijson_demo.apijson_user WHERE ( (name REGEXP BINARY 't') ) ) AS User ON User.id = Moment.userId LEFT JOIN ( SELECT * FROM apijson_demo.Comment ) AS Comment ON Comment.momentId = Moment.id LIMIT 10 OFFSET 0

   如果移到外层就不支持副表带条件了。

   这个不会影响性能，像

   LEFT JOIN ( SELECT * FROM `apijson_demo`.`Comment` ) AS `Comment`

   

   这种简单的子查询，MySQL 5.7+ 会自动优化为

   LEFT JOIN `apijson_demo`.`Comment` AS `Comment`

   

   见以上两条 SQL 下的两张 EXPLAIN 效果图，执行过程一样