correlated subquery - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

correlated subquery

correlated subquery# Database - 数据库

m*h2008-09-27 07:09

1 楼

CREATE TABLE department(
dept_no CHAR(4) NOT NULL,
dept_name CHAR(25) NOT NULL,
location CHAR(30) NULL)
insert into department values ('d1', 'developer', 'Dallas')
insert into department values ('d2', 'tester', 'Seattle')
insert into department values ('d3', 'marketing', 'Dallas')
SELECT t1.*
FROM department t1
WHERE t1.location IN
(SELECT t2.location
FROM department t2
WHERE t1.dept_no <> t2.dept_no)
dept_no dept_name location

B*g2008-09-27 07:09

2 楼

咱们每一个record都看一下。
d1: sql就变成
SELECT t1.*
FROM department t1
WHERE t1.dept_no = 'd1'
AND t1.location IN
(SELECT t2.location
FROM department t2
WHERE 'd1' <> t2.dept_no)
也就是
SELECT t1.*
FROM department t1
WHERE t1.dept_no = 'd1'
AND t1.location IN （'Dallas'， 'Seattle'）
所以符合, d1有了
d2: sql就变成
SELECT t1.*
FROM department t1
WHERE t1.dept_no = 'd2'
AND t1.location IN
(SELECT t2.location
FROM department t2
WHERE 'd2' <> t2.dept_no)
也就是
SELECT t1.*
FROM department t1
WHERE t1.dept_no = 'd2'
AND t

【在 m*****h 的大作中提到】

: CREATE TABLE department(
: dept_no CHAR(4) NOT NULL,
: dept_name CHAR(25) NOT NULL,
: location CHAR(30) NULL)
: insert into department values ('d1', 'developer', 'Dallas')
: insert into department values ('d2', 'tester', 'Seattle')
: insert into department values ('d3', 'marketing', 'Dallas')
: SELECT t1.*
: FROM department t1
: WHERE t1.location IN