SQL 选择每个团队每天的信息

Question

我需要从数据库中选择一些信息，每天和每个团队。我想看看每个团队每天购买了多少物品。例如，如果在 2012-11-21 购买了 6 件商品，我希望我的结果显示：团队 1 购买了 3 件物品，团队 2 - 2 件物品和最后团队 1 件物品。我可以使用以下方法获取一个团队的信息：

select count(CONVERT(date, getdate())) as Team1
from items
where team = 1
group by CONVERT(date, getdate())

但我不确定如何获得所有团队所需的信息。

编辑

我需要结果是这样的：

Date      |Team1   | Team2   | Team3  
2012-11-21|2 items | 3 items | 0 items 

我的表结构是这样的：Date, Product, Price, Team.

Answer 1

你需要的是

像这样PIVOT你的桌子：

SELECT 
  Date,
  SUM(CASE WHEN team = 'team1' THEN 1 ELSE 0 END) 'team1',
  SUM(CASE WHEN team = 'team2' THEN 1 ELSE 0 END) 'team2',
  SUM(CASE WHEN team = 'team3' THEN 1 ELSE 0 END) 'team3'
FROM Tablename
GROUP BY date;

Answer 2

你需要

GROUP BY团队和日期。

SELECT datecol, team, COUNT(*)
FROM items
GROUP BY datecol, team

基于您更新的要求并且不知道您的 DBMS 是什么：

SELECT a.datecol AS 'Date',
    (SELECT COUNT(*) FROM items WHERE datecol = a.datecol AND team = '1') AS 'Team1',
    (SELECT COUNT(*) FROM items WHERE datecol = a.datecol AND team = '2') AS 'Team2',
    (SELECT COUNT(*) FROM items WHERE datecol = a.datecol AND team = '3') AS 'Team3'
FROM items a
GROUP BY a.datecol