MySQL支持通用表达式吗?例如,在Oracle中有一个“WITH”子句?
WITH aliasname
AS
( SELECT COUNT(*) FROM table_name )
SELECT COUNT(*) FROM dept,aliasname
WITH aliasname
AS
( SELECT COUNT(*) FROM table_name )
SELECT COUNT(*) FROM dept,aliasname
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
一种方法是使用子查询:
SELECT COUNT(*)
FROM dept,
(
SELECT COUNT(*)
FROM table_name
) AS aliasname
,
将像您发布的查询中一样交叉连接这两个表。如果它们之间有任何关系,您可以使用JOIN
来连接它们。WITH
,从问题中我理解他需要类似于 Oracle CTE 的东西,它是使用 WITH
子句定义的,MySQL 中的替代方法是使用子查询。 - Mahmoud GamalWITH tablealias as (....)
。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
可以在MySQL中重新编写为
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
让我们来谈谈 WITH
子句。
WITH
子句和 INNER JOIN 或 JOIN 是同一种类型,但是在 WHERE
子句中,WITH
子句给您提供了更多的灵活性;
我将创建一个视图,该视图将获取诸如用户计数、用户名等值。
首先(创建我们的用户表和插入用户):
插入用户表:
CREATE TABLE users (id BIGINT(10) AUTO INCEREMENT PRIMARY KEY , name VARCHAR(50))
用户表:
CREATE TABLE users (id BIGINT(10) AUTO INCEREMENT PRIMARY KEY , name VARCHAR(50) , gender TINYINT(1))
第二步(插入一些值以便操作):
用户表:
INSERT INTO users (name,gender) VALUES ('Abolfazl M' , 1)
我不想通过查询将数据插入到inserted_users表中,而是想添加一个触发器TRUGGER
,在数据插入到users表之前自动将数据插入到users_inserted表中。
第三步(创建触发器add_uinserted):
DELIMITER $$
CREATE TRIGGER IF NOT EXISTS add_uinserted BEFORE INSERT ON users FOR EACH ROW
BEGIN
IF NEW.name <> '' THEN
INSERT INTO users_inserted (name) VALUES (NEW.name);
ELSE
INSERT INTO users (name,gender) VALUES ('Unknown',NEW.gender);
INSERT INTO users_inserted (name) VALUES ('Unknown');
END IF;
END$$
DELIMITER ;
CREATE VIEW select_users AS
WITH GetCAll AS (
SELECT u1.id As Uid ,COUNT(u1.name) AS CnAll FROM users u1
)
SELECT u1.name AS NAME,CASE
WHEN s1.gender = 1 THEN "MALE"
WHEN s1.gender = 0 THEN "FEMALE"
ELSE "UNKNOWN"
END AS GENDER,CASE
WHEN u1.id = gca.Uid THEN "INSERTED TO users_inserted"
ELSE "NOT INSERTED TO users_inserted"
END AS INSERTED,gca.CnAll FROM GetCAll AS gca INNER JOIN users u1;
当您的查询运行后,视图将被创建,通过调用 select_users 视图来显示数据。
最后一步(调用 select_users 视图):
SELECT * FROM select_users
感谢您查看我的答案,就是这样!