xml地图|网站地图|网站标签 [设为首页] [加入收藏]
开窗函数Over,数据库还原的问题
分类:数据库

利用over(),将统计信息计算出来,然后直接筛选结果集

还原sql server 2012数据库时,经常会出现:

Dim sql As String = " SELECT xp.*, "
            sql = sql + "        xf_owner.ownername, "
            sql = sql + "        xf_receiver.receivename, "
            sql = sql + "        lastprice = "
            sql = sql + "        ( "
            sql = sql + "            SELECT TOP 1 "
            sql = sql + "                   lp.tonprice "
            sql = sql + "            FROM "
            sql = sql + "            ( "
            sql = sql + "                SELECT * "
            sql = sql + "                FROM xf_price "
            sql = sql + "                WHERE pk_price IN ( "
            sql = sql + "                                      SELECT TOP 2 "
            sql = sql + "                                             pk_price "
            sql = sql + "                                      FROM xf_price "
            sql = sql + "                                      WHERE pk_pricegroup = xp.pk_pricegroup "
            sql = sql + "                                            AND pk_owner = xp.pk_owner "
            sql = sql + "                                            AND pk_receiver = xp.pk_receiver "
            sql = sql + "                                      ORDER BY enabletime DESC "
            sql = sql + "                                  ) "
            sql = sql + "            ) lp "
            sql = sql + "            ORDER BY lp.enabletime ASC "
            sql = sql + "        ), "
            sql = sql + "        transname = CASE "
            sql = sql + "                        WHEN transtype = 0 THEN "
            sql = sql + "                            '外部运输' "
            sql = sql + "                        WHEN transtype = 1 THEN "
            sql = sql + "                            '厂内转运' "
            sql = sql + "                        ELSE "
            sql = sql + "                            '厂外转运' "
            sql = sql + "                    END "
            sql = sql + " FROM xf_price xp "
            sql = sql + "     LEFT JOIN xf_owner "
            sql = sql + "         ON xf_owner.pk_owner = xp.pk_owner "
            sql = sql + "     LEFT JOIN xf_receiver "
            sql = sql + "         ON xf_receiver.pk_receiver = xp.pk_receiver "
            sql = sql + " WHERE xp.dr = 0 "
            sql = sql + "       AND xp.pk_pricegroup = @pk_pricegroup "
            sql = sql + "       AND pk_price IN ( "
            sql = sql + "                           SELECT TOP 1 "
            sql = sql + "                                  pk_price "
            sql = sql + "                           FROM xf_price "
            sql = sql + "                           WHERE pk_pricegroup = xp.pk_pricegroup "
            sql = sql + "                                 AND pk_owner = xp.pk_owner "
            sql = sql + "                                 AND pk_receiver = xp.pk_receiver "
            sql = sql + "                           ORDER BY enabletime DESC "
            sql = sql + "                       ) "
            sql = sql + " ORDER BY xp.enabletime DESC; "
            Dim com As New SqlClient.SqlCommand(sql, strCon)
            com.Parameters.Add(New SqlClient.SqlParameter("@pk_pricegroup", pk_pricegroup))
            Dim ds As New DataSet
            Dim adapter As New SqlClient.SqlDataAdapter(com)
            strCon.Open()
            adapter.Fill(ds, "xf_price")
            strCon.Close()

主要思路:先取整体结果集,然后条件取按时间倒序排第一条记录的主键,然后有个字段需要取上一次修改的结果值(别名lastprice,即上次修改的值tonprice),则采用先TOP 2 取两条数据,然后按时间倒序排,再TOP 1取第一条便是上一次修改的结果值。
注意xf_price xp 的妙用,可以解决当主键条件取第一条时只显示一条返回记录的问题。
 1 declare @t table(
 2 ProductID int,
 3 ProductName varchar(20),
 4 ProductType varchar(20),
 5 Price int)
 6  
 7 insert @t
 8 select 1,'name1','P1',3 union all
 9 select 2,'name2','P1',5 union all
10 select 3,'name3','P2',4 union all
11 select 4,'name4','P2',4

“因为数据库正在使用,所以无法获得对数据库的独占访问权”,

 

 

我关闭了sql查询窗口,关了连接的客户端,关闭了浏览器上相关的访问网页,甚至关闭了IIS,然而,并没有什么用

查询要求:查出每类产品中价格最高的信息

 

--做法一:找到每个组里,价格最大的值;然后再找出每个组里价格等于这个值的
--缺点:要进行一次join     

百度到了一个解决方法,最终解决了问题

select t1.* from @t t1
  join (select ProductType, max(Price) Price from @t group by ProductType) t2 
  on t1.ProductType = t2.ProductType
 where t1.Price = t2.Price
 order by ProductType

还原前执行

本文由澳门新葡亰手机版发布于数据库,转载请注明出处:开窗函数Over,数据库还原的问题

上一篇:SQL一次性查询一个字段不同条件下的统计结果, 下一篇:没有了
猜你喜欢
热门排行
精彩图文