[code="java"]
select * from dbo.tbGoodsInfo where (id in (select distinct isnull(goodsId,'') from dbo.tbGoodsBindArea )
or id in(select distinct isnull(goodsId,'') from dbo.tbGoodsBindBar where barUserId='cdcs'))
and auditStatus=0 and bupd!=-1 and goodsType=1
union
select * from dbo.tbGoodsInfo where (id not in (select distinct isnull(goodsId,'') from dbo.tbGoodsBindArea )
and id not in(select distinct isnull(goodsId,'') from dbo.tbGoodsBindBar where barUserId='cdcs'))
and auditStatus=0 and bupd!=-1 and goodsType=1[/code]
如题求解
不好意思，没有环境，sql server用得不多，难免语法错误 呵呵，说下思路：
你检索的数据实际上就是满足那3个条件的数据，只是这些数据的顺序不同，把ID存在表tbGoodsBindArea和表tbGoodsBindBar的数据放到前面，其他数据放到后面。
所以，让表tbGoodsInfo和上面sql的表M进行left join，关联到的数据排到前面，具体你再改改~~~：）
你这个SQL比较奇怪
select * from where (cond1 or cond2) and auditStatus=0 and bupd!=-1 and goodsType=1
union
select * from where (not cond1 and not cond2) and auditStatus=0 and bupd!=-1 and goodsType=1
那不就等价于：
select * from where auditStatus=0 and bupd!=-1 and goodsType=1 这个吗？
相当于：
select * from dbo.tbGoodsInfo where auditStatus=0 and bupd!=-1 and goodsType=1
在这三个字段上建建索引
[quote]需求是优先查询出跟dbo.tbGoodsBindArea,dbo.tbGoodsBindBar有关的dbo.tbGoodsInfo数据,然后再查询出剩下的数据[/quote]
那就不用union啊，得两条sql语句分别执行。
没环境我没跑，你试下面的SQL怎么样
[code="sql"]SELECT N.* FROM
(
SELECT tbGoodsInfo.*,M.goodsId
FROM dbo.tbGoodsInfo
LEFT JOIN
(SELECT isnull(goodsId, '') goodsId
FROM dbo.tbGoodsBindArea
UNION
SELECT isnull(goodsId, '') goodsId
FROM dbo.tbGoodsBindBar
WHERE barUserId = 'cdcs')
) M on tbGoodsInfo.id = M.goodsId
AND auditStatus = 0
AND bupd != -1
AND goodsType = 1
) N
ORDER BY N.goodsId[/code]