SQL 查询单价的问题

nebuung 2018-06-13 08:38:13
图一是数据库的订单明细，怎么才能做出图二那样能根据时间的desc查出物料的最近三次采购订单号及采购单价？（图为单物料，应用场景为多物料）
听说使用游标可以搞定，但没找到思路，求大神指点，贴代码更可爱。


...全文
695 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
nebuung 2018-06-13
  • 打赏
  • 举报
 回复
引用 4 楼 sinat_28984567 的回复:
有点问题，没看清是根据时间排序的，这样改一下 
;WITH cte AS (
SELECT *,
       ROW_NUMBER() OVER (PARTITION BY fitem ORDER BY fdate DESC) rn
FROM #T
)
SELECT a.fitem,
       a.funit,
       a.fcurr,
       a.fpono AS fpono1,
       a.fprice AS fprice1,
       b.fpono AS fpono2,
       b.fprice AS fprice2,
       c.fpono AS fpono3,
       c.fprice AS fprice3
FROM cte a
    LEFT JOIN cte b
        ON b.fitem = a.fitem
           AND a.rn = b.rn - 1
    LEFT JOIN cte c
        ON c.fitem = a.fitem
           AND a.rn = c.rn - 2
WHERE a.rn = 1;
学习了，谢谢斑竹。
xiaoxiangqing 2018-06-13
  • 打赏
  • 举报
 回复
先查出最近3次数据，排好序，再分组显示
二月十六 2018-06-13
  • 打赏
  • 举报
 回复
有点问题，没看清是根据时间排序的，这样改一下
;WITH cte AS (

SELECT *,

       ROW_NUMBER() OVER (PARTITION BY fitem ORDER BY fdate DESC) rn

FROM #T

)

SELECT a.fitem,

       a.funit,

       a.fcurr,

       a.fpono AS fpono1,

       a.fprice AS fprice1,

       b.fpono AS fpono2,

       b.fprice AS fprice2,

       c.fpono AS fpono3,

       c.fprice AS fprice3

FROM cte a

    LEFT JOIN cte b

        ON b.fitem = a.fitem

           AND a.rn = b.rn - 1

    LEFT JOIN cte c

        ON c.fitem = a.fitem

           AND a.rn = c.rn - 2

WHERE a.rn = 1;


二月十六 2018-06-13
  • 打赏
  • 举报
 回复
游标效率比较低，试试这个
--测试数据

if not object_id(N'Tempdb..#T') is null

	drop table #T

Go

Create table #T([fdate] Date,[fpono] nvarchar(27),[fsupplier] int,[ftype] nvarchar(22),[fitem] nvarchar(25),[funit] nvarchar(24),[fqty] int,[fcurr] nvarchar(23),[fprice] decimal(18,12),[famount] decimal(18,12))

Insert #T

select '2017-12-11',N'1424-61',13056,N'MS',N'碳带',N'EACH',100,N'CNY',10.940171,1094.0171 union all

select '2017-10-16',N'1217-61',13056,N'MS',N'碳带',N'EACH',50,N'CNY',75.21368,3760.684 union all

select '2017-07-06',N'875-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.46,82800 union all

select '2017-07-06',N'876-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.46,82800 union all

select '2017-07-06',N'880-61',13056,N'MS',N'碳带',N'EACH',5,N'CNY',75.213675,376.068375 union all

select '2017-06-05',N'765-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.46,82800 union all

select '2017-06-05',N'766-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.46,82800 union all

select '2017-04-05',N'565-61',61001,N'IN',N'12636',N'EACH',225000,N'USD',0.46,103500 union all

select '2017-04-05',N'566-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.46,82800 union all

select '2017-04-05',N'567-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.46,82800 union all

select '2017-04-05',N'568-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.46,82800 union all

select '2017-03-09',N'484-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.46,82800 union all

select '2017-03-09',N'484-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.46,82800 union all

select '2017-03-06',N'447-61',13056,N'MS',N'碳带',N'EACH',10,N'CNY',75.2137,752.137 union all

select '2016-12-16',N'262-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.46,82800 union all

select '2016-12-16',N'263-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.47147,84864.6 union all

select '2016-12-16',N'264-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.47147,84864.6 union all

select '2016-12-16',N'265-61',61001,N'IN',N'12636',N'EACH',270000,N'USD',0.47147,127296.9 union all

