如何在不重复列值的情况下显示记录 [英] how to display the records without repeating the column value

问题描述

我喜欢使用Sql server 2008，我希望根据日期显示每天的记录。

但是当iam运行我的querry时，它会计算状态列值中的重复值，即存储，交付，我不会多次计算状态列值的值多次

你可以纠正我的烦恼



以下是我的表

历史

==========

Id -uniqueidentifier

代码-nvarchar（50）

状态 - nvarchar（50）

StatusDate- smalldatetime

名称-varchar（50）

  Id  代码 状态  StatusDate  名称 
 ================================================== ================================= 
 
  70947cd3-998f   EP515859956SA  已交付 4/8/2013 < span class =code-leadattribute> 12:50:00   PM   PSRUH20300  
 
  2fcd1ea0-6740  -    EP827144739SA  已交付 4/8/2013  12:52:00   PM   PSRUH20300  
  9794c3de-37cc   EP827144739SA  已存储 4/8/2013  12:10:00   PM   PSRUH20300  
  4b344945-cbb8   EP827144739SA   Stored  4/8/2013  12:00:00   PM   PSRUH20300  
 == ==================== 
  d1b13be5-ee6b   EH000278540SA  已存储 4/8/2013  12:03:00   PM   PSRUH20300  
  9f4c2cc2-05ac   EQ131017851SA   Stored  4/8/2013  12:03:00   PM   PSRUH20300  
  2ece4352-1e02   EQ131017851SA  已存储 4/8/2013  11:53:00   AM   PSRUH20300  
 ====================== 
  ca53c856-47b7   EH001475961SA  已存储 4/8/2013  12： 02:00   PM   PSRUH20300  
  37aff000-a7df   EQ010022561SA  已存储 4/8/2013  12:00:00   PM   PSRUH20300  
  434e57be-81f8   EH001475961SA   Stored  4/8/2013  11:52:00   AM   PSRUH20300  
 ======================= 
 
  f5689228-6905   EH001479941SA  已存储 4/8/2013  12:00:00   PM   PSRUH20300  
  b7126c1d-0307   EH001479941SA  已存储 4/8/2013  12:10:00   PM   PSRUH20300  
 ======================= 

查询iam使用如下

  SELECT  COUNT（Id） AS 操作，状态，名称
  FROM 历史 AS 他的
  WHERE （DATEADD（D， 0 ，DATEDIFF（D， 0 ，StatusDate）） BETWEEN  '  2013-04-09'  AND  '  2013- 
 
 04-09' ）
  GROUP   BY 状态，名称
  ORDER   BY 名称

解决方案

请参阅：

< pre lang =sql> DECLARE @ dateFrom SMALLDATETIME
DECLARE @ dateTo SMALLDATETIME

SET @ dateFrom = ' 2013-04-09'
SET @ dateTo = ' 2013-04-09'

SELECT [状态]，[名称]，COUNT（[状态]） AS CountOfStatusForName
FROM 历史记录
WHERE 状态日期 BETWEEN @ dateFrom AND @ dateTo
GROUP BY [状态] ，[名称]
ORDER BY [姓名]，[状态]

SELECT [Status]，COUNT（[Status]） AS CountOfStatus
FROM 历史记录
WHERE StatusDate BETWEEN < span class =code-sdkkeyword> @ dateFrom AND @ dateTo
GROUP BY [状态]
ORDER BY [状态]

你看到了区别吗？

Hi Iam using Sql server 2008 , i want to display the records for every day based on date.
but when iam running my querry it is counting the repeated values in status column value ie stored,delivered ,I dont wnat count the value for Status Column value more than once
can you correct my querry

below is my table
Histories
==========
Id -uniqueidentifier
Code -nvarchar(50)
Status- nvarchar(50)
StatusDate- smalldatetime
Name -varchar(50)

Id  Code          Status           StatusDate            Name
===================================================================================

70947cd3-998f   EP515859956SA   Delivered   4/8/2013 12:50:00 PM        PSRUH20300

2fcd1ea0-6740-  EP827144739SA   Delivered   4/8/2013 12:52:00 PM        PSRUH20300
9794c3de-37cc   EP827144739SA   Stored          4/8/2013 12:10:00 PM    PSRUH20300
4b344945-cbb8   EP827144739SA   Stored          4/8/2013 12:00:00 PM    PSRUH20300
                ======================
d1b13be5-ee6b   EH000278540SA   Stored          4/8/2013 12:03:00 PM    PSRUH20300
9f4c2cc2-05ac   EQ131017851SA   Stored          4/8/2013 12:03:00 PM    PSRUH20300
2ece4352-1e02   EQ131017851SA   Stored          4/8/2013 11:53:00 AM    PSRUH20300
                ======================
ca53c856-47b7   EH001475961SA   Stored          4/8/2013 12:02:00 PM    PSRUH20300
37aff000-a7df   EQ010022561SA   Stored          4/8/2013 12:00:00 PM    PSRUH20300
434e57be-81f8   EH001475961SA   Stored          4/8/2013 11:52:00 AM    PSRUH20300
                =======================

f5689228-6905   EH001479941SA   Stored          4/8/2013 12:00:00 PM    PSRUH20300
b7126c1d-0307   EH001479941SA   Stored          4/8/2013 12:10:00 PM    PSRUH20300
                =======================

queryy iam using as below

SELECT        COUNT(Id) AS Actions, Status, Name
FROM            Histories AS his
WHERE        (DATEADD(D, 0, DATEDIFF(D, 0, StatusDate)) BETWEEN '2013-04-09' AND '2013-

04-09')
GROUP BY Status, Name
ORDER BY Name

解决方案

See this:

DECLARE @dateFrom SMALLDATETIME
DECLARE @dateTo SMALLDATETIME

SET @dateFrom = '2013-04-09'
SET @dateTo = '2013-04-09'

SELECT [Status], [Name], COUNT([Status]) AS CountOfStatusForName
FROM Histories 
WHERE StatusDate BETWEEN @dateFrom AND @dateTo
GROUP BY [Status], [Name]
ORDER BY [Name], [Status]

SELECT [Status], COUNT([Status]) AS CountOfStatus 
FROM Histories 
WHERE StatusDate BETWEEN @dateFrom AND @dateTo
GROUP BY [Status]
ORDER BY [Status]

Do you see the difference?

这篇关于如何在不重复列值的情况下显示记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！