问题很简单。如何将以下 2 个 sql 查询合并为一个。
我在MYSQLWorkbase UI上使用MYSQL v8.0.16。
SELECT Node_Type, COUNT(*) AS `count`
FROM node_table_vis
WHERE Node_SpinePlanID = "1"
GROUP BY Node_Type;
返回
TYPE - COUNT
-----------
AGN 18
TJ 26
DSLAM 15
PON 18
CCJ 17
和
SELECT DISTINCT Node_SpinePlanID
FROM node_table_vis
WHERE Node_Exchange = "Exchange 1";
这就是回报
SpinePlanID
------------
1
5
10
所以本质上我想要的是看起来像这样的查询?
SELECT Node_Type, COUNT(*) AS `count`
FROM node_table_vis
WHERE Node_SpinePlanID =
(
SELECT DISTINCT Node_SpinePlanID
FROM node_table_vis
WHERE Node_Exchange = "Exchange 1";
)
GROUP BY Node_Type;
所以我得到的表格看起来像
TYPE - 1 - 5 - 10
-----------------------
AGN 18 x y
TJ 26 x y
DSLAM 15 x y
PON 18 x y
CCJ 17 x y
所以这只是抛出错误而不是生产货物。我能够找到顶部查询的答案,我能够进行底部查询,但是我无法找到将两者结合起来的答案。
任何建议将不胜感激。
更新/编辑
我有以下...
SET @@group_concat_max_len = 10000;
SET @sql = null;
SELECT group_concat(distinct
concat(
'SUM(Node_SpinePlanID = ''',
Node_SpinePlanID,
''',) AS ',
Node_SpinePlanID
)
) INTO @sql
FROM node_table_vis;
SET @sql = CONCAT('SELECT Node_Type, ', @sql, ' FROM node_table_vis GROUP BY Node_Type');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
但是,准备标准不工作?收到错误代码 1064 您有 SQL 错误语法?
对此有什么建议吗?
您是否正在寻找条件聚合?
SELECT Node_Type,
SUM(Node_SpinePlanID = 1) AS count_1,
SUM(Node_SpinePlanID = 5) AS count_5,
SUM(Node_SpinePlanID = 10) AS count_10
FROM node_table_vis
GROUP BY Node_Type;
您还可以将值放入行中:
SELECT Node_Type, Node_SpinePlanID, COUNT(*) as cnt
FROM node_table_vis
GROUP BY Node_Type, Node_SpinePlanID;
如果没有动态 SQL,您能做的最好的事情就是将值放在一行中,就是将它们放在具有两个聚合级别的字符串中:
SELECT Node_Type, GROUP_CONCAT(Node_SpinePlanID, ':', cnt SEPARATOR ',')
FROM (SELECT Node_Type, Node_SpinePlanID, COUNT(*) as cnt
FROM node_table_vis
GROUP BY Node_Type, Node_SpinePlanID
) ts
GROUP BY Node_Type;