从许多记录中挑出1条记录sql [英] Picking 1 record out of many records sql

问题描述

问题：我通过在不同的表上应用多个连接来从sql server填充asp.net报告。它可以工作，但是在一个问题的地方，我会通过单元格no的帮助选择PoliceStation名称。问题是1个小区没有可以是许多警察局，所以它挑选每个警察的名字并在报告中显示它。我只需要一个警察局名称，我需要从PoliceStations表格



查询：

  SELECT  [ID] 
， LEFT （REPLACE（ convert （ varchar ，CmsSMSDb.dbo.SendMessages.SendingDateTime， 113 ），' '，' < span class =code-string> /'）， 11 ）+ ' < span class =code-string>' + 
  RIGHT （REPLACE（ convert （ varchar ，CmsSMSDb.dbo.SendMessages.SendingDateTime， 113 ），' '，'  /'）， 12 ）
  as  SendingDateTime 
，[ToMobileNo] 
，[Message] 
，Designations.Name  as  [ToDesignation] 
 < span class =code-keyword> FROM  [CmsSMSDb]。[dbo]。[SendMessages] 
  inner   join  
 CPOCMS.dbo.Designations 
  ON  CPOCMS.dbo.Designations.MobileNo = CmsSMSDb.dbo.SendMessages .ToMobileNo 
 其中 转换（ date ，CmsSMSDb.dbo.SendMessages.SendingDateTime）> =  @ DateFrom  
  AND  
 转换（ date ，CmsSMSDb.dbo.SendMessages.SendingDa teTime）< =  @ DateTo  
 
 
 联盟 全部 
 
  SELECT  [ID] 
， LEFT （REPLACE（ convert （ varchar ，CmsSMSDb.dbo.SendMessages。 SendingDateTime， 113 ），' '， '  /'）， 11 ）+ ' ' + 
  RIGHT  （REPLACE（ convert （ varchar ，CmsSMSDb.dbo.SendMessages.SendingDateTime， 113 ），' '，'  /'）， 12 ）
  as  SendingDateTime 
，[ToMobileNo] 
，[Message] 
，'  SDpo' + ' ' + CPOCMS.dbo.PoliceStations.PsName  as  [ToDesignation] 
  From  [CmsSMSDb]。[ dbo]。[SendMessages] 
  inner   join  CPOCMS.dbo.PoliceStations 
 < span class =code-keyword> ON  CPOCMS.dbo.PoliceStations.sDpo_ContactNo = SendMessages.ToMobileNo 
 其中 转换（ date ，CmsSMSDb.dbo.SendMessages.SendingDateTime）> =  @ DateFrom  
  AND  
 转换（ date ，CmsSMSDb.dbo.SendMessages.SendingDateTime）< =  @ DateTo  
  order   by  SendMessages.ID 

eg



City Police St. | 0900800800

Bill Rd PoliceSt | 0900800800



i amy只想发送给一个。

解决方案

SELECT TOP 1 * FROM YOUR_STUFF ORDER BY NEWID（）

Problem: I'm filling asp.net report from sql server by applying multiple joins on different tables. It works but creates a problem where am picking PoliceStation name by help of cell no. Problem is that 1 cell no can be of many police stations, so it picks each Policestation name and disply that it in report. I just want 1 police station name which i require from PoliceStations table

Query:

SELECT [ID]
      ,LEFT(REPLACE(convert(varchar, CmsSMSDb.dbo.SendMessages.SendingDateTime, 113), ' ','/'), 11) + ' ' +
       RIGHT(REPLACE(convert(varchar, CmsSMSDb.dbo.SendMessages.SendingDateTime, 113), ' ','/'), 12)
      as SendingDateTime
      ,[ToMobileNo]
      ,[Message]
      ,Designations.Name as [ToDesignation]
      FROM [CmsSMSDb].[dbo].[SendMessages] 
      inner join 
      CPOCMS.dbo.Designations
      ON CPOCMS.dbo.Designations.MobileNo = CmsSMSDb.dbo.SendMessages.ToMobileNo
      where Convert(date,CmsSMSDb.dbo.SendMessages.SendingDateTime)>= @DateFrom 
      AND 
      Convert(date,CmsSMSDb.dbo.SendMessages.SendingDateTime)<= @DateTo


      Union All

      SELECT [ID]
      ,LEFT(REPLACE(convert(varchar, CmsSMSDb.dbo.SendMessages.SendingDateTime, 113), ' ','/'), 11) + ' ' +
       RIGHT(REPLACE(convert(varchar, CmsSMSDb.dbo.SendMessages.SendingDateTime, 113), ' ','/'), 12)
      as SendingDateTime
      ,[ToMobileNo]
      ,[Message]
      ,'SDpo'+' '+CPOCMS.dbo.PoliceStations.PsName as [ToDesignation]
      From [CmsSMSDb].[dbo].[SendMessages] 
      inner join CPOCMS.dbo.PoliceStations
      ON CPOCMS.dbo.PoliceStations.sDpo_ContactNo = SendMessages.ToMobileNo
      where Convert(date,CmsSMSDb.dbo.SendMessages.SendingDateTime)>= @DateFrom 
      AND 
      Convert(date,CmsSMSDb.dbo.SendMessages.SendingDateTime)<= @DateTo
      order by SendMessages.ID

e.g.

City Police St. | 0900800800
Bill Rd PoliceSt | 0900800800

i amy just want to send to one .

解决方案

SELECT TOP 1 * FROM YOUR_STUFF ORDER BY NEWID()

这篇关于从许多记录中挑出1条记录sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！