select '2016-12-16',N'266-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.46,82800 union all

select '2016-12-16',N'267-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.46,82800 union all

select '2016-12-16',N'268-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.46,82800 union all

select '2016-12-16',N'269-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.46,82800 union all

select '2016-12-07',N'200-61',13056,N'MS',N'碳带',N'EACH',3,N'CNY',75.213675,225.641025 union all

select '2016-11-04',N'85-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.47147,84864.6 union all

select '2016-10-14',N'36-61',13056,N'MS',N'碳带',N'EACH',26,N'CNY',75.2137,1955.5562 union all

select '2016-09-30',N'10-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.47147,84864.6 union all

select '2016-09-30',N'11-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.47147,84864.6 union all

select '2016-09-30',N'11-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.47147,84864.6 union all

select '2016-09-30',N'12-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.47147,84864.6 union all

select '2016-09-30',N'13-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.4715,84870 union all

select '2016-09-30',N'14-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.4715,84870 union all

select '2016-09-30',N'9-61',61001,N'IN',N'12636',N'EACH',360000,N'USD',0.47147,169729.2

Go

--测试数据结束

;WITH cte AS (

SELECT *,

       ROW_NUMBER() OVER (PARTITION BY fitem ORDER BY fprice DESC) rn

FROM #T

)

SELECT a.fitem,

       a.funit,

       a.fcurr,

       a.fpono AS fpono1,

       a.fprice AS fprice1,

       b.fpono AS fpono2,

       b.fprice AS fprice2,

       c.fpono AS fpono3,

       c.fprice AS fprice3

FROM cte a

    LEFT JOIN cte b

        ON b.fitem = a.fitem

           AND a.rn = b.rn - 1

    LEFT JOIN cte c

        ON c.fitem = a.fitem

           AND a.rn = c.rn - 2

WHERE a.rn = 1;


nebuung 2018-06-13
  • 打赏
  • 举报
 回复
引用 1 楼 sinat_28984567 的回复:
先用rownum分组取各个物料的前三名形成rn列自增列作为临时表，然后临时表自己关联自己三次每次都是rn=rn-1 物料=物料这样关联，然后就取到三个订单号和三个单价依次排列的结果了。 楼主如果能给出测试数据就更可爱了，可以帮你写代码语句，贴图片没法弄测试数据。
斑竹大大您要的测试数据，我这里拿了两个fitem 如果用游标能搞吗，我对游标在这的应用有兴趣 fdate fpono fsupplier ftype fitem funit fqty fcurr fprice famount 2017-12-11 00:00:00.000 1424-61 13056 MS 碳带 EACH 100 CNY 10.940171 1094.0171 2017-10-16 00:00:00.000 1217-61 13056 MS 碳带 EACH 50 CNY 75.21368 3760.684 2017-07-06 00:00:00.000 875-61 61001 IN 12636 EACH 180000 USD 0.46 82800 2017-07-06 00:00:00.000 876-61 61001 IN 12636 EACH 180000 USD 0.46 82800 2017-07-06 00:00:00.000 880-61 13056 MS 碳带 EACH 5 CNY 75.213675 376.068375 2017-06-05 00:00:00.000 765-61 61001 IN 12636 EACH 180000 USD 0.46 82800 2017-06-05 00:00:00.000 766-61 61001 IN 12636 EACH 180000 USD 0.46 82800 2017-04-05 00:00:00.000 565-61 61001 IN 12636 EACH 225000 USD 0.46 103500 2017-04-05 00:00:00.000 566-61 61001 IN 12636 EACH 180000 USD 0.46 82800 2017-04-05 00:00:00.000 567-61 61001 IN 12636 EACH 180000 USD 0.46 82800 2017-04-05 00:00:00.000 568-61 61001 IN 12636 EACH 180000 USD 0.46 82800 2017-03-09 00:00:00.000 484-61 61001 IN 12636 EACH 180000 USD 0.46 82800 2017-03-09 00:00:00.000 484-61 61001 IN 12636 EACH 180000 USD 0.46 82800 2017-03-06 00:00:00.000 447-61 13056 MS 碳带 EACH 10 CNY 75.2137 752.137 2016-12-16 00:00:00.000 262-61 61001 IN 12636 EACH 180000 USD 0.46 82800 2016-12-16 00:00:00.000 263-61 61001 IN 12636 EACH 180000 USD 0.47147 84864.6 2016-12-16 00:00:00.000 264-61 61001 IN 12636 EACH 180000 USD 0.47147 84864.6 2016-12-16 00:00:00.000 265-61 61001 IN 12636 EACH 270000 USD 0.47147 127296.9 2016-12-16 00:00:00.000 266-61 61001 IN 12636 EACH 180000 USD 0.46 82800 2016-12-16 00:00:00.000 267-61 61001 IN 12636 EACH 180000 USD 0.46 82800 2016-12-16 00:00:00.000 268-61 61001 IN 12636 EACH 180000 USD 0.46 82800 2016-12-16 00:00:00.000 269-61 61001 IN 12636 EACH 180000 USD 0.46 82800 2016-12-07 00:00:00.000 200-61 13056 MS 碳带 EACH 3 CNY 75.213675 225.641025 2016-11-04 00:00:00.000 85-61 61001 IN 12636 EACH 180000 USD 0.47147 84864.6 2016-10-14 00:00:00.000 36-61 13056 MS 碳带 EACH 26 CNY 75.2137 1955.5562 2016-09-30 00:00:00.000 10-61 61001 IN 12636 EACH 180000 USD 0.47147 84864.6 2016-09-30 00:00:00.000 11-61 61001 IN 12636 EACH 180000 USD 0.47147 84864.6 2016-09-30 00:00:00.000 11-61 61001 IN 12636 EACH 180000 USD 0.47147 84864.6 2016-09-30 00:00:00.000 12-61 61001 IN 12636 EACH 180000 USD 0.47147 84864.6 2016-09-30 00:00:00.000 13-61 61001 IN 12636 EACH 180000 USD 0.4715 84870 2016-09-30 00:00:00.000 14-61 61001 IN 12636 EACH 180000 USD 0.4715 84870 2016-09-30 00:00:00.000 9-61 61001 IN 12636 EACH 360000 USD 0.47147 169729.2
二月十六 2018-06-13
  • 打赏
  • 举报
 回复
