SQL Server中的唯一行

Question

我要显示SQL Server数据库中的唯一行。我有两个表名为UploadedData和DummyReportDetail。我想连接这些表，但是使用distinct并没有得到唯一的行。

这是我的问题：

SELECT DISTINCT 
    D.TokenNo, D.UploadDate AS "Date", D.[Time], 
    D.SMSContent AS [Message], D.SMSType AS [SMS Type], 
    (SELECT COUNT(d.MobNo) FROM dbo.DummyReportDetail AS d 
     WHERE d.UploadDate = '31/12/2012' AND d.UserID = '2' 
       AND d.UserType = 'Reseller' AND d.TokenNo = U.TokenNo) AS "Count",
    D.DStatus AS "Status" 
FROM    
    dbo.DummyReportDetail AS D 
INNER JOIN 
    dbo.UploadedData AS U ON D.TokenNo = U.TokenNo 
WHERE 
    D.UploadDate = '31/12/2012' AND D.UserID = '2' 
    AND D.UserType = 'Reseller' AND D.TokenNo = U.TokenNo  
ORDER BY 
    D.TokenNo DESC

我对这个查询的输出是：

Token-4 31/12/2012   11:23:59 AM   संपादकीय Unicode  2  SUBMITED
Token-4 31/12/2012   11:24:9 AM    संपादकीय Unicode  2  SUBMITED
Token-3 31/12/2012   10:21:31 AM   TestSMS Normal  1 SUBMITED

但是我想要这个输出：

Token-4 31/12/2012  11:24:9 AM  संपादकीय    Unicode  2  SUBMITED
Token-3 31/12/2012 10:21:31 AM  TestSMS    Normal   1  SUBMITED

请帮帮我。提前感谢

Answer 1

 select distinct D.TokenNo, D.UploadDate as "Date",D.[Time], D.SMSContent as "Message",D.SMSType as "SMS Type", 
        (select count (d.MobNo) from dbo.DummyReportDetail as d 
           where d.UploadDate='31/12/2012' and d.UserID='2' 
           and d.UserType='Reseller' and d.TokenNo=U.TokenNo) as "Count"
          ,D.DStatus as "Status"
    from dbo.DummyReportDetail as D join dbo.UploadedData as U
    on D.TokenNo=U.TokenNo 
    where D.UploadDate='31/12/2012' and D.UserID='2' 
    and D.UserType='Reseller' and D.TokenNo=U.TokenNo  group by D.tokenNo  order by D.TokenNo desc

按TokenNo添加组

Answer 2

您将需要应用分组以实现所需的内容。在这种情况下，您需要对每一列进行分组，并获取最大日期：

select D.TokenNo, D.[Time], D.SMSContent as "Message",D.SMSType as "SMS Type", 
(select count (d.MobNo) from dbo.DummyReportDetail as d 
   where d.UploadDate='31/12/2012' and d.UserID='2' 
   and d.UserType='Reseller' and d.TokenNo=U.TokenNo) as "Count"
  ,D.DStatus as "Status", MAX(D.UploadDate) as "Date"
from dbo.DummyReportDetail as D join dbo.UploadedData as U
on D.TokenNo=U.TokenNo 
where D.UploadDate='31/12/2012' and D.UserID='2'
group by D.TokenNo, D.[Time], D.SMSContent, D.SMSType, Count, D.DStatus
and D.UserType='Reseller' and D.TokenNo=U.TokenNo  order by D.TokenNo desc

此外，您可能还希望考虑重写查询，以使用分组来替换"Count“字段的当前内部查询。连接表将比当前查询更有效，因为连接只执行一次，而现在您在结果集中得到的每一行都运行一次子查询。