[code="java"]select * from dbo.tbGoodsInfo where
(
id in (select goodsId from dbo.tbGoodsAddLink )
or id in(
select goodsId from dbo.tbGoodsBindBar where barUserId=
(select id from dbo.t_ClientInfo where bh='cdcs')
))
and auditStatus=0 and bupd!=-1
union
select * from dbo.tbGoodsInfo where (id not in (select goodsId from dbo.tbGoodsAddLink )
and id not in(select goodsId from dbo.tbGoodsBindBar where
barUserId=(select id from dbo.t_ClientInfo where bh='cdcs')
)) and auditStatus=0 and bupd!=-1[/code]
[size=medium]谢谢[/size]
上面语句有点问题
[code="sql"]
-- ftype 优先绑了地区、其次绑了网吧、最后其他
select goods.*,
(case when addr.id is not null then 1 else
case when client.id is not null then 2 else
3 end end
) as ftype
from dbo.tbGoodsInfo as goods
left join dbo.tbGoodsAddLink as addr on goods.id=addr.goodsId
left join dbo.tbGoodsBindBar as bar on goods.id=bar.goodsId
left join dbo.t_ClientInfo as client on bar.barUserId=client.id and client.bh='cdcs'
where goods.auditStatus=0
and goods.bupd!=-1
order by ftype
[/code]
你应该把你的语句的逻辑写出来，再附上格式化的sql
这个sql的性能问题在于
1.嵌套太多的子查询，子查询是很影响性能的，建议转换为多表关联查询
2.一个语句可以搞定就不用union
把你语句实现的业务逻辑写出来，再帮你调优。
如果一个商品既绑了地区又绑了网吧，查询结果是一个商品记录还是两个商品记录？
[code="sql"]
-- ftype 优先绑了地区、其次绑了网吧、最后其他
select goods.*,(case when add.id is not null then 1 else case when bar.id is not null then 2 else 3 end end) as ftype
from dbo.tbGoodsInfo as goods
left join dbo.tbGoodsAddLink as add on goods.id=add.goodsId
left join dbo.tbGoodsBindBar as bar on goods.id=bar.goodsId
left join dbo.t_ClientInfo as client on bar.barUserId=client.id and client.bh='cdcs'
where goods.auditStatus=0
and goods.bupd!=-1
order by ftype
[/code]
这个实现结果，商品不会重复。 绑地区 优先于 绑网吧