重写SQL语句，不使用嵌套select

发布于 2024-10-24 18:41:00 字数 1129 浏览 1 评论 0原文

必须有某种方法来重写以下语句。它很丑，但是有效。必须有更好的方法。我想知道，所以我不再继续使用嵌套选择编写语句。

输出将是：

H45_134, 190
H45_180, 143

本质上，我想要获取的是不同的设备以及该设备在订单上使用的次数。在上面的输出中，“H45_134”是设备制造编号，190 是订单上使用该设备的总次数。 190 是订单上行数量的总和，但仅限于设备制造编号匹配的情况。

    SELECT distinct he1.[Mfg_Number] as HingeDeviceOneLocation, 
    (select sum(lineqty)
        FROM [MQPDatabase].[dbo].[Hinge_Edge] he2 inner join lineinfo li on li.ctr=he2.lineinfoctr 
            inner join order_header oh on oh.ctr=li.order_headerctr
            where location_1 >0 
                and location_2 =0 
                and location_3 = 0 
                and location_4=0 
                and location_5=0 
                and oh.jobnum='T35204D' 
                and he1.mfg_number=he2.mfg_number) as DeviceQty
  FROM [MQPDatabase].[dbo].[Hinge_Edge] he1 inner join lineinfo li on li.ctr=he1.lineinfoctr 
    inner join order_header oh on oh.ctr=li.order_headerctr
    where location_1 >0 
        and location_2 =0 
        and location_3 = 0 
        and location_4=0 
        and location_5=0 
        and oh.jobnum='T35204D'

原文

There has to be some way to re-write the folowing statement. It's ugly, but works. There has to be a better way. I would like to know so I don't continue writing statements with nested selects.

The output would be:

H45_134, 190
H45_180, 143

Essentially, what I'm trying to get are the distinct devices and the number of times that device is used on an order. In the output above, "H45_134" is the device mfg number and 190 is the total number of times the device is used on the order. 190 is the sum of the line quantity on the order, but only where the device mfg number matches.

    SELECT distinct he1.[Mfg_Number] as HingeDeviceOneLocation, 
    (select sum(lineqty)
        FROM [MQPDatabase].[dbo].[Hinge_Edge] he2 inner join lineinfo li on li.ctr=he2.lineinfoctr 
            inner join order_header oh on oh.ctr=li.order_headerctr
            where location_1 >0 
                and location_2 =0 
                and location_3 = 0 
                and location_4=0 
                and location_5=0 
                and oh.jobnum='T35204D' 
                and he1.mfg_number=he2.mfg_number) as DeviceQty
  FROM [MQPDatabase].[dbo].[Hinge_Edge] he1 inner join lineinfo li on li.ctr=he1.lineinfoctr 
    inner join order_header oh on oh.ctr=li.order_headerctr
    where location_1 >0 
        and location_2 =0 
        and location_3 = 0 
        and location_4=0 
        and location_5=0 
        and oh.jobnum='T35204D'

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

余生再见 2024-10-31 18:41:00

尝试一下。

SELECT he1.[Mfg_Number] as HingeDeviceOneLocation, sum(lineqty) as DeviceQty
  FROM [MQPDatabase].[dbo].[Hinge_Edge] he1 
    inner join lineinfo li on li.ctr=he1.lineinfoctr 
    inner join order_header oh on oh.ctr=li.order_headerctr
    where location_1 >0 
        and location_2 =0 
        and location_3 = 0 
        and location_4=0 
        and location_5=0 
        and oh.jobnum='T35204D'
  GROUP BY he1.[Mfg_Number]

Give this a try.

SELECT he1.[Mfg_Number] as HingeDeviceOneLocation, sum(lineqty) as DeviceQty
  FROM [MQPDatabase].[dbo].[Hinge_Edge] he1 
    inner join lineinfo li on li.ctr=he1.lineinfoctr 
    inner join order_header oh on oh.ctr=li.order_headerctr
    where location_1 >0 
        and location_2 =0 
        and location_3 = 0 
        and location_4=0 
        and location_5=0 
        and oh.jobnum='T35204D'
  GROUP BY he1.[Mfg_Number]

回复收藏 0 原文