sql - 如何查找组的行数？

Question

我正在用 SQL 创建一个存储过程，但我很难使用分组来查找计数。

这是我的数据库架构:

CREATE TABLE InputCurr (
  [InputCurrID] int IDENTITY(1, 1) NOT NULL,
  [Date] datetime NULL,
  [OpCurrencyName] decimal(18, 8) NULL,
  [IpCurrencyName] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [Count] int NULL,
)

数据值:

insert into InputCurr ( Date,OpCurrencyName,IpCurrencyName ) values ('1/1/2013','US','$');
insert into InputCurr ( Date,OpCurrencyName,IpCurrencyName ) values ('1/2/2013','US','$');
insert into InputCurr ( Date,OpCurrencyName,IpCurrencyName ) values ('1/3/2013','UK','#');
insert into InputCurr ( Date,OpCurrencyName,IpCurrencyName ) values ('1/4/2013','US','$');
insert into InputCurr ( Date,OpCurrencyName,IpCurrencyName ) values ('1/5/2013','US','$');

我使用以下 SP:

CREATE PROCEDURE dbo.getCurrencyReportFiels
AS
BEGIN
SELECT InputCurrID,Date,OpCurrencyName,IpCurrencyName,Count=(COUNT(*))
FROM InputCurr
GROUP BY InputCurrID,date,OpCurrencyName,IpCurrencyName
END

我得到的 O/p 是这样的:

InputCurrID    Date       OpCurrencyName      IpCurrencyName     Count
-----------------------------------------------------------------------
      1        1/1/2013       US                   $               1 <--here i want count of grouping by OpCurrencyName & IpCurrencyName
      2        1/2/2013       US                   $               1 <--here i want count of grouping by OpCurrencyName & IpCurrencyName
      3        1/3/2013       UK                   #               1
      4        1/4/2013       US                   $               1<--here i want count of grouping by OpCurrencyName & IpCurrencyName
      5        1/5/2013       US                   $               1<--here i want count of grouping by OpCurrencyName & IpCurrencyName

现在，我想要这样的输出...

InputCurrID    Date       OpCurrencyName      IpCurrencyName     Count
-----------------------------------------------------------------------
      1        1/1/2013       US                   $               4  
      2        1/2/2013       US                   $               4 
      3        1/3/2013       UK                   #               1
      4        1/4/2013       US                   $               4
      5        1/5/2013       US                   $               4

Answer 1

你可以这样做:

WITH Groups
AS
(
  SELECT 
    OpCurrencyName, 
    IpCurrencyName,
    Count=(COUNT(*))
  FROM InputCurr
  GROUP BY OpCurrencyName,IpCurrencyName
) 
SELECT 
  i.InputCurrID, 
  i.Date, 
  g.OpCurrencyName, 
  g.IpCurrencyName,
  g.Count
FROM InputCurr i
INNER JOIN Groups g  ON i.OpCurrencyName = g.OpCurrencyName
                    AND i.IpCurrencyName = g.IpCurrencyName
ORDER BY i.InputCurrID;

SQL Fiddle Demo

这会给你:

| INPUTCURRID |                           DATE | OPCURRENCYNAME | IPCURRENCYNAME | COUNT |
------------------------------------------------------------------------------------------
|           1 | January, 01 2013 00:00:00+0000 |             US |              $ |     4 |
|           2 | January, 02 2013 00:00:00+0000 |             US |              $ |     4 |
|           3 | January, 03 2013 00:00:00+0000 |             UK |              # |     1 |
|           4 | January, 04 2013 00:00:00+0000 |             US |              $ |     4 |
|           5 | January, 05 2013 00:00:00+0000 |             US |              $ |     4 |

您可以在没有 CTE 的情况下使用子查询或相关子查询编写它。