mysql - 单个查询中具有多个选定结果的多个选择语句

Question

我正在尝试将多个选择链接到一个查询中我已经查看了这里的另一个线程，这很有帮助，但这只说明了像这样的单个选择选项。

  SELECT  (
    SELECT COUNT(*)
    FROM   user_table
    ) AS tot_user,
    (
    SELECT COUNT(*)
    FROM   cat_table
    ) AS tot_cat,
    (
    SELECT COUNT(*)
    FROM   course_table
    ) AS tot_course

但我的问题是我正在尝试从每个选择中获取多个选项。

SELECT (
SELECT c.name AS Company,
CONCAT(UPPER(u.forename),' ', u.surname) AS Username,
count(c.case) AS 'No. of Private Cases Generated',
) AS 'No. of Private Cases Generated',
(
SELECT c.name AS Company,
CONCAT(UPPER(u.forename),' ', u.surname) AS Username,
count(c.case) AS 'No. of Web Cases Generated'
) AS 'No. of Web Cases Generated'

我不断收到错误消息“#1241 - 操作数应包含 1 列”

从我读到的内容来看，这是因为我试图在一个选择器下链接 3 个不同的值，而我不想这样做。

最后的这段代码就是问题所在。

) AS 'No. of Web Cases Generated'

我已经尝试解决这个问题，但显然我缺少解决问题的专业知识。

) "" AS Company,
  "" AS Username,
  "" AS 'No. of Private Cases Generated',
  AS 'No. of Web Cases Generated'

这不起作用，因为它不知道将什么值分配给什么选择器。

如有任何帮助，我们将不胜感激。

Answer 1

是的，这是正确的行为，因为您实际上是在尝试将多个列值合并到一个列中，这是不可能的

( SELECT c.name AS Company,
CONCAT(UPPER(u.forename),' ', u.surname) AS Username,
count(c.case) AS 'No. of Private Cases Generated',
) AS 'No. of Private Cases Generated',

想想你正在寻找的是一个 UNION 而不是

SELECT c.name AS Company,
CONCAT(UPPER(u.forename),' ', u.surname) AS Username,
count(c.case) AS 'No. of Private Cases Generated'
FROM some_table  --first part of query

UNION            -- combine the data

SELECT c.name AS Company,
CONCAT(UPPER(u.forename),' ', u.surname) AS Username,
count(c.case) AS 'No. of Web Cases Generated'
FROM some_other_table;  -- second part of query

根据您的评论，执行 JOIN 然后

select xx.Company, xx.Username,xx.'No. of Private Cases Generated',
xxx.Company1, xxx.Username1, xxx.'No. of Web Cases Generated'
FROM (
SELECT c.name AS Company,
CONCAT(UPPER(u.forename),' ', u.surname) AS Username,
count(c.case) AS 'No. of Private Cases Generated',
FROM some_table  ) xx

JOIN (
SELECT c.name AS Company,
CONCAT(UPPER(u.forename),' ', u.surname) AS Username,
count(c.case) AS 'No. of Web Cases Generated'
FROM some_other_table ) xxx
ON xx.Company = xxx.Company;