SQL 在sql的子查询中使用group by子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8320074/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
using group by clause in subquery in sql
提问by vaichidrewar
I am trying to use group by clause in subquery which is in from clause
我试图在来自子句的子查询中使用 group by 子句
select userID,count(id)
from
(
(
select id,max(bidAmount),userID
from Bids
group by id,bidAmount
)
group by userID
);
but this gives error
但这给出了错误
Error: near "group": syntax error
错误：“组”附近：语法错误
Is it possible to use group by clause in subquery in from clause in sql?
是否可以在 sql 的 from 子句中的子查询中使用 group by 子句？
回答by u710480
Check your (), they are not at right places. Should be something more like this:
检查您的 ()，它们不在正确的位置。应该更像这样：
select w.userID,count(w.id)
from (select id,max(bidAmount),userID from Bids group by id, userID) w
group by w.userID
回答by Jake Feasel
Try this:
尝试这个：
select userID,count(id)
from (
select id,max(bidAmount),userID from Bids group by id,userID
) as tmp
group by userID
回答by vaichidrewar
You can use group byin a subquery, but your syntax is off.
您可以group by在子查询中使用，但您的语法已关闭。
select userID,count(id)
from
(
select id,max(bidAmount),userID
from Bids
group by id,userID
)
GROUP BY userid
回答by martin
my Problem is:
我的问题是：
in this query I will know, how many assignment are inside one article-Number. So I write this query:
在此查询中，我将知道一篇文章编号中有多少作业。所以我写了这个查询：
SELECT
Auftrag_det.BNR AS Auftrag_Nr,
Artikel.ZNR_ALPHAN AS Artikel_Nr,
Artikel.Bezeichnung1, Auftrag_det.R_STK AS Menge,
CONVERT(nvarchar, DATENAME(iso_Week, Auftrag.DAT_WUTER)) + N'/' + CONVERT(nvarchar, DATENAME(yy, Auftrag.DAT_WUTER)) AS KW_Jahr,
Artikel.BestandFrei,
Artikel.BestandReserviert,
Artikel.BestandGesperrt,
Artikel.BestandBestelltFrei,
Artikel.BestandBestelltReserviert,
(Select Count(a.BNR)
from Auftrag a, Auftrag_det b, Artikel c
Where b.ZNR_ALPHAN=c.ZNR_ALPHAN
AND (a.BNR = b.BNR)
AND (a.ERLEDIGT IS NULL)
AND (a.DAT_WUTER IS NOT NULL)
AND (c.Bezeichnung1 <> N'Verpackungskosten')
AND (b.R_STK > 0)
)
FROM Auftrag_det
INNER JOIN Auftrag ON Auftrag_det.BNR = Auftrag.BNR
INNER JOIN Artikel ON Auftrag_det.ZNR_ALPHAN = Artikel.ZNR_ALPHAN
WHERE (Auftrag_det.ERLEDIGT IS NULL)
AND (Auftrag.DAT_WUTER IS NOT NULL)
AND (Artikel.Bezeichnung1 <> N'Verpackungskosten')
AND (Auftrag_det.R_STK > 0)
I get a result, but not per article Special all articles.
I can't Group in the Subquery for ZNR_ALPHAN(this are the article-number)
我得到一个结果，但不是每篇文章特别所有的文章。
我不能在子查询中分组ZNR_ALPHAN（这是文章编号）
