SQL 'with‘语句

Question

我这里有一个语法错误，但找不到它。你对这个有什么想法吗？

WITH table_selection AS (SELECT device_name FROM devices WHERE device_id = 8),
column_names AS (SELECT column_name FROM device_parameters WHERE device_id =  8)
SELECT * FROM table_selection

其思想是，table_selection查询获取表的名称，column_names查询获取要从该表中读取的列，而SELECT将获取table_selection中column_names列的所有值。

完整的错误消息是：

Exception: Error running query:
SQLQuery(query=; WITH table_selection AS (SELECT device_name FROM devices WHERE device_id = 5),
column_names AS (SELECT column_name FROM device_parameters WHERE device_id =  5)
SELECT * FROM table_selection
, database=Logging)@0ms
On: Data Tracker.Root Container.Table 2.data
    caused by GatewayException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; WITH table_selection AS (SELECT device_name FROM devices WHERE device_id = 5),' at line 1
    caused by MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; WITH table_selection AS (SELECT device_name FROM devices WHERE device_id = 5),' at line 1

Ignition v7.9.5 (b2017111615)
Java: Oracle Corporation 1.8.0_231

Answer 1

为什么不试试JOIN呢？

SELECT device_name,column_names 
FROM devices D JOIN device_parameters DP ON D.device_id = DP.device_id 
WHERE device_id = 8;

Answer 2

仔细查看错误消息：

... near '; WITH ...

它指向分号(或紧接在分号之前)。我将其解释为客户端包不知道如何在一次调用中处理两个语句。(大多数客户端API都不允许这样做。)