select * from a where (select count(distinct price) from b where code=a.code)>1
解决方案 »
- 一个月进行统计一次的SQL 怎么写
- sql 求查询一小时内添加的数据的sql语句
- 统计库存的问题?
- 使用ssis 将 access2000 导入SQL2005时候。 选择哪个数据源??
- 在线等订阅时提示:名称不能是NULL
- sql2008数据库同步并发的问题
- 问一存储过程
- 初手问:如何让主键属性象ACCESS一样按升序自动编号。
- 在企业管理器中,修改表中记录出现以下错误如何处理?"错误信息:不能在firehose 方式下启动事务"
- 有关调用自己写的存储过程实现创建表的问题,请各位高手帮忙解决!!急!!
- 我的sql server2005 express有一点问题
- 导入excel文件出错,大家帮忙看看
select
a.code,quantity=(a.quantity-isnull(b.quantity,0))a.price
from
a
left join
b on a.code=b.code
a.code,quantity=(a.quantity-isnull(b.quantity,0)),a.price
from
(select code,quantity=sum(quantity),price from a group by code,price )a
left join
(select code,quantity=sum(quantity),price from b group by code,price )b on a.code=b.code
drop table #t1
drop table #t2
create table #a(code int,quantity int,price float)
insert into #a select 101,20,4
insert into #a select 101,40,2.5
insert into #a select 102,10,12
insert into #a select 102,5,8
insert into #a select 102,30,15create table #b(code int,quantity int,price float)
insert into #b select 101,20,4
insert into #b select 102,10,12
insert into #b select 102,10,15
select a.code,quantity=isnull(a.quantity-b.quantity,a.quantity), a.price from #a a left join #b b
on a.code=b.code and a.price=b.price
where a.code=102
code quantity price
----------- ----------- ----------------------
102 0 12
102 5 8
102 20 15(3 行受影响)
create table #a(code int,quantity int,price float)
insert into #a select 101,20,4
insert into #a select 101,40,2.5
insert into #a select 102,10,12
insert into #a select 102,5,8
insert into #a select 102,30,15create table #b(code int,quantity int,price float)
insert into #b select 101,20,4
insert into #b select 102,10,12
insert into #b select 102,10,15
select a.code,quantity=isnull(a.quantity-b.quantity,a.quantity), a.price from #a a left join #b b
on a.code=b.code and a.price=b.price
where a.code=102
code quantity price
----------- ----------- ----------------------
102 0 12
102 5 8
102 20 15(3 行受影响)