SQL选择查询问题 [英] SQL Select Query problem

问题描述

我有一张带有ID（自动编号），dealerMobile，totalCost和deliveryDate的表格。

一些示例数据：

  ID，dealerMobile，totalCost，deliveryDate  
 1,01723325484， 5000,20-Apr-14 
 2,01723325484,1000,22-Apr-14 
 3,01723325484,2000,24-Apr-14 
 4,01852136544,7000,24-Apr -14 

我需要根据最后插入的dealerMobile显示一行。那就是如果我搜索01723325484那么它只显示第三行。目前我正在应用以下查询，它不能正常工作。

  SELECT   DISTINCT  dealerMobile，totalCost，deliveryDate  FROM  tbMain  WHERE  dealerMobile = < span class =code-string>' + txtdealerMobile.Text +'  ORDER   BY  deliveryDate  DESC  

请帮忙。

解决方案

你想在SQL中使用find-Nth-pattern

 选择 dealerMobile，totalCost，deliveryDate 
  FROM  
（
 选择 ROW_NUMBER（） over （ partition   by  dealerMobile，totalCost， convert （ date ，deliveryDate ）订单 按 dealerMobile，totalCost，转换 （ date ，deliveryDate）） as  rownr，
 dealerMobile，totalCost，deliveryDate 
 来自 tbMain 
 其中 dealerMobile = ' 在此设置所需数字'   -   < - 您在这里的输入 
 订单 按 deliverydate  desc  
） as  rows 
  where 行.rownr =  1  

更原始的ansi sql方法来做同样的事情

  SELECT  dealerMobile，totalCost，deliveryDate 
  FROM  tbMain 
 其中 dealerMobile = '  01723325484' 
 和 deliveryDate =（选择 max（deliveryDate）来自 tblMain 其中 dealerMobile = '  01723325484' ）

I have a table with ID(auto number),dealerMobile, totalCost and deliveryDate.
Some example data:

ID, dealerMobile, totalCost, deliveryDate
1,  01723325484,  5000,      20-Apr-14
2,  01723325484,  1000,      22-Apr-14
3,  01723325484,  2000,      22-Apr-14
4,  01852136544,  7000,      23-Apr-14

I need to Show only one row based on dealerMobile which is lastly insert. That is If I Search 01723325484 then It show me the third row only. Currently I am applying the below query, It's not working as I want.

SELECT DISTINCT dealerMobile, totalCost, deliveryDate FROM tbMain WHERE dealerMobile = '" + txtdealerMobile.Text + "' ORDER BY deliveryDate DESC

Please help.

解决方案

You want to use the find-Nth-pattern in SQL

Select dealerMobile, totalCost, deliveryDate
FROM
(
    select ROW_NUMBER() over (partition by  dealerMobile, totalCost, convert(date, deliveryDate) order by dealerMobile, totalCost, convert(date, deliveryDate)) as rownr,
     dealerMobile, totalCost, deliveryDate
    from tbMain
    where dealerMobile='set the wanted number here' -- <-- your input here
    order by deliverydate desc
) as rows
where rows.rownr = 1

a more primitive ansi sql way to do the same

SELECT dealerMobile, totalCost, deliveryDate 
FROM   tbMain
where  dealerMobile = '01723325484'
and    deliveryDate = (Select max(deliveryDate) from tblMain where dealerMobile = '01723325484')

这篇关于SQL选择查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！