按日期计算项目？ （SQL） [英] Count items in column by date? (SQL)

问题描述

您好！

我正在使用Microsoft SQL Server 2005

我有一个这样的表：

Hello!
I am using Microsoft SQL Server 2005
I have a table like this:

QueueNo      Date
 3B001     5/14/2014
 3B002     5/13/2014 
 3B003     5/13/2014
 3G004     5/12/2014
 3G005     5/12/2014
 2P001     5/12/2014
 2G002     5/12/2014
 2P003     5/14/2014  
 1P001     5/12/2014

i在上表中使用此代码：

i used this code for the table above:

SELECT     queueno, CONVERT(varchar(10), qdate, 101) AS DateToday
FROM         TRN_Queue

我想创建一个新表来计算所有以'3'，'2'和'1'但是买日期。

这样的东西：

I want to make a new table that counts all the ones that start with '3','2', and '1' but buy date.
something like this:

   Date     QueueInitial    Count
5/14/2014         3           1
5/14/2014         2           1
5/13/2014         3           2
5/12/2014         3           2
5/13/2014         2           2
5/13/2014         1           1

非常感谢提前，就像真的一样！哈哈：）

Super thanks in advance, like really! HAHA :)

推荐答案

因此，请先阅读上一个问题：列中的项目计数？ （SQL） [ ^ ]并看看它是如何工作的。这是一个非常简单的修改，并且学习如何自己做这件事将会为你节省很多时间 - 自己做这件事要比让别人为你做的更快 - 而且它防止你被虐待标记为让我们在一点点做所有工作的人。我们希望您考虑一下您在做什么！ （并且标记为滥用将很可能会禁止您的帐户）

So take teh anser to your previous question: Count items in column? (SQL)[^] and look at how it works. It's a pretty simple modification to that to do this, and learning how to do it yourself will save you a lot of time in the future - it really is quicker to do it yourself than to ask someone else to do it for you - and it prevents you getting "abuse" marked as someone who gets us to do all his work in little bits. We do expect you to think about what you are doing! (And getting marked as abusive will very likely get your account banned)

我同意OriginalGriff，尝试查看此查询，看看您是否可以根据自己的需要调整它：

I agree with OriginalGriff, try looking at this query and see if you can adapt it to your needs though:

DECLARE @TRN_Queue TABLE
(
	QueueNo NVARCHAR(5),
	[Date] DATETIME2
);

INSERT INTO @TRN_Queue VALUES('3B001', '5-10-2014')
INSERT INTO @TRN_Queue VALUES('3B002', '5-09-2014')
INSERT INTO @TRN_Queue VALUES('3B003', '5-09-2014')
INSERT INTO @TRN_Queue VALUES('3G004', '5-11-2014')
INSERT INTO @TRN_Queue VALUES('3G005', '5-11-2014')
INSERT INTO @TRN_Queue VALUES('2P001', '5-11-2014')
INSERT INTO @TRN_Queue VALUES('2G002', '5-11-2014')
INSERT INTO @TRN_Queue VALUES('2P003', '5-08-2014')
INSERT INTO @TRN_Queue VALUES('1P001', '5-11-2014')

SELECT SUBSTRING(QueueNo, 1, 1), COUNT(SUBSTRING(QueueNo, 1, 1)), COUNT(SUBSTRING(QueueNo, 1, 1)) OVER() FROM @TRN_Queue
GROUP BY SUBSTRING(QueueNo, 1, 1)

希望它有帮助......

Hope it helps...

这篇关于按日期计算项目？ （SQL）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！