先用rownum分组取各个物料的前三名形成rn列自增列作为临时表，然后临时表自己关联自己三次每次都是rn=rn-1 物料=物料这样关联，然后就取到三个订单号和三个单价依次排列的结果了。 楼主如果能给出测试数据就更可爱了，可以帮你写代码语句，贴图片没法弄测试数据。
mysql查询并计算单价,sql语句查询今年单价总金额
sqlserver :1. 一天的订单总额 : select sum(total ) from orderswhere created_at>='2019-05-16 00:00:00'and created_at一天24小时各个小时的金额总数select convert(varchar(2),GETDATE(),14) ,sum(total ) from orderswh...
excel vba使用SQL查询最新日期各厂、物料单价
表头() EXCEL2007 工作表名称：MX Sub CreateRecordset1() Application.Calculation = xlManual Dim cnn As Object Dim rst As Object Dim strPath As String Dim strSQL As String Dim lngCount As ...
SQL查询语句详解
SQL查询语句详解（一） 一、基本语法 Select select_list From table_name Where condition_expression Group by group_columns having condition_expression Order by sort_columns 二、查询实例 查询所有字段 查询指定字段 用DISTINCT去除结果中的重复行 查询指定数据 带有in关键字的查询 带between and的范围查询 带like的字符匹配查询 表1：
sql学习第3天复杂一些的查询
知识点 关联子查询 关联子查询就是通过一些标志将内外两层的查询连接起来起到过滤数据的目的，接下来我们就一起看一下关联子查询的具体内容吧。 关联子查询与子查询的联系 还记得我们之前的那个例子么查询出销售单价高于平均销售单价的商品，这个例子的SQL语句如下 SELECT product_id, product_name, sale_price FROM product WHERE sale_price > (SELECT AVG(sale_price) FROM product); 我们再来看
mysql实验二:图书管理系统之SQL查询语言案例
声明:本博文如存在问题，欢迎各位dalao指正！！！ 目的与任务： 要求熟练掌握SQL查询语言的基本查询、条件查询、多表查询、统计查询。 这里将所有sql查询语句都放在一起了，直接全部复制到navicat中既可，另外实验一中只有表格没有数据，下面将数据附上。 链接：https://pan.baidu.com/s/14YjOhxUud8-dje3lk2bXKg 提取码：tsvt -- 1、从...
疑难问题

22,206

社区成员

121,729

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章