select CompId from DataTable
where (ProdId='id1' and Qty=1)
or (ProdId='id2' and Qty=2)
or (ProdId='id3' and Qty=3)
where (ProdId='id1' and Qty=1)
or (ProdId='id2' and Qty=2)
or (ProdId='id3' and Qty=3)
解决方案 »
- 提取数据问题
- 如何在聚合函数中过滤掉空字符串
- 请问: 我在远程服务器上建立了一个表,但经常会出现访问这个表的时候,连接超时.而其它表中的数据远比这个表的数据多却没有问题.
- 百分相赠,绝不食言:如何在存储过程或SQL语句中实现将多表的数据导入到execl文件的多个工作表中,谢谢。
- Microsoft 分布式事务处理协调器(MS DTC)已取消此分布式事务
- 大量数据导入问题,BULK INSERT ,或生成若干个'insert into table '问题
- 超级难的SQL语句
- 数据库模式这东西,有朋友能通俗讲解下么?
- 真诚请问高手~一个PROC的难题?
- 有两台sql server2000数据库服务器A和B(有固定ip地址,不在一起),我希望在应用程序(delphi做的)里点个按钮,然后就把A里的一些数据
- 同局域网不同网段的SQL数据库访问
- 将重复了M次的记录显示N次的sql语句怎么写
from DataTable
where [Group] = 'Group1' and ((ProdId=id1 and Qty=1) or (ProdId=id2 and Qty=2) or (ProdId=id3 and Qty=3))
我数据库中的数据这是样的
CompId ProdId Qty
comp1 id1 1
comp1 id2 2
comp1 id3 3
comp2 id1 1
comp2 id2 2 Group里的内容是随便定的,只是用来方便区分的,现在有
ProdId Qty
id1 1
id2 2
id3 3
这些数据,那么就要想查出comp1
select 'id1'ProdId,1 Qty union all
select 'id2'ProdId,2 Qty union all
select 'id1'ProdId,1 Qty)a on b.ProdId=a.ProdId and b.Qty=a.Qty
(
select CompId from
(
select distinct CompId from tb where ProdId = 'id1' and Qty = 1
union all
select distinct CompId from tb where ProdId = 'id2' and Qty = 2
union all
select distinct CompId from tb where ProdId = 'id3' and Qty = 3
) t group by CompId having count(*) = 3
)
insert into tb values('comp1', 'id1' , 1 )
insert into tb values('comp1', 'id2' , 2 )
insert into tb values('comp1', 'id3' , 3 )
insert into tb values('comp2', 'id1' , 1 )
insert into tb values('comp2', 'id2' , 2 )
goselect CompId from
(
select distinct CompId from tb where ProdId = 'id1' and Qty = 1
union all
select distinct CompId from tb where ProdId = 'id2' and Qty = 2
union all
select distinct CompId from tb where ProdId = 'id3' and Qty = 3
) t group by CompId having count(*) = 3
/*
CompId
----------
comp1(所影响的行数为 1 行)
*/select * from tb where CompId in
(
select CompId from
(
select distinct CompId from tb where ProdId = 'id1' and Qty = 1
union all
select distinct CompId from tb where ProdId = 'id2' and Qty = 2
union all
select distinct CompId from tb where ProdId = 'id3' and Qty = 3
) t group by CompId having count(*) = 3
)
/*
CompId ProdId Qty
---------- ---------- -----------
comp1 id1 1
comp1 id2 2
comp1 id3 3(所影响的行数为 3 行)
*/drop table tb