SQL查询某字段的数据取另一个表的一条数据怎么写

靠技术搞事的程序媛 2017-04-24 12:02:13

select
iRowNo as 行號,
b.cbdefine30 as 數量,
cDefine22 as 走貨地,
isnull(b.cbdefine1,'') as 底字,
cDefine31 as 'TIMT-SET',
dPreMoDate as Job期,
cDefine25 as 客PO,
cDefine24 as 客PO行號,
a.iUnitPrice as Price,
b.cbdefine16 as MovmentPrice,
b.cbdefine17 as PackingPrice,
a.cInvCode,
b.cbdefine40 as FinishedInvCode,
a.cMemo,
a.cDefine23,
a.cDefine32,
b.cbdefine41 as SpareClassName,
im.MovtModelNo
from SO_SODetails a
join SO_SOMain m on m.ID = a.ID
left join SO_SODetails_extradefine b on a.iSOsID=b.iSOsID
left join v_Cust_Inventory_Movt im on im.InvCode = b.cbdefine40
where a.cSOCode = '{0}'
and isnull(b.cbdefine30,0) > 0
and im.InvCode='D_AX2176'

查出来的结果是

这样的
现在我想要的是：这两条数据只要一条就行了，可以参考

这个表的，取他的第一条数据，该怎么修改sql语句

...全文

708 2 打赏收藏转发到动态举报

写回复

用AI写文章

2 条回复

切换为时间正序

请发表友善的回复…

发表回复

二月十六版主 2017-04-24

打赏
举报

试试这个

SELECT  iRowNo AS 行號 ,
        b.cbdefine30 AS 數量 ,
        cDefine22 AS 走貨地 ,
        ISNULL(b.cbdefine1, '') AS 底字 ,
        cDefine31 AS 'TIMT-SET' ,
        dPreMoDate AS Job期 ,
        cDefine25 AS 客PO ,
        cDefine24 AS 客PO行號 ,
        a.iUnitPrice AS Price ,
        b.cbdefine16 AS MovmentPrice ,
        b.cbdefine17 AS PackingPrice ,
        a.cInvCode ,
        b.cbdefine40 AS FinishedInvCode ,
        a.cMemo ,
        a.cDefine23 ,
        a.cDefine32 ,
        b.cbdefine41 AS SpareClassName ,
        ( SELECT TOP 1
                    MovtModelNo
          FROM      v_Cust_Inventory_Movt
          WHERE     InvCode = b.cbdefine40
        ) AS MovtModelNo
FROM    SO_SODetails a
        JOIN SO_SOMain m ON m.ID = a.ID
        LEFT JOIN SO_SODetails_extradefine b ON a.iSOsID = b.iSOsID
WHERE   a.cSOCode = '{0}'
        AND ISNULL(b.cbdefine30, 0) > 0
        AND im.InvCode = 'D_AX2176'

靠技术搞事的程序媛 2017-04-24