求一条SQL语句 ( 积分: 50 )

L

liangexcel

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-07-06

• #1

两个表,一个是主表,一个是明细表.
主表主要主段SERIES,明细表主要ITEM,SERIES,NUM
SERIES是关联字段.如何根本明细表的值而查出主表的SERIES值.例如:查找NUM=1 AND NUM=2 AND NUM=3 的SERIES值.
看得明白吧.就是一个SERIES对应N个NUM值,我想找出NUM既有1也有2和3的SERES值

 

W

weichao9999

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-07-06

• #2

select * from 明细表 where NUM=1 AND NUM=2 AND NUM=3
你可能没有表达清楚

 

L

liangexcel

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-07-06

• #3

to weichao9999:
你的回答肯定是错的.
给你一个例子吧.
表A
SERIES
1
2
3
4
表B
ITEM SERIES NUM
1 1 1
2 1 2
3 1 3
4 2 1
5 2 4
6 3 1
7 4 3
8 4 6
你按这些值查一下,你得到的结果是空值的.

 

I

icc

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-07-06

• #4

这就叫给了个例子么
老兄你想要什么

 

Y

ysai

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-07-06

• #5

select * from 表a a
where exists (select 1 from 表b b where a.SERIES=b.SERIES and b.NUM=1)
and exists (select 1 from 表b c where a.SERIES=c.SERIES and c.NUM=2)
and exists (select 1 from 表b d where a.SERIES=d.SERIES and d.NUM=3)

 

V

vcchengm

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-07-06

• #6

Select * From Table1 A
Where A.SERIES in (Select SERIES From Table B Where A.SERIES=B.SERIES And B.Num=1)
And A.SERIES in (Select SERIES From Table B Where A.SERIES=B.SERIES And B.Num=2)
And A.SERIES in (Select SERIES From Table B Where A.SERIES=B.SERIES And B.Num=3)

 

L

liangexcel

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-07-06

• #7

这样的效率会否低了一点?应该还有更有效率的语句吧?

 

A

avbb4cat

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-07-06

• #8

select aa.*
from aa,
(select SERIES from ( select SERIES, count(Num) theCount
from ( select distinct SERIES, NUM from bb) cc where cc.Num in (1,2,3)
group by SERIES) dd where dd.theCount=3 ) ee
where aa.SERIES=ee.SERIES

 

A

avbb4cat

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-07-06

• #9

aa=表A
bb=表B

 

J

jack391

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-07-06

• #10

select SERIES from 明细表 where (num=1) or (num=2) or (num=3) group by SERIES having count(distinct num)=3

 

W

weichao9999

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-07-09

• #11

jack391的方法不错

 

Z

zhlu

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-07-09

• #12

SELECT SERIES
FROM
(
SELECT SERIES,SUM(CASE WHEN Num=1 THEN 1 ELSE 0 END) AS Num1,SUM(CASE WHEN Num=2 THEN 1 ELSE 0 END) AS Num2,SUM(CASE WHEN Num=3 THEN 1 ELSE 0 END) AS Num3
FROM
GROUP BY SERIES
) A
WHERE Num1>0 AND Num2>0 AND Num3>0