在单个查询中具有多个选定结果的多个select语句

I am trying to link multiple selects into one query i have looked at another thread on here which helped greatly but this only accounts for single select options like so.

我试图将多个选项链接到一个查询我已经查看了这里的另一个线程，这有很大帮助，但这只考虑单选项，如此。

  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

but my issue is that i am trying to get multiple options from each select like so.

但我的问题是我试图从每个选择中获得多个选项。

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'

I keep getting the error "#1241 - Operand should contain 1 column(s)"

我一直收到错误“＃1241 - 操作数应包含1列”

From what i have read this is because i am trying to link 3 different values under one selector which i dont want to do.

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

This bit of code at the end is what the problem is.

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

) AS 'No. of Web Cases Generated'

I have tried this to resolve but obviously am missing the expertise to get it right.

我试过这个来解决，但很明显我错过了正确的专业知识。

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

This doesnt work as it doesnt know what value to allocate to what selector.

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

Any help would be greatly appreciated.

任何帮助将不胜感激。

1 个解决方案

#1

Yes that's correct behavior since you are actually trying to merge multiple column values in single column which is not possible

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

( 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',

Think what you are looking for is a UNION rather like

想想你正在寻找的是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

Per your comment, perform a JOIN then

根据您的评论，然后执行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;

#1