SQL选择查询问题

本文介绍了SQL选择查询问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一张带有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 = ' + 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')