Geek Answers Handbook

WITH in MySQL?

Does MySQL support common table expressions? For example, Oracle has a WITH ? WITH :

 WITH aliasname AS ( SELECT COUNT(*) FROM table_name ) SELECT COUNT(*) FROM dept,aliasname
+4
source share
3 answers

One way is to use a subquery:

  SELECT COUNT(*) FROM dept, ( SELECT COUNT(*) FROM table_name ) AS aliasname

Please note that between the two tables the two tables will intersect in the same way as in your message you requested. If there is any connection between them, you can JOIN them instead.

+1
source 
 SELECT t.name, t.num FROM TABLE t JOIN (SELECT c.id,COUNT(*) 'num1' FROM TABLE1 c WHERE c.column = 'a' GROUP BY c.id) ta1 ON ta1.id = t.id JOIN (SELECT d.id,COUNT(*) 'num2' FROM TABLE2 d WHERE d.column = 'a' GROUP BY d.id) ta2 ON ta2.id = t.id
+1
source

No, MySQL does not support Common Table Expressions (CTE). So instead of using WITH tablealias as (....) you will need to execute a subquery.

For instance,

 WITH totalcount AS (select userid, count(*) as tot from logins group by userid) SELECT a.firstname, a.lastname, b.tot FROM users a INNER JOIN totalcount b on a.userid = b.userid

can be rewritten in MySQL as

 SELECT a.firstname, a.lastname, b.totalcount FROM users a INNER JOIN (select userid, count(*) as tot from logins group by userid) b on a.userid = b.userid
+1
source

More articles:


All Articles