gpt4 book ai didi

sql - 如何查找组的行数?

转载 作者:行者123 更新时间:2023-12-02 05:19:29 25 4
gpt4 key购买 nike

我正在用 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

最佳答案

你可以这样做:

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 的情况下使用子查询或相关子查询编写它。

关于sql - 如何查找组的行数?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/14108272/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com