请教一条SQL:
A:a,b,c
1 2 3
2 2 4
3 2 5
4 3 3
5 3 4
6 3 5
7 4 3
7 4 4
B e f
1 2
2 3
3 4
怎么查出
b.e b.f
1 2
2 3
条件就是A.b = B.e
A.c中同时含有3,4,5
A:a,b,c
1 2 3
2 2 4
3 2 5
4 3 3
5 3 4
6 3 5
7 4 3
7 4 4
B e f
1 2
2 3
3 4
怎么查出
b.e b.f
1 2
2 3
条件就是A.b = B.e
A.c中同时含有3,4,5
解决方案 »
- 向数据库连续插入多条数据的问题!(在线等)
- 查找语法错误
- 数据库不熟,谁能帮我弄一下这条语句该怎么写!
- Microsoft OLE DB Provider for SQL Server 错误 '80040e14' 在关键字 'and' 附近有语法错误。
- datetime长度问题?
- 存储过程 实现一行到多行
- SQL没有问题ACCESS出现“该特定字段 '[User]' 可以参考 SQL 语句中 FROM 子句列表中的多个表。 ”
- 寻求译者!!!
- sql统计,谢谢大家帮忙
- 在一条SQL里要用到不同机子数据库里的表怎么写啊(急......)
- SQL server 中,and 和OR连用的问题
- 请教一个查询SQL语句
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-07-11 12:49:11
---------------------------------
IF OBJECT_ID('[ta]') IS NOT NULL
DROP TABLE [ta]
go
CREATE TABLE [ta] (a INT,b INT,c INT)
INSERT INTO [ta]
SELECT 1,2,3 UNION ALL
SELECT 2,2,4 UNION ALL
SELECT 3,2,5 UNION ALL
SELECT 4,3,3 UNION ALL
SELECT 5,3,4 UNION ALL
SELECT 6,3,5 UNION ALL
SELECT 7,4,3 UNION ALL
SELECT 7,4,4select * from [ta]---------------------------------
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-07-11 12:49:55
---------------------------------
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
go
CREATE TABLE [tb] (e INT,f INT)
INSERT INTO [tb]
SELECT 1,2 UNION ALL
SELECT 2,3 UNION ALL
SELECT 3,4select * from [tb]with wang
as(select * from ta,tb where ta.b = tB.e),
wang1 as (select e,f from wang s where c=3 and exists(select 1 from wang where c=4) and exists(select 1 from wang where c=5))select * from wang1e f
2 3
3 4
怎么查出
b.e b.f
1 2
2 3
条件就是A.b = B.f
A.c中同时含有3,4,5
--> 我的淘宝:http://shop36766744.taobao.com/if object_id('[A1]') is not null drop table [A1]
create table [A1]([a] int,[b] int,[c] int)
insert [A1]
select 1,2,3 union all
select 2,2,4 union all
select 3,2,5 union all
select 4,3,3 union all
select 5,3,4 union all
select 6,3,5 union all
select 7,4,3 union all
select 7,4,4
if object_id('[B2]') is not null drop table [B2]
create table [B2]([e] int,[f] int)
insert [B2]
select 1,2 union all
select 2,3 union all
select 3,4select B2.* from B2 join (
select * from A1 a where [a]=(select [a] from A1 where a.b=b and c=a.c+2)-2)t
on B2.f=t.b/*
e f
----------- -----------
1 2
2 3(所影响的行数为 2 行)*/
drop table A1,B2
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('a') IS NOT NULL
DROP TABLE a
GO
CREATE TABLE a(a int, b int, c int )
go
insert into a
select
1 , 2 ,3 union all select
2 , 2 , 4 union all select
3 , 2 ,5 union all select
4 , 3 ,3 union all select
5 , 3 , 4 union all select
6 , 3 ,5 union all select
7 , 4 , 3 union all select
7 ,4 , 4
go
IF OBJECT_ID('b') IS NOT NULL
DROP TABLE b
GO
CREATE TABLE b(e int, f int)
go
insert into b
select
1,2 union all select
2,3 union all select
3,4
go
select distinct e,f
from a aa join b bb on aa.b=bb.f
where 3 in(select c from a where aa.b=b) and 4 in(select c from a where aa.b=b) and 5 in(select c from a where aa.b=b)
/*------------
e f
----------- -----------
1 2
2 3
-------*/
create table [A]([a] int,[b] int,[c] int)
insert [A]
select 1,2,3 union all
select 2,2,4 union all
select 3,2,5 union all
select 4,3,3 union all
select 5,3,4 union all
select 6,3,5 union all
select 7,4,3 union all
select 7,4,4
if object_id('[B]') is not null drop table [B2]
create table [B]([e] int,[f] int)
insert [B]
select 1,2 union all
select 2,3 union all
select 3,4select B.* from B
where exists ( select 1 from a
where a.b=b.f
and c=3
)
and exists ( select 1 from a
where a.b=b.f
and c=4
)
and exists ( select 1 from a
where a.b=b.f
and c=5
)--结果
e f
----------- -----------
1 2
2 3(所影响的行数为 2 行)
insert into @a select 1,2,3
union all select 2,2,4
union all select 3,2,5
union all select 4,3,3
union all select 5,3,4
union all select 6,3,5
union all select 7,4,3
union all select 7,4,4
declare @b table (e int,f int)
insert into @b select 1,2
union all select 2,3
union all select 3,4
;With china as
(
select * from @a a where exists (select 1 from @a where a.b=b and c=3)
and exists (select 1 from @a where a.b=b and c=4)
and exists (select 1 from @a where a.b=b and c=5)
),china1 as
(
select * from china a right join @b b on a.b= b.e
)
select top 2 e,f from china1(3 行受影响)
e f
----------- -----------
1 2
2 3(2 行受影响)
declare @A table (a int,b int,c int)
insert into @A
select 1,2,3 union all
select 2,2,4 union all
select 3,2,5 union all
select 4,3,3 union all
select 5,3,4 union all
select 6,3,5 union all
select 7,4,3 union all
select 7,4,4 union all
select 7,4,6
--> 测试数据: @B
declare @B table (e int,f int)
insert into @B
select 1,2 union all
select 2,3 union all
select 3,4select b.* from @b b,
(select b from @A where c in(3,4,5)
group by b having count(1)>=3)a
where a.b=b.e
感觉这样可以:
视图:
SELECT MAX(RPID) AS RPID, RP_Product_ID, RP_Supplier_ID, 1 AS rowNum
FROM Regist_Product
GROUP BY RP_Product_ID, RP_Supplier_ID
查询:
select * from [Suppliers] as a
inner join
(
select RP_Supplier_ID,sum(rowNum) as rowtotal from [视图]
where RP_Product_ID in(3,4,5)group by RP_Supplier_ID
) as b
on a.SID = b.RP_Supplier_ID
where b.rowTotal = 3
只是不知道效率